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.
Syntax:= MATCH (lookup_value, lookup_array, [match_type])
The MATCH function syntax has the following arguments:
- lookup_value Required. The value that you want to match in lookup_array. For example, when you look up someone’s number in a telephone book, you are using the person’s name as the lookup value, but the telephone number is the value you want.The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.
- lookup_array Required. The range of cells being searched.
- match_type Optional. The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.The following table describes how the function finds values based on the setting of the match_type argument.
Match_type Behavior 1 or omitted MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: …-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE. MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order. -1
MATCH finds the smallest value that is greater than or equal tolookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, …2, 1, 0, -1, -2, …, and so on.
Example: Let’s look at some Excel MATCH function examples and explore how to use the MATCH function as a worksheet function in Microsoft Excel:
Based on the Excel spreadsheet above, the following MATCH examples would return:
- 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.