The Excel XMATCH function returns the position of a value in a row or column — it’s MATCH rebuilt for this decade. Exact match is the default, it can search from the end of a list, and its next-smaller/next-larger modes don’t need sorted data. Pair it with INDEX for the modern version of Excel’s most flexible lookup.
Syntax
| Argument | Description | |
|---|---|---|
lookup_value | Required | The value to find. |
lookup_array | Required | A single row or single 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 = search first-to-last (default) · -1 = search last-to-first · 2 = binary search, data sorted ascending · -2 = binary search, descending. |
Available in: Excel for Microsoft 365, Excel 2021+, and Excel for the web. For older versions, use MATCH — just remember its third argument.
Find a position — exact by default
The shipping table below lists order sizes in A2:A7 (note the duplicate 35). Where does 35 first appear?
| A | B | D | E | ||
|---|---|---|---|---|---|
| 1 | Order size | Carrier | Find | Position | |
| 2 | 15 | Redline | 35 | 3 | |
| 3 | 28 | Polar | |||
| 4 | 35 | FastShip | |||
| 5 | 35 | Redline | |||
| 6 | 52 | Polar | |||
| 7 | 75 | FastShip |
To find the last 35 instead — say, the most recent order of that size — flip search_mode to -1:
And when 40 isn’t in the list, the match modes pick a neighbor — no sorting required:
Try it: interactive XMATCH demo
Search the order sizes above (15, 28, 35, 35, 52, 75). Change the value and modes — notice what the duplicate 35 does to each search direction.
XMATCH vs MATCH: what actually changed
Same job, very different defaults and reach:
| Capability | XMATCH | MATCH |
|---|---|---|
| Default match type | Exact | Approximate (silent wrong answers on unsorted data) |
| Next-smaller / next-larger match | Any order — no sorting needed | Requires sorted data |
| Search from the end | Yes (search_mode -1) | No |
| Fast binary search modes | Yes (search_mode 2 / -2, explicit) | Implied by match_type, easy to misuse |
| Wildcards | Explicit mode 2 | With match_type 0 |
| Works in Excel 2019 and older | No | Yes |
The headline fix is the default. =MATCH(x, range) quietly does an approximate match; =XMATCH(x, range) does what everyone expected all along. Paired with INDEX it makes the classic combo safer and stronger:
Already on 365? If you only need the matched value, not its position, XLOOKUP does it in one step — it shares the same match_mode and search_mode arguments.
Wildcards and binary search modes
With match_mode 2, wildcards work in the lookup value: * for any characters, ? for exactly one, and ~ to escape a literal * or ?:
The binary modes (search_mode 2 and -2) split sorted data in half repeatedly — dramatically faster on hundred-thousand-row lookups, but they require sorted data and return wrong positions without complaint if it isn’t. On modern hardware, default linear search is fine for all but the biggest models.
Errors & common pitfalls
#N/A — no match. With exact mode, any miss returns #N/A — check for stray spaces or text-formatted numbers, or wrap in IFNA. With modes -1/1, #N/A means there was no smaller/larger neighbor at all.
#NAME? — older Excel. XMATCH needs Excel 2021 or Microsoft 365. Files shared with Excel 2019 users need MATCH instead.
Pitfall: binary search on unsorted data. search_mode 2/-2 assumes sorted data and returns wrong positions silently when it isn’t. Unless you have a proven speed problem, leave search_mode alone.
Pitfall: position, not worksheet row. Like MATCH, XMATCH counts within the range you give it. If the range starts at A5, a result of 1 means worksheet row 5.
Pitfall: wildcards need mode 2. In exact mode, an asterisk is just an asterisk. =XMATCH("Fast*", range) looks for the literal text "Fast*" unless match_mode is 2.
Practice workbook
Frequently asked questions
What's the difference between XMATCH and MATCH?
Which Excel versions have XMATCH?
How do I find the last occurrence of a value?
=XMATCH(value, range, 0, -1) searches bottom-up and returns the position of the last match — something plain MATCH simply cannot do.Does XMATCH need sorted data?
Should I use XMATCH or XLOOKUP?
Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class