Excel MATCH Function

Related Function:
CHOOSE Function

Basic Description

The Excel MATCH function finds the relative position of a value in a supplied array. The user can request either an exact match or can request the position of the closest match (above or below), if an exact match is not found.

The syntax of the function is:

MATCH( lookup_value, lookup_array, [match_type] )

Where the arguments are as follows:

lookup_value - The value that you want to look up
lookup_array - The data array that is to be searched
[match_type] - An optional logical argument, which can set to 1, 0 or -1 to return the following results:
1 - if the function cannot find an exact match, it should use the closest match below the lookup_value. (If this option is used, the lookup_array must be in ascending order).
0 - if the function cannot find an exact match, it should return an error. (If this option is used, the lookup_array does not need to be ordered).
-1 - if the function cannot find an exact match, it should use the closest match above the lookup_value. (If this option is used, the lookup_array must be in descending order).

The Match function can be used to match numeric values, logical values, or text strings. Note that, when used with text strings, the function is NOT case-sensitive. So, for example, the text strings "TEXT" and "text" will be considered to be a match.

Wildcards

You can also use the following wildcards in text matches:

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

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

eg. the condition "A*e" will match all cells containing a text string beginning with "A" and ending in "e".

It is also useful to know the wildcards that can be used with text strings, when the [match_type] argument is set to 0 (requiring an exact match). These are shown on the right.

Use of these wildcards is illustrated in the examples below.


Excel Match Function Example 1

The following spreadsheet shows the Excel Match function used with the [match_type] argument set to 0. Therefore, in these examples, the function only returns a result if an exact match to the lookup_value is found. Otherwise, the function returns an error.

 Formulas:

  A B
1 cccc =MATCH( "aaaa", A1:A5, 0 )
2 dddd =MATCH( "?eee", A1:A5, 0 )
3 aaaa =MATCH( "*b", A1:A5, 0 )
4 bbbb  
5 eeee  

 Results:

  A B C
1 cccc 3 - matches "aaaa" so returns position 3
2 dddd 5 - matches "eeee" so returns position 5
3 aaaa 4 - matches "bbbb" so returns position 4
4 bbbb    
5 eeee    

Note that, in the above examples, as the [match_type] argument is set to 0, the text strings in the lookup_array (cells A1-A5) do not need to be ordered.


Match Function Example 2

The following spreadsheet also shows the Excel MATCH function used with the [match_type] argument set to 0, but in this case the function is used to look up numeric values.

 Formulas:

  A B
1 7 =MATCH( 4, A1:A6, 0 )
2 2 =MATCH( 8, A1:A6, 0 )
3 4 =MATCH( 10, A1:A6, 0 )
4 1  
5 8  
6 11  

 Results:

  A B C
1 7 3 - returns position 3
2 2 5 - returns position 5
3 4 #N/A - no exact match - returns error
4 1    
5 8    
6 11    

As in the previous examples, as the [match_type] argument is set to 0, the values in the lookup_array (cells A1-A6) do not need to be ordered.


Match Function Example 3

The following spreadsheet shows the Excel MATCH function used with the [match_type] argument set to 1. Therefore, in this example, the data in the lookup_array must be in ascending order, and the function returns the position of the exact match to the lookup_value if this is found, or the position of the closest value below the lookup_value if an exact match is not found.

 Formulas:

  A B
1 4 =MATCH( 6, A1:A6, 1 )
2 6 =MATCH( 8, A1:A6, 1 )
3 7 =MATCH( 15, A1:A6, 1 )
4 10  
5 11  
6 16  

 Results:

  A B C
1 4 2 - returns position 2
2 6 3 - returns position 3 (nearest value below 8)
3 7 5 - returns position 5 (nearest value below 15)
4 10    
5 11    
6 16    

Note that the [match_type] argument could have been omitted from the functions in this spreadsheet, as this argument takes the value 1 by default.

Further information and examples of the Excel Match Function can be found on the Microsoft Office website.


Match Function Errors

If you get an error from the Excel Match function, this is likely to be one of the following :

Common Errors

#N/A - Occurs if the match function fails to find a match for the lookup_value. This may be either :
if [match_type] = 0 - an exact match for the lookup_value is not found within the lookup_array
if [match_type] = 1
(or is omitted)
- the first value in the lookup_array is larger than the lookup_value (note that, if the array is in ascending order, this means there is no closest match below or equal to the lookup_value)
if [match_type] = -1 - the first value in the lookup_array is smaller than the lookup_value (and therefore, if the array is in descending order, there is no closest match above or equal to the lookup_value)
If you have checked that you have the correct match_type argument value and that the lookup_value (or a closest match) exists in the lookup_array, it may be that the match has failed because either:
- there are unseen spaces in either the lookup_value or the values in the lookup_array
or
- the lookup_value and the data in the lookup_array have different data types

Either of these cause the lookup_value and the values in the lookup_array to be seen by Excel as containing different values, and will therefore cause the Match function to fail.

More information and solutions to these problems can be found on the Failure to match values page


You may also experience the following common problem with the Excel Match function:

Common Match Function Problem

The Excel Match function returns the wrong result

Possible Reason

If your Excel Match function simply returns the wrong result, this is likely due to the lookup_array not being ordered correctly.

Check the following:

1. Make sure the [match_type] argument is set to the correct value (Note that if this argument is omitted, it will default to 1)
2. If you have set the [match_type] argument to 1 or -1, check that the lookup_array is correctly ordered:
-   if [match_type] is set to 1, the lookup_array should be in ascending order
-   if [match_type] is set to -1, the lookup_array should be in descending order
Return to the Excel Lookup and Reference Functions Page

Return to the List of All Built-In Excel Functions

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net