Excel MATCH function used to locate the position of a lookup value in a row, column, or table. Often, the INDEX function is combined with the MATCH to retrieve the value at the position returned by MATCH.
Purpose of Excel MATCH Function
Get the position of an item in an array.
.A number representing a position in lookup_array.
=MATCH (lookup_value, lookup_array, [match_type])
|lookup_value||The value to match in lookup_array.||Mandatory|
|lookup_array||A range of cells or an array reference.||Mandatory|
|[match_type]||exact or next smallest (default), 0 = exact match, -1 = exact or next largest.||Optional|
- MATCH is not case-sensitive.
- MATCH returns the #N/A error if no match is found.
- MATCH only works with text up to 255 characters in length.
- In the case of duplicates, MATCH returns the first match.
- If match_type is -1 or 1, the lookup_array must be sorted as noted above.
- If match_type is 0, the lookup_value can contain the wildcards.
- The MATCH function is frequently used together with the INDEX function.
Since 2007 for Windows.
Since 2011 for Mac.
A simple example of match
Formula used: =MATCH(E5,$C$4:$C$10,0)
E5: Referring cell
0: Meant for exact match
After executing the above formula the function returns the place in numbers of the referred cell in the table.
Suggested reading: 20 Excel Functions Useful For Finance Professionals