XMATCH function
XMATCH is the modern MATCH: it returns the position of a value in a list, but it defaults to an exact match, can search from the bottom up, and handles wildcards and next-larger or next-smaller matches without sorted data. Pair it with INDEX the same way you would pair MATCH.
- Available in
- Excel 2021, 2024 and Microsoft 365
- Category
- Lookup functions
- Returns
- The position of the match within lookup_array, or #N/A when there is no match.
- Google Sheets
- Available in Google Sheets with the same arguments.
Syntax
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
| Argument | Required? | What it is |
|---|---|---|
lookup_value | Required | The value to find. |
lookup_array | Required | The single row or column to search. |
match_mode | Optional | 0 exact match (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard match. |
search_mode | Optional | 1 searches first to last (default), -1 last to first, 2 or -2 a binary search on sorted data. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | =XMATCH("Ito",B2:B5) | 2 | Exact match is the default, so no third argument is needed |
| E3 | =XMATCH("Halvorsen",B2:B5,0,-1) | 3 | Searching from the bottom finds Halvorsen's latest order, in position 3 |
| E4 | =INDEX(C2:C5,XMATCH("Halvorsen",B2:B5,0,-1)) | $980 | With INDEX: the amount of that latest order |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Date | Customer | Amount | Result | |
| 3/2/2026 | Halvorsen | $420 | 2 | |
| 3/5/2026 | Ito | $1,150 | 3 | |
| 3/9/2026 | Halvorsen | $980 | $980 | |
| 3/12/2026 | Mbeki | $260 |
Practice XMATCH
Graded instantly, then re-checked on changed data.
Latest price paid
F2, return the most recent unit cost for the supplier in E2: use INDEX with XMATCH searching from the bottom.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #NAME? | XMATCH needs Excel 2021, Excel 2024 or Microsoft 365. In older versions use MATCH with 0 as the third argument. |
| Copying MATCH's match type | MATCH's 1 means "approximate, sorted ascending", but XMATCH's 1 means "exact or next larger". Converting old formulas, change MATCH(x,range,0) to XMATCH(x,range) and recheck any 1 or -1. |
| #N/A | No match, often because of a trailing space or a number stored as text. |
Tips
- search_mode -1 returns the last occurrence, which is the latest entry in a log sorted oldest first.
- Use match_mode 2 for wildcards: =XMATCH("Hal*",B2:B5,2).
Questions
What is the difference between MATCH and XMATCH?
XMATCH defaults to an exact match, can search last to first, and finds next-larger or next-smaller values without sorted data. MATCH works in every version of Excel.
How do I find the last match in a list?
Use XMATCH with search_mode -1: =XMATCH(E2,B2:B100,0,-1). Wrap it in INDEX to return a value from that row.
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.