The Excel Match function looks up a value in an array, and returns the position of the value within the array.
The user can specify that the function should only return a result if an exact match is found, or that the function should return the position of the closest match (above or below), if an exact match is not found.
The syntax of the Match function is:
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:|
You can use the following wildcards in the lookup_value for text matches (but only when the [match_type] argument is set to 0):
? - 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).E.g. the condition "A*e" will match all cells containing a text string beginning with "A" and ending in "e".
The Match function can be used to match numeric values, logical values, or text strings. Note that, when looking up a text string, the function is NOT case-sensitive. So, for example, the text strings "TEXT" and "text" will both be considered to be a match.
Wildcards can be used when the lookup_value is a text string and the [match_type] argument is set to 0 (requiring an exact match). This is illustrated in the examples below.
In the following Excel Match function examples, the [match_type] argument is 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.
In each case, the lookup_array is the range of cells A1-A5.
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.
Note also, the use of the wildcard characters '?' and '*' in the examples in cells B2 and B3.
The following Excel Match function examples also all have the [match_type] argument set to 0 (requiring an exact match), but in this case the function is used to look up numeric values.
In each of these examples, the lookup_array is the range of cells A1-A6.
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.
In the following examples, the [match_type] argument is set to 1. Therefore, 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.
Note that, as the [match_type] argument is set to 1, the data in the lookup_array must be in ascending order.
Note that the [match_type] argument could have been omitted from the functions in the above spreadsheet, as this argument has the value 1 by default.
For further information and examples of the Excel Match Function, see the Microsoft Office website.
If you get an error from the Excel Match function, this is likely to be the #N/A error:
Either of these cause the lookup_value and the values in the lookup_array to be seen by Excel as different, and will therefore cause the Match function to fail.For further details on how to identify and resolve this problem, see the Failure to Match Values page.
Also, some users experience the following common problem with the Excel Match function:
Common Match Function Problem
The Excel Match function returns the wrong result
If your Excel Match function simply returns the wrong result, this may be because the lookup_array is not ordered correctly.
Check the following: