XMATCH, explained properly

XMATCH returns the position of an item in a range: =XMATCH(D2,A:A) finds where D2 appears in column A. Unlike MATCH it defaults to an exact match, it can search from the bottom up with a -1 search mode, and it supports wildcards natively. It is available in Excel 365 and Excel 2021, and in Google Sheets.

Find the position of a value

=XMATCH(D2,A:A)

Exact match by default — no third argument needed, unlike MATCH.

Try it with your data

Edit the grid or formula, then run it through a real spreadsheet engine — no signup.

Sample data — click any cell to edit

Cell row 1 column 1	Cell row 1 column 2	Cell row 1 column 3	Cell row 1 column 4
Cell row 2 column 1	Cell row 2 column 2	Cell row 2 column 3	Cell row 2 column 4
Cell row 3 column 1	Cell row 3 column 2	Cell row 3 column 3	Cell row 3 column 4
Cell row 4 column 1	Cell row 4 column 2	Cell row 4 column 3	Cell row 4 column 4
Cell row 5 column 1	Cell row 5 column 2	Cell row 5 column 3	Cell row 5 column 4

Formula

Runs server-side · free · no signup

Syntax and the four arguments

XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]). Only the first two are required, and the defaults are the ones you almost always want.

match_mode: 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard. search_mode: 1 first to last (default), -1 last to first, 2 and -2 binary search on sorted data.

The default matters. MATCH defaults to approximate match, which silently returns wrong answers on unsorted data — a bug that has cost people a great deal of trust in spreadsheets. XMATCH defaults to exact, so the safe behaviour is the one you get for free.

Exact match (the default)

=XMATCH(D2,A:A)

Next smaller value — useful for banding

=XMATCH(D2,A:A,-1)

Finds the largest entry that does not exceed D2. The classic tax-band or shipping-tier lookup.

Search from the bottom up

=XMATCH(D2,A:A,0,-1)

Returns the LAST occurrence rather than the first — the most-recent-record pattern.

Wildcard match

=XMATCH("north*",A:A,2)

match_mode 2 turns on * and ?. They are inert in the other modes.

XMATCH vs MATCH

XMATCH defaults to an exact match, while MATCH defaults to an approximate match if its match_type is omitted. XMATCH also offers a reverse-search mode and a dedicated wildcard match mode. MATCH can match wildcards too when match_type is 0, so wildcard support alone is not a reason to switch. Choose the match and search modes deliberately, and check compatibility with the spreadsheet versions your readers use.

The reverse-search mode is the one with no MATCH equivalent at all. Finding the last matching row previously meant a LOOKUP(2,1/(...)) construction that almost nobody could read; =XMATCH(D2,A:A,0,-1) says the same thing plainly.

The one reason to stay on MATCH is compatibility. If the file will be opened in Excel 2019 or earlier, XMATCH resolves to #NAME? and the workbook is broken for that reader.

Last matching row, the old way

=LOOKUP(2,1/(A:A=D2),ROW(A:A))

Works everywhere, readable by almost nobody.

Last matching row, the XMATCH way

=XMATCH(D2,A:A,0,-1)

XMATCH with INDEX — and why you might still use it over XLOOKUP

XLOOKUP replaces most INDEX/MATCH pairs, so the reasonable question is why XMATCH exists at all. The answer is that XMATCH returns a position, not a value, and positions compose.

The two-dimensional lookup is the clearest case: one XMATCH down the rows, one across the headers, both fed into a single INDEX. XLOOKUP can do this by nesting one call inside another, but the INDEX/XMATCH form is easier to read and evaluates the array once.

The other case is performance on large sheets. If you need six columns back from the same matched row, one XMATCH into six INDEX calls beats six separate XLOOKUPs, because the search runs once instead of six times.

Two-dimensional lookup

=INDEX(B2:G100,XMATCH(I1,A2:A100),XMATCH(I2,B1:G1))

Row position and column position, resolved independently, into one INDEX.

One search, several columns returned

=INDEX(B:B,$H$1)

With $H$1 holding =XMATCH(D2,A:A). Change B:B per column; the search never repeats.

Correct, but depends on values from your own sheet, so we cannot run it here.

Multiple criteria, without a helper column

=INDEX(C:C,XMATCH(1,(A:A=F1)*(B:B=F2),0))

Multiplying the two conditions gives 1 only where both hold. This is also the answer to "XLOOKUP with multiple criteria".

Availability and errors

XMATCH is in Excel 365, Excel 2021 and later, Excel for the web, and Google Sheets. It is not in Excel 2019, 2016 or any earlier version, and it is not in LibreOffice Calc before 7.6.

#NAME? means the version you are on does not have the function — the formula itself is fine. #N/A means the value genuinely is not in the range; check for trailing spaces and for numbers stored as text, which are the two causes behind most phantom misses.

Handle a genuine miss

=IFERROR(XMATCH(D2,A:A),"Not found")

Match despite trailing spaces

=XMATCH(TRIM(D2),A:A)

XMATCH match and search modes

Argument	Value	Meaning
match_mode	0	Exact match (default)
match_mode	-1	Exact, or the next smaller value
match_mode	1	Exact, or the next larger value
match_mode	2	Wildcard match — * and ? become active
search_mode	1	First to last (default)
search_mode	-1	Last to first — returns the last match
search_mode	2 / -2	Binary search; requires sorted data

Frequently asked

What does XMATCH do?

It returns the position of a value within a range — for example =XMATCH(D2,A:A) returns 5 if D2 is found in the fifth row of column A. It does not return the value itself; pair it with INDEX for that.

What is the difference between XMATCH and MATCH?

XMATCH defaults to exact match where MATCH defaults to approximate, supports searching from the bottom up, and handles wildcards through a dedicated match mode. MATCH remains the only option in Excel 2019 and earlier.

Should I use XMATCH or XLOOKUP?

XLOOKUP for a straightforward one-value lookup. XMATCH when you need the position itself — for a two-dimensional lookup, or to reuse one search across several returned columns.

Why does XMATCH return #NAME?

The Excel version in use does not have the function. XMATCH requires Excel 365 or Excel 2021; in Excel 2019 and earlier, use MATCH instead.

Does XMATCH work in Google Sheets?

Yes, with the same syntax and the same match and search modes.

Stop writing these by hand

Describe what you need in plain English and FormulaCraft returns the formula, already run through a real spreadsheet engine. Free, no account.

Last reviewed 2026-08-23. Every formula here is executed against sample data by our verification engine as part of the build. The few that cannot be — because they need values from your own sheet, or because the engine has no implementation for that form — say so directly underneath, rather than being counted as verified.