Excel MATCH Function
MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, the INDEX function is combined with MATCH to retrieve the value at the position returned by MATCH.
- lookup_value - The value to match in lookup_array.
- lookup_array - A range of cells or an array reference.
- match_type - [optional] 1 = exact or next smallest (default), 0 = exact match, -1 = exact or next largest.
Use the MATCH function to get the position of a value in an array. Match offers three different matching modes, which makes it more flexible than other lookup functions. When used with INDEX, MATCH can retrieve the value at the matched position.
Match type information
Match type is optional. If not provided, match type defaults to 1 (exact or next smallest). When match type is 1 or -1, it is sometimes referred to as "approximate match". However, keep in mind that MATCH will find an exact match with all match types, as noted in the table below:
|1||Exact match or next smallest value. The lookup_array must be sorted in ascending order.|
|0||Exact match only. The lookup_array does not need to be sorted.|
|-1||Exact match or next largest value. The lookup_array must be sorted in descending order.|
Note: Be sure to set match type to zero (0) if you require an exact match. The default setting of 1 can cause MATCH to return results that look "normal" but are in fact incorrect. I recommend always providing a value for match_type, as a reminder of what behavior is expected.
Basic exact match
When match type is set to zero, MATCH performs an exact match. In the example below, the formula in E3 is:
Basic approximate match
When match type is set to 1, MATCH will perform an approximate match on values sorted A-Z, finding the largest value less than or equal to the lookup value. In the example shown below, the formula in E3 is:
Basic wildcard match
When match type is set to zero (0), MATCH can perform a match using wildcards. In the example shown below, the formula in E3 is:
This is equivalent to:
See below for more examples of the MATCH function.
- MATCH is not case-sensitive.
- MATCH returns the #N/A error if no match is found.
- MATCH only works with text up to 255 characters in length.
- In case of duplicates, MATCH returns the first match.
- If match_type is -1 or 1, the lookup_array must be sorted as noted above.
- If match_type is 0, the lookup_value can contain the wildcards.
- The MATCH function is frequently used together with the INDEX function.