How to use MATCH Function in Excel

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.
  0	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:

Syntax:  =MATCH(46,E2:E17,0)

Result:

Based on the Excel spreadsheet above, the following MATCH examples would return:

Note:

• 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.