XMATCH function
Quick answer
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.
- Syntax
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])- Most common use
=XMATCH("Ito",B2:B5)returns 2 (Exact match is the default, so no third argument is needed)
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.
Answers that use XMATCH
In other languages
XMATCH is COINCIDIRX in Spanish Excel and XVERGLEICH in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .