September 28, 2021

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

READ:  How to use RTD Function in Excel

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:

Syntax: =MATCH(50,E2:E17,0)
Result: 11

Syntax: =MATCH(45,E2:E17,-1)
Result: #N/A

Syntax: =MATCH(“Jack Albertson”,B2:B17,0)
Result: 6

Syntax: =MATCH(“Ja*”,B2:B17,0)
Result: 6

Result: 10

Syntax: =MATCH(“Do*”,B2:B17,0)
Result: 11

Syntax: =MATCH(50000,F2:F17,0)
Result: 4

Syntax: =MATCH(85000,F2:F17,0)
Result: 9

Syntax: =MATCH(80000,F2:F17,0)
Result: 1

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. #### Excel

View all posts by Excel →