XMATCH function in Excel
Returns the relative position of an item in an array or range of cells.
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])XMATCH syntax and parameters
Four arguments, two required.
- lookup_valueanyRequired
The lookup value
- lookup_arrayarrayRequired
The array or range to search
- match_modenumberOptional
Specify the match type:0 - Exact match (default)-1 - Exact match or next smallest item1 - Exact match or next largest item2 - A wildcard match where *, ?, and ~ have special meaning.
- search_modenumberOptional
Specify the search type:1 - Search first-to-last (default)-1 - Search last-to-first (reverse search).2 - Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.-2 - Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.
XMATCH examples
Formulas you'll actually reuse.
Which row holds the Q3 actuals? Exact match by default:
=XMATCH("Q3 actuals", A2:A40)Result
23Search bottom-up to find a customer's most recent order row:
=XMATCH(F2, B2:B200, 0, -1)Result
187Which salary band does 42,500 fall into? match_mode 1 takes the next larger value, no sorting needed:
=XMATCH(42500, D2:D8, 1)Result
3First row whose id starts with INV-2026-, using a wildcard (match_mode 2):
=XMATCH("INV-2026-*", A2:A500, 2)Result
118A two-way lookup: XMATCH finds the product row and the month column, INDEX reads the crossing cell. Exact match is the default, unlike MATCH:
=INDEX(B2:M20, XMATCH(P2, A2:A20), XMATCH(Q1, B1:M1))Result
18,240
XMATCH in Google Sheets
Same name — your formula ports as-is.
Try XMATCH in the playground
Edit the example — nothing to install.
Preloaded with the XMATCH formula from Example 1 — change anything and watch it respond.
XMATCH errors
What they mean — and the fixes.
#N/ANo exact match found — check for stray spaces, or pass match_mode 1/-1 to accept the next larger/smaller value.
#NAME?XMATCH needs Excel 2021, Microsoft 365 or Google Sheets. On older Excel use MATCH(value, range, 0), which is exact-match too once you pass the 0.
#N/AA wildcard pattern like "INV-*" only matches with match_mode 2; with the default exact match the asterisk is a literal character.
Related functions
More ways Excel gets this done.
- ADDRESSReturns a reference as text to a single cell in a worksheetLookupGoogle Sheets
- AREASReturns the number of areas in a referenceLookupNo Google Sheets equivalent
- CHOOSEChooses a value from a list of valuesLookupGoogle Sheets
- CHOOSECOLSReturns the specified columns from an arrayLookupGoogle Sheets
- CHOOSEROWSReturns the specified rows from an arrayLookupGoogle Sheets
- COLUMNReturns the column number of a referenceLookupGoogle Sheets