Excel MATCH Function
- lookup_value - The value to match in lookup_array.
- lookup_array - A range of cells or an array reference.
- match_type - [optional] 1 = exact or next smallest (default), 0 = exact match, -1 = exact or next largest.
The MATCH function is used to determine the position of a value in an 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) // returns 5
The MATCH function can perform exact and approximate matches, and supports wildcards (* ?) for partial matches. There are 3 separate match modes (set by the match_type argument), as described below.
Frequently, the MATCH function is combined with the INDEX function in order to retrieve a value at a certain (matched) position. In other words, MATCH figures out the position, and INDEX returns the value at that position. For a detailed explanation, see How to use INDEX and MATCH.
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:
|1||Approximate||MATCH finds the largest value less than or equal to lookup value. Lookup array must be sorted in ascending order.|
|0||Exact||MATCH finds the first value equal to lookup value. Lookup array does not need to be sorted.|
|-1||Approximate||MATCH finds the smallest value greater than or equal to lookup value. Lookup array must be sorted in descending order.|
|Approximate||When match type is omitted, it defaults to 1 with behavior as explained above.|
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.
Below are simple examples of how the MATCH function can be used to return the position of values in a range. Further down the page are more advanced examples of how MATCH can be used to solve real-world problems.
When match type is set to zero, MATCH performs an exact match. In the example below, the formula in E3 is:
In the formula above, the lookup value comes from cell E2. If the lookup value is hardcoded into the formula, it must be enclosed in double quotes ("") , since it is a text value:
Note: MATCH is not case-sensitive, so "Mars" and "mars" will both return 4.
When match type is set to 1, MATCH will perform an approximate match on values sorted A-Z, finding the largest value less than or equal to the lookup value. In the example shown below, the formula in E3 is:
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:
This is equivalent to:
See below for more examples of the MATCH function.
- 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.