XMATCH Function in Excel
XMATCH is the modern replacement for MATCH. Same core purpose — find the position of a value in a range — but with better defaults (exact match), wildcard support built in, and a reverse-search option. Excel 2021 and 365 only.
What it does: Returns the position of lookup_value in lookup_array. Defaults to exact match (unlike MATCH, which defaults to approximate). Supports wildcards natively and can search backward from the end of the array.
What XMATCH improves over MATCH
MATCH has been Excel's position-finder since 1997. XMATCH fixes its three biggest problems:
- Default is exact match, not approximate. MATCH's approximate default has caused decades of silent wrong-answer bugs. XMATCH defaults to
0(exact) — safer out of the box. - Wildcards without needing exact-match specification. MATCH required setting match_type to 0 to use wildcards; XMATCH has a dedicated wildcard mode via
match_mode. - Reverse search. XMATCH can search from the end of the array to find the last occurrence of a value — impossible with MATCH.
search_mode set to -1 does it directly: =XMATCH("Chen", A:A, 0, -1) returns the position of the last "Chen" in column A.
Syntax breakdown
lookup_value Required
What to search for. Can be a number, text, logical value, or cell reference. Case-insensitive by default (like MATCH).
lookup_array Required
The range to search. Single row or single column — same restriction as MATCH.
match_mode Optional
How to match: 0 (default) exact match; -1 exact or next smaller (returns position of largest ≤ lookup); 1 exact or next larger; 2 wildcard match with * and ?. Note: modes -1 and 1 work on unsorted data (unlike MATCH's approximate mode).
search_mode Optional
Direction and algorithm: 1 (default) search first-to-last; -1 search last-to-first (returns position of last match); 2 binary search ascending (fast on sorted data); -2 binary search descending. Use -1 for "last occurrence" searches; the binary modes only pay off on very large sorted arrays.
5 real-world examples
Example 1: Simple position lookup with safer default
Find "Chen" in a name column:
=XMATCH("Chen", A2:A100)Result: 4 — position of "Chen". Notice: no third argument needed. XMATCH defaults to exact match, unlike MATCH which would need =MATCH("Chen", A2:A100, 0).
Example 2: Wildcard search built in
Find the first email address that ends in ".gov":
=XMATCH("*.gov", C2:C1000, 2)Result: Position of the first matching email. The 2 match mode explicitly enables wildcards — cleaner than MATCH's implicit wildcard-with-exact-match behavior.
Example 3: Find the LAST occurrence, not the first
You have a transaction log with repeated customer IDs. Find the most recent (last) transaction for customer 8347:
=XMATCH(8347, A2:A1000, 0, -1)Result: Position of the last row where A contains 8347. The -1 search mode makes XMATCH scan from the end backwards. Impossible with MATCH.
Example 4: Approximate-match on unsorted data
Find the position of the largest value ≤ 500 in an unsorted price column:
=XMATCH(500, B2:B100, -1)Result: Position of that value. Unlike MATCH, XMATCH's approximate modes (-1 and 1) don't require sorted data. This makes tier lookups safe on any list order.
Example 5: XMATCH inside INDEX
The classic INDEX + MATCH pattern, upgraded:
=INDEX(D2:D100, XMATCH(F2, A2:A100))Result: Value from D corresponding to the row where A equals F2. Reads more clearly than MATCH(F2, A2:A100, 0) because the exact-match specification is now default and invisible.
Common errors and how to fix them
XMATCH doesn't exist in your Excel version. Requires Excel 2021 or 365. In older versions, use MATCH with explicit 0 for exact match: =MATCH(value, range, 0).
Value not found. Common causes: trailing spaces, case mismatch (rare — XMATCH is case-insensitive), or genuine absence. Wrap in IFERROR: =IFERROR(XMATCH(...), "Not found").
The lookup_array is 2D. Same restriction as MATCH — must be a single row or single column. For 2D lookups, use two XMATCHes with INDEX.
You forgot to set match_mode to 2. Unlike MATCH, XMATCH's wildcards only work when you explicitly enable them. Fix: add the third argument: =XMATCH("Ali*", A:A, 2).
XMATCH vs MATCH: side by side
| Feature | MATCH | XMATCH |
|---|---|---|
| Default match mode | Approximate (dangerous) | Exact (safer) |
| Wildcards | Only in exact-match mode | Explicit mode 2 |
| Reverse search | Not possible | Yes, with search_mode -1 |
| Binary search on sorted data | Not available | Yes, with search_mode 2 or -2 |
| Approximate on unsorted data | No — requires sorted data | Yes — modes -1 and 1 |
| Excel version | All versions | 2021+ / 365 only |
Version compatibility
Download the practice workbook
Every example above, plus MATCH vs XMATCH side-by-side comparisons and a "last occurrence" template.
Related functions
Frequently asked questions
Should I always use XMATCH instead of MATCH?
Yes, if your workbook only needs to run on Excel 2021 or newer. XMATCH is safer (exact match by default) and more capable. Keep MATCH for workbooks that must run on Excel 2019 or earlier.
Can XMATCH find multiple matches?
No — like MATCH, XMATCH returns only one position. The search_mode controls whether that's the first (-1: last, 1: first). For multiple matches, use FILTER with ROW to get all matching row numbers.
What are the binary search modes for?
Speed. On very large sorted arrays (100,000+ rows), binary search is dramatically faster than linear scan. Modes 2 (ascending) and -2 (descending) require the data to actually be sorted. Wrong sort order gives wrong results silently. For typical workbooks under 10,000 rows, the difference is imperceptible — stick with default.
How does XMATCH's approximate mode differ from MATCH's?
MATCH's approximate mode (match_type 1 or -1) requires sorted data and gives wrong answers on unsorted data. XMATCH's approximate modes (-1 and 1) work correctly regardless of sort order — Excel scans linearly. This makes tiered lookups (finding the tier a value falls into) safe on any list.
Should I use XMATCH + INDEX or XLOOKUP?
XLOOKUP for straight lookups where you want a value. XMATCH + INDEX when you need the position number itself (e.g., for further calculations), or when you need XMATCH's specific search modes that XLOOKUP doesn't expose. XLOOKUP is simpler for most cases.
Does XMATCH support case-sensitive matching?
Not directly. Both MATCH and XMATCH are case-insensitive. For case-sensitive position lookup, use SUMPRODUCT with EXACT: =SUMPRODUCT(--EXACT(A:A, "alice") * ROW(A:A)) — awkward but works.
Why does my XMATCH with wildcards not work?
Because you didn't set the match_mode to 2. XMATCH only treats wildcards specially when told to — the default mode (0) treats * and ? as literal characters. Add , 2 as the third argument to enable wildcard matching.
Modernize your lookup formulas — with the Sheets & Cells AI Add-in
Highlight any MATCH formula and the Add-in rewrites it as XMATCH with the correct match_mode and search_mode arguments. Plus explains what changed. All inside Excel.
Learn about the Add-in →