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.
Reference:
follow us on
http://arivilm.blogspot.in
like us on https://www.facebook.com/Arivilm2501/