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