Matching Text and Values with MATCH function

MATCH function is helpful when you want to know the relative position of any text/value in a range of cells.

MATCH can even find a result if it doesn’t exactly match the given input by approximating the second-best result.

Syntax 

=MATCH (lookup_value, lookup_array, [match_type])

Arguments

• Lookup_value: The value you want to match in the lookup range of cells.
• Lookup_range: The range in which you want to match the value given in 1^st argument.
• Match_type: It is an optional argument but if you specify this argument inside the function you have 3 options.

• 1 – exact or next smallest
• 0 – exact match
• -1 – exact or next largest

• If you specify 1 as the third argument inside the match function then the function will return either the exact position of the match or the closest next smallest number.
• If you specify -1 as the third argument inside the function then the function will return either the exact position of the match or the closest next largest number.
• 0 as the third argument will return the position only if it finds an exact match.

Let us learn MATCH function by some examples on matching text as well as values for a better understanding.

Matching values/text with different match types

With match type 0

That means we are going to perform an exact match.

Returns the position as 7 as 50 is the 7^th element of the array/range we gave as our second argument.

Now if we pass something which is not present in the array/range with 0 as the match type,

#N/A error is thrown.

Performing the same match type, this time with text gives us similar results.

With match type 1

As we know the match type – 1 returns either exact or next smallest.
In our example we have given the lookup argument to be 53.

Notice that the value 53 is not present in the given range, the next smallest value is 50, so match function has returned the relative position 7 which is of the element 50 in our lookup array.

With match type -1

Going with the exact same data and giving the third argument as -1, observe the below result.

Let’s observe the result.

Damn! We got an error.

But we are very lucky to get this error as this is a learning opportunity for you all.

Remember- To make the match function return a result with -1 match type the array/range must be sorted in descending order first.

After sorting our range and then applying the match function we get,

We can clearly see that it has chosen the next large number to 53 as it is not present in the array.

We get the position of 55 in the array as our result.

In this way you can play around with the match function till you’ve got your use case for the MATCH function in Excel.

Practice MATCH function below:

The formula can be entered in the highlighted cell. If the formula entered is correct, it shows good job. If the formula is entered incorrectly, the result will be invalid. Please keep in mind that you can only enter formula in the highlighted cells.

Hurray! You know now how to use MATCH function. Practice INDEXMATCH and other functions by checking our articles below.

Hope you learned something valuable from this post.

Keep Learning!