Match() function

Match() function

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

The syntax for the MATCH function in Microsoft Excel is:

=MATCH (lookup_value, lookup_array, [match_type])

Parameters or Arguments

lookup_value

The value to search for in the array.

lookup_array

A range of cells that contains the value that you are searching for.

match_type

Optional. It the type of match that the function will perform. The possible values are:

Match_type	Explanation
1 (default)	The MATCH function will find the largest value that is less than or equal to value. You should be sure to sort your array in ascending order. If the match_type parameter is omitted, it assumes a match_type of 1.
0	The MATCH function will find the first value that is equal to value. The array can be sorted in any order.
-1	The MATCH function will find the smallest value that is greater than or equal to value. You should be sure to sort your array in descending order.

Usage notes

Use the MATCH function to get the relative position of an item in an array. Match offers several different matching modes, which makes it more flexible than other lookup functions. Used together with INDEX, MATCH can retrieve the value at the matched position.

Match type information

-          If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. The lookup_array must be sorted in ascending order.

-          If match_type is 0, MATCH finds the first value exactly equal to lookup_value. lookup_array does not need to be sorted.

-          If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. The lookup_array must be sorted in descending order.

-          If match_type is omitted, it is assumed to be 1.

Note: All match types will find an exact match.

Notes:

-          Match is not case-sensitive.

-          Match returns the #N/A error if no match is found

-          The argument lookup_array must be placed in descending order: TRUE, FALSE, Z-A,...2, 1, 0, -1, -2,..., and so on.

-          If match_type is 0 and lookup_value is text, the wildcard characters question mark (?) and asterisk (*) can be used in lookup_value.

-          If match_type is 0 and lookup_value is text, lookup_value can contain the wildcard characters asterisk (*) and question mark (?). An asterisk matches any sequence of characters; a question mark matches any single character.

Excel Match Function Example 1: Text Match

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.

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.

-          Note also, the use of the wildcard characters '?' and '*' in the examples in cells B2 and B3.

Match Function Example 2: Number Match

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.

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: Match Closest Value

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.

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 (closest value below 8)
3	7	5	- returns position 5 (closest value below 15)
4	10
5	11
6	16

-          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.

Common Error

#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)

he 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 characters 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 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.

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 may be because the lookup_array is not 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:

i.                     If [match_type] is set to 1, the lookup_array should be in ascending order;

ii.                   If [match_type] is set to -1, the lookup_array should be in descending order.