Lookup & reference
How to Use XMATCH in Excel (the Modern MATCH)
XMATCH is the modern replacement for MATCH. It returns the POSITION of a value in a list, which you then feed to another function such as INDEX to pull back the matching result.
It defaults to exact match, can search from the bottom up to find the most recent entry, and supports wildcard and approximate modes. This guide shows the syntax and the patterns you will actually use.
XMATCH syntax
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]) | Argument | Required | What it does |
|---|---|---|
lookup_value | Required | The value to search for. |
lookup_array | Required | The single column or row to search in. |
match_mode | Optional | 0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard. |
search_mode | Optional | 1 = first to last (default), -1 = last to first, 2/-2 = binary search on sorted data. |
Note: Available in Microsoft 365, Excel 2021+, and Google Sheets. Older versions show #NAME?; use MATCH instead.
Examples
Real, copy-paste-ready formulas.
1. Find the position of a value
=XMATCH(E2, A2:A100) Returns the position (1, 2, 3…) of E2 inside A2:A100. Exact match is the default, so there is no need for a 0 flag like the old MATCH.
2. Pair XMATCH with INDEX to return a value
=INDEX(C2:C100, XMATCH(E2, A2:A100)) XMATCH finds the row of E2, then INDEX returns the value at that row in C2:C100. This combo can look left or right, unlike VLOOKUP.
3. Find the LAST match (most recent entry)
=XMATCH(E2, A2:A100, 0, -1) A search_mode of -1 scans from the bottom up, so when a value appears several times you get the most recent occurrence instead of the first.
4. Approximate match on sorted data
=XMATCH(B2, $E$2:$E$6, -1) match_mode -1 returns the position of the largest value that is less than or equal to B2, which is ideal for grade bands or tax brackets on a sorted list.
How to write XMATCH step by step
- 1
Click the result cell and type =XMATCH(
- 2
Click the cell with the value you are searching for, then a comma.
- 3
Select the single column or row to search in (the lookup_array).
- 4
Optionally add a comma and a match_mode (0 for exact), then a comma and a search_mode (-1 for last to first).
- 5
Close the bracket ) and press Enter. To return a value, wrap it in INDEX(return_range, …).
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#N/A | No match was found, often due to extra spaces, a number stored as text, or the value simply not being in the list. | Clean values with TRIM(), confirm both sides are the same data type, or wrap the formula in IFNA for a friendly fallback. |
#NAME? | Your Excel version does not have XMATCH. | Update to Microsoft 365 or Excel 2021+, or fall back to MATCH with a 0 third argument for an exact match. |
#VALUE! | match_mode or search_mode is invalid, or the lookup_array spans more than one column and row. | Use a valid mode number and make sure lookup_array is a single column or a single row. |
Frequently asked questions
What is the difference between XMATCH and MATCH?
XMATCH defaults to an exact match (MATCH defaults to approximate), can search from the bottom up with search_mode -1, and supports wildcard and binary-search modes. MATCH still works, but XMATCH is cleaner and less error-prone.
What is the difference between XMATCH and XLOOKUP?
XMATCH returns the POSITION of a value, while XLOOKUP returns the VALUE itself. Use XMATCH when you need an index number, often to feed INDEX or to drive a two-way lookup.
Which Excel versions have XMATCH?
XMATCH is available in Microsoft 365 and Excel 2021 or later. It is not in Excel 2019 or earlier, where you should use MATCH instead. Google Sheets also supports XMATCH.
How do I get the last match instead of the first?
Set the fourth argument, search_mode, to -1. This scans the list from last to first, so a value that appears several times returns its most recent position.