Description
The Excel MATCH function searches for a specific item in a given range and returns the position as an index number. MATCH is covers exact, approximate, and wildcard matching. It is frequently used together with the INDEX function for powerful and version-independent lookup operations.
Syntax
MATCH( lookup_value, lookup_array, [match_type] )
Arguments
lookup_value – The value you want to search for
lookup_array – A range of cells or array
match_type – [Optional] A value indicating which match_type to use
Return value
An integer number representing the position of the lookup_value
How to use the MATCH function in Excel
The Excel MATCH function is used to find out the position of a lookup_value in a lookup_array. MATCH is frequently used together with the INDEX function to perform powerful lookups. By default, it performs an exact match search, like in the example above.
To find out the position of “Joe” (entered as lookup_value in cell D6) in the given vertical list of names, we use the following formula:
= MATCH(D6, B6:B15, 0) // returns row index 6
MATCH is able to apply three different match modes, which are specific in the match_type argument. In the above statement, we used the exact match type (0). Here you have a list of all keys that you can use for the match_type argument:
Match Type | Match Behaviour | Requirements |
|---|---|---|
0 (default) | Exact match only (also allows wildcard matches) | – |
1 | Exact match or next smallest value | The lookup_array needs to be sorted in asc order |
-1 | Exact match or next largest value | The lookup_array needs to be sorted in desc order |
.Lets take a closer look at the possible match types.
Exact match
You can perform an exact match search by supplying 0 for the match_type argument like this:
= MATCH(D5, B5:B14, 0) // returns row index 6
Note: MATCH is not case-sensitive, so “Joe” and “joe” will both return 6.
Approximate match
To perform an approximate match search, you have to either set match_type to 1 (exact match or next smaller item) or -1 (exact match or next smaller item). In this context, you have to ensure that approximate matching works correctly by sorting your data in either ascending or descending order.
In the example shown below, the formula is:
= MATCH(D5, B5:B14, 1) // returns row index of next smaller item
Wildcard match
If match_type is set to 0, MATCH also allows you to perform a match using wildcards (*?). In the example shown below, the formula is:
= MATCH(D5, B5:B14, 0) // returns row index best wildcard match
INDEX and MATCH
The Excel MATCH function is commonly used together with the INDEX function. In this combination, MATCH covers the task of locating and feeding a position to the INDEX function, which then returns the value at that given position. The basic syntax of INDEX MATCH looks like this:
In the above statement, MATCH locates the position of a lookup_value in the lookup_column, which is then used as the row_num index in the INDEX function. With a given lookup_array and col_ind for the result column, INDEX is then able to perform a flexible lookup that is even superior to the VLOOKUP function.
One major reason why INDEX MATCH is often preferred over VLOOKUP function is the fact that you can perform lookup to the left and right, while VLOOKUP is limited to only look up to the right.
Additional Notes
If no match is found, MATCH throws an #N/A error. You can trap this error by wrapping MATCH with the IFNA function.
Formula examples
Related Tutorials
Related Functions
Excel VLOOKUP function
The Excel VLOOKUP function searches for a value by matching on the first table column and returns the corresponding value from a column in the same row.
Excel XMATCH function
The Excel XMATCH function searches for a specific item in a given range and returns the position. XMATCH is a more flexible and robust version of MATCH.
Excel INDEX function
The Excel INDEX function returns the value of an element in a range or array at a given position, specified by the row and column number indexes.
