How to Use the Excel MATCH Function [In Detail]


The MATCH function is part of Excel’s group of lookup functions. This includes INDEX, LOOKUP, VLOOKUP, and HLOOKUP.

Excel’s MATCH function lets you tell it what item to look for in a range, and it then returns the position of that item in the range. Note that it returns, not the item, but the position of the item in the range. This is different from the LOOKUP functions.

Again, it’s pretty simple and basic. However, it’s a powerful function that can be combined with the INDEX function to create very advanced lookup formulas. Here’s how to use it.

 

Start With the Details

Here’s the Excel MATCH function:

MATCH(lookup_value, lookup_array, [match_type])

lookup_value = the value you want to find. It’s required.

lookup_array = the range of cells to search for the value. It’s required.

[match type] = tells the function how it matches the lookup_value in lookup_array. It’s optional. If you use it, you pass the values of either -1, 0, or 1 to the function.

match_type valueDescription
-1Return the smallest value that is greater than or equal to the lookup_value. The data in lookup_array must be in ascending order.
0Return the first value that is equal to the lookup_value.
1Return the largest value that is less than or equal to the lookup_value. The data in lookup_array must be in descending order.

 

Using the Excel MATCH Function

The sample below will be used as the basis for the examples in this post. Using text for the lookup_value as well as using values for the lookup_value will be shown.

excel match function

Using Text for the Lookup_value

With the above example, enter the following formula:

=MATCH(“Green”, A2:A6).

This will return the result of “3”.

The above formula is looking for the position of the text “Green” in the area of cells A2 to A6. Since the text “Green” is the third cell, the result of “3” is returned.

The match is not case-sensitive, and will return the position, even you enter “0” as the match type.

Also, if you use text for the lookup_value, and also use “0” for the match_type argument, wildcard characters of question mark (?) and asterisk (*) can be used as part of the lookup_value. If you actually want to search for a question mark or asterisk, you will need to type a tilde character (~) before it.

For example, using the formula:

=MATCH(“Ye*”, A2:A6,0),

will return “2” because “Yellow” is in position 2 in the lookup_array range.

Changing the formula to:

=MATCH(“Bl*”, A2:A6,0),

will return “4”. Note in the sample, there are two values in the lookup_array range that contain start with “Bl”. These are both “Blue” in position 4 and “Black” in position 5. Since the “0” match_type argument will return the first result that matches, it returns “4”, since “Blue” is the first match.

This may result in unintended results, so you may have to change the lookup_value criteria to be more specific, depending on your requirements.

Using Values for the Lookup_value

Using the same sample data, enter the following formula:

=MATCH(80, C2:C6).

This will return the result of “4”.

The above formula is looking for the position of the value “80” in the area of cells C2 to C6. Since the value of “80” is the fourth cell, the result of “4” is returned.

Now, change the above formula to this:

=MATCH(79, C2:C6).

This will return the result of “3”.

The above formula is looking for the position of the value “79” in the area of cells C2 to C6. Note that there is no value of “79”.

As there is no entry for the match_type argument, the default match_type of “1” is used. The argument of “1” tells the function to find the largest value that is less than or equal to “79”. In the range above, that value of “50” is in the third cell, so the result of “3” is returned.

Now, change the formula to:

=MATCH(79, C2:C6, 0).

The match_type argument of “0” tells the function to find an exact match. Since there is no exact match, the function returns the error result of “#N/A”.

How about if you enter this formula:

=MATCH(79, C2:C6, -1)?

The match_type argument of “-1” tells the function to find the smallest value that is greater than or equal to the lookup_value. Since the lookup_value is “79”, it would be reasonable to expect the function to return “4”, since the smallest value greater than or equal to “79” in the range specified is “80”, which is in position “4”.

However, the function will return the error result of “#N/A”.

This is because the values in the lookup_array range have to be in descending order if you use the match_type argument of “-1”.

Also, if you use the match_type argument of “1” to get the largest value that is less than or equal to the lookup_value, the lookup_array range values need to be sorted in ascending order.

When to use the MATCH Function?

On its own, there aren’t that many uses for the Excel MATCH function. One use of the function on its own is to use the function to have a formulaic way to verify that a value or text exists in a list.

However, when combined with other functions, MATCH becomes more useful. Such as, when combined with INDEX, it provides a powerful, and flexible replacement for VLOOKUP.

 

Leave a Reply