| Syntax |
| =MATCH(lookup_value,lookup_array,[match_type]) |
| Purpose / Description |
| Returns the position of the searched for item within the one-row or one-column array searched. |
| Arguments |
| Name |
Type |
Description |
| lookup_value |
any |
A constant, reference or nested expression that returns the value to be found. If it is a text string, it may include wild-card characters — * = any character or characters, ? = any one character — in which case a wildcard search is performed. |
| lookup_array |
reference or array of values |
A constant array, reference or nested expression that returns the set of values within which the matching is to be performed. |
| [match_type] |
number |
An optional constant, reference or nested expression that returns the flag for the type of match to be performed. Defaults to 1 if omitted.
| 1 |
Threshold Match on data sorted in Ascending Order. If data is not sorted correctly, the result will be unreliable.
The match is made with the last (where duplicates appear) largest value, less than or equal to the lookup_value. |
|
Exact Match, data order is irrelevant.
The match is made on the first entry in the searched range of the same value type that exactly matches lookup_value. |
| -1 |
Threshold Match on data sorted in Descending Order. If data is not sorted correctly, the result will be unreliable
The match is made with the last (where duplicates appear) smallest value, greater than or equal to the lookup_value. |
|
| Return Values |
| Type |
Specific Value |
Description |
| number |
|
The position of the matched item within the lookup array or range, if matched. |
| error |
#N/A |
If no match is found, this error is returned. |
| error |
#REF! |
If the value passed to match_type is an error, this error value is returned. |
| error |
|
Any errors referenced by lookup_value are returned due to propagation of error values. |
| Notes / Comments / Advice / Warnings |
| Note: |
The value type of the lookup_value is important. As the argument is specified as "any", it will match numbers, text strings and logicals, but only against the same value types. A text_string will notmatch against a number and vice versa. |
| Comment: |
We use the term "Threshold Match" to describe the basis of matching where an inexact match is made. In the online help and in the tooltips, this is described as approximate match, but we feel this isn't a useful description. The value 2.99 is more approximately 3, but will match on the threshold value 2. |
| Advice: |
We recommend always specifying three arguments for the MATCH function, including the optional match_type, so that it is clear to those reviewing the formulas, the basis on which the match is expected to be made. |
| Advice: |
As a general rule, nest a MATCH inside an IFNA function to trap possible mismatches, and provide a default value or return a value to indicate no match instead of #N/A. |
| Caution: |
The default match_type is 1, not 0 as many users expect. It is important to specify the match_type! |
| Examples: |
Open / download sample workbook: MATCH, XMATCH.xlsx |
| |