Lookup & Reference
Modern
XMATCH
Returns the relative position of an item in a range or array (modern version of MATCH).
Available in Microsoft 365 · Excel 2024 · Excel 2021 · Excel for the web. Not in Excel 2016/2019 desktop.
Syntax
XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])What it returns
The relative position of the item, as a number.
Arguments
| Argument | Required | Description |
|---|---|---|
lookup_value |
Yes | The value to find. |
lookup_array |
Yes | The range or array to search in. |
match_mode |
No | 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard. |
search_mode |
No | 1 first to last (default), -1 last to first, 2 binary (ascending), -2 binary (descending). |
Example
Lookup value
Apple
| A | |
|---|---|
| 1 | Fruit |
| 2 | Apple |
| 3 | Banana |
| 4 | Cherry |
Position
1
Find Apple, return its position in the list (1).
=XMATCH("Apple", A2:A10)Important to know
Returns #N/A if no match in exact mode.
search_mode 2/-2 (binary) requires sorted data.