Related Function:

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range.

Syntax

=MATCH(lookup_value,lookup_array,[match_type])

Arguments

Argument Description
lookup_value The value that you want to match in lookup_array

  When used with text strings, the MATCH function is NOT case-sensitive, e.g., the text strings “TEXT” and “text” will be considered to be a match
lookup_array The range of cells being searched
[match_type] Optional. The match_type argument specifies how Excel matches lookup_value with values in lookup_array

  [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 to lookup_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

Note: Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself. For example, you might use the MATCH function to provide a value for the row_num argument of the INDEX function.

Usage note: The CHOOSE and MATCH functions both work with arrays of data

  • The CHOOSE function receives a position and returns the value that has this position in the array
  • The MATCH function receives a value to lookup, and returns the postion of the value in the array

Examples

  A B C D
1 Product Count    
2 Oats 25    
3 Rye 38    
4 Wheat 40    
5 Barley 41    
6        
7 Formula Result Notes
8 =MATCH(39,B2:B5,1) 2 Because there is not an exact match, the position of the next lowest value (38) in the range B2:B5 is returned
9 =MATCH(41,B2:B5,0) 4 The position of the value 41 in the range B2:B5
10 =MATCH(40,B2:B5,-1) #N/A Returns an error because the values in the range B2:B5 are not in descending order

Usage note: You can also use the following wildcards in text matches when the [match_type] argument is set to 0:

      ?  –  matches any single character
  *  –  matches any sequence of characters

e.g. the condition “A*e” will match any substring beginning with “A” and ending in “e”.

  • If you do actually want to find the ? or * character, type the ~ symbol before this character in your search

Common Function Error(s)

Problem What went wrong
#N/A Occurs if either:

  [match_type] = 0 an exact match for the lookup_value is not found within the lookup_array
  [match_type] = 1
(or is omitted)
the first value in the lookup_array is larger than the lookup_value, i.e. if the array is in ascending order, there is no closest match below or equal to the lookup_value
  [match_type] = -1 the first value in the lookup_array is smaller than the lookup_value, i.e. if the array is in descending order, there is no closest match above or equal to the lookup_value
  the lookup_value and the data in the lookup_array have different data types
The MATCH function returns the wrong result May be because the lookup_array has not been placed in the correct order

  Make sure the [match_type] argument is set to the correct value, e.g. if omitted, the argument defaults to 1
  If [match_type] argument is set to 1 or -1, check that the lookup_array is correctly ordered; ascending order if [match_type] set to 1, descending order if [match_type] set to -1