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:

MATCH Function - How to use MATCH Function in Excel

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

Result:

MATCH Function in Excel - How to use MATCH Function in Excel

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

Syntax: =MATCH(“Adrienne Ames”,B2:B17,0)
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.

Add a Comment

Your email address will not be published. Required fields are marked *