| Syntax |
| =XMATCH(lookup_value,lookup_array,[match_mode],[search_mode]) |
| 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. Wildcard characters can be used for text string searching if match_mode is set to 2. |
| 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_mode] |
number |
An optional constant, reference or nested expression that returns the flag for the type of match to be performed. Defaults to 0 if omitted.
|
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, can work on both sorted and unsorted data. Relevance of sort order depends upon the search_mode argument.
The match is made with largest value, less than or equal to the lookup_value. If there are duplicates, whether the match is on first or last depends upon the search_mode argument. |
| 1 |
Threshold Match, can work on both sorted and unsorted data. Relevance of sort order depends upon the search_mode argument.
The match is made with smallest value, greater than or equal to the lookup_value. If there are duplicates, whether the match is on first or last depends upon the search_mode argument. |
| 2 |
Wildcard searching, allows wildcard matching on text strings, which is not permitted in other match_modes. |
|
| [search_mode] |
number |
An optional constant, reference or nested expression that returns the flag for the type of search to be performed. Defaults to 1 if omitted.
| 1 |
Exhaustive search, entry by entry from first to last. Sort order of data is irrelevant. |
| -1 |
Exhaustive search, entry by entry from last to last. Sort order of data is irrelevant. |
| 2 |
Binary search, sampling entries. Data must be sorted in Ascending Order for the results to be valid and reliable. |
| -2 |
Binary search, sampling entries. Data must be sorted in Descending Order for the results to be valid and reliable. |
|
| 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 |
#VALUE! |
If the values passed to match_mode or search_mode are not numbers and cannot be coerced to numbers. |
| error |
|
Any errors referenced by arguments 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. |
| Advice: |
To get exactly the same answers as were achieved with the old MATCH function, a specific combination of match_mode and search_mode is required.
| Settings for MATCH |
Settings for XMATCH |
| match_type |
1 |
|
match_mode |
-1 |
search_mode |
2 |
| match_type |
|
|
match_mode |
|
search_mode |
1 or -1 |
| match_type |
-1 |
|
match_mode |
1 |
search_mode |
-2 |
|
| Advice: |
As a general rule, nest a XMATCH 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: |
XMATCH matches on empty cells, so some care and changes will be required if migrating from older MATCH functions that did not have this behaviour. |
| Examples: |
Open / download sample workbook: MATCH, XMATCH.xlsx |
| |