The XMATCH function in Excel
XMATCH is the modern MATCH. It returns the position of a value in a row or column, and it matches exactly by default, so you don't need to add a 0.
Together with INDEX it gives a flexible lookup. It is available in Excel 2021, Excel 2024 and Microsoft 365.
Syntax
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
| Argument | What it means |
|---|---|
| lookup_value | What to find. |
| lookup_array | The single row or column to search in. |
| match_modeoptional | 0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard. |
| search_modeoptional | 1 = from the first item (default), -1 = from the last item, 2 / -2 = binary search on sorted data. |
Examples
1. Position of a value
| A | B | C | |
|---|---|---|---|
| 1 | Fruit | Position | |
| 2 | Apple | 2 | |
| 3 | Fig | ||
| 4 | Kiwi | ||
| 5 | Plum |
"Fig" is the second item. No 0 is needed because exact matching is the default.
2. Exact or next smaller
| A | B | C | |
|---|---|---|---|
| 1 | Limit | Position of 55 | |
| 2 | 0 | 3 | |
| 3 | 25 | ||
| 4 | 50 | ||
| 5 | 75 |
55 isn't in the list, so match mode -1 takes the next smaller value, 50, which is third.
3. Search from the bottom
| A | B | C | |
|---|---|---|---|
| 1 | Fruit | Last Fig | |
| 2 | Apple | 4 | |
| 3 | Fig | ||
| 4 | Kiwi | ||
| 5 | Fig | ||
| 6 | Plum |
Search mode -1 starts at the last item, so it finds the second Fig first: position 4.
4. Pair it with INDEX
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Fruit | Price | Kiwi price | |
| 2 | Apple | 1.2 | 0.8 | |
| 3 | Fig | 3.5 | ||
| 4 | Kiwi | 0.8 | ||
| 5 | Plum | 2 |
XMATCH finds the row (3), and INDEX returns the price in that row.
Common mistakes
Searching a multi-column range. XMATCH needs a single row or a single column.
Fix: Pass only the column you search in, and use INDEX for the column you return.
Opening the file in Excel 2019 or older. The cells show #NAME?.
Fix: Use MATCH when the file is shared with older versions.
Frequently asked questions
What is the difference between MATCH and XMATCH?
XMATCH matches exactly by default, can search from the end, supports wildcards with match mode 2, and doesn't require sorted data for approximate matches.
Related functions
- MATCHMATCH returns the position of a value in a list, for example that "Lena" is the 3rd name.
- INDEXINDEX returns the value at a given row and column of a range.
- XLOOKUPXLOOKUP searches one column or row and returns the matching value from another, in any direction, with a built-in 'not found' message.