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, MATCH is combined with the INDEX function to retrieve a value at a matched position.
Purpose: Get the position of an item in an array.
Return value: A number representing a position in lookup_array.
Syntax: =MATCH (lookup_value, lookup_array, [match_type])
Examples:
The MATCH function is used to determine the position of a value in a range or array. For example, in the screenshot above, the formula in cell E6 is configured to get the position of the value in cell D6. The MATCH function returns 5 because the lookup value (“peach”) is in the 5th position in the range B6:B14:
=MATCH(D6,B6:B14,0)
Match Type Information
Match type is optional. If not provided, match type defaults to 1 (exact or next smallest). When match type is 1 or -1, it is sometimes referred to as “approximate match”. However, keep in mind that MATCH will find an exact match with all match types, as noted in the table below:
Caution: Be sure to set match type to zero (0) if you need an exact match. The default setting of 1 can cause MATCH to return results that “look normal” but are in fact incorrect. Explicitly providing a value for match_type, is a good reminder of what behavior is expected.
Exact match:
When match type is set to zero, MATCH performs an exact match. In the example below, the formula in E3 is: =MATCH(E2,B3:B11,0) // returns 4
Wildcard Match
When match type is set to zero (0), MATCH can perform a match using wildcards. In the example shown below, the formula in E3 is:=MATCH(E2,B3:B11,0) // returns 6
This is equivalent to=MATCH("pq*",B3:B11,0)
Match and Index
The MATCH function is commonly used together with the INDEX function. The resulting formula is called “INDEX and MATCH”. For example, in the screen below, INDEX and MATCH are used to return the cost of a code entered in cell F4. The formula in F5 is:=INDEX(C5:C12,MATCH(F4,B5:B12,0)) // returns 150
In this example, MATCH is set up to perform an exact match. The MATCH function locates the code ABX-075 and returns its position (7) directly to the INDEX function as the row number. The INDEX function then returns the 7th value from the range C5:C12 as a final result.
The formula is solved like this:=INDEX(C5:C12,MATCH(F4,B5:B12,0))=INDEX(C5:C12,7)
And thus the result 150
Case-Sensitive match:
The MATCH function is not case-sensitive. However, MATCH can be configured to perform a case-sensitive match when combined with the EXACT function in a generic formula like this:=MATCH(TRUE,EXACT(lookup_value,array),0))
The EXACT function compares every value in array with the lookup_value in a case-sensitive manner. This formula is explained in with an INDEX and MATCH example here.
Tips:
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 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.
For many more article keep browsing this website or use search functionality on the top.
