XMATCH Function in Excel: Find a Position

The XMATCH function in Excel finds a value in a list and returns its position, such as 2 for the second item. It is the modern version of MATCH: it does an exact match by default, can search from the bottom up, and understands wildcards. This guide shows each option on one small order list, and how to combine XMATCH with INDEX to return a value.

XMATCH needs Excel 2021, Excel 2024, Microsoft 365 or Excel for the web. In older versions, use MATCH instead.

What does XMATCH return?#

XMATCH returns a position, not a value. If you ask where "Omar" is in a list of names and he is second, XMATCH returns 2. On its own, that number answers questions like "which row is this in?" or "is this value in the list at all?". Combined with INDEX, it becomes a full lookup.

The syntax has two required parts and two optional ones:

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

All the examples use this order list in A1:C7:

How to find a position with XMATCH#

For a basic exact match, give XMATCH the value and the range:

=XMATCH("Omar",B2:B7)

The result is 2. Omar is in B3, which is the second cell of B2:B7. The count starts at the first cell of the range, not at row 1 of the sheet.

XMATCH returns the first match it finds, so Omar's second order in B6 is ignored. If the value isn't in the list, the result is #N/A. That makes XMATCH a quick way to check membership: =ISNUMBER(XMATCH("Ravi",B2:B7)) returns TRUE if Ravi is in the list.

XMATCH is not case-sensitive, so "omar" finds "Omar" too.

XMATCH can also look up several values at once. =XMATCH({"Ravi","Omar"},B2:B7) returns 4 and 2, spilled into two cells side by side. Point the first argument at a range of names instead, and you get one position per name.

How to find the last match with search_mode -1#

Set search_mode to -1 to search from the bottom up. This finds the last occurrence, such as a customer's most recent order:

=XMATCH("Lena",B2:B7,0,-1)

The result is 6. Even though XMATCH started at the bottom, the position is still counted from the top: Lena's last order is the sixth row of the range. The 0 keeps the match exact. You must type it, because search_mode is the fourth argument and match_mode sits before it.

The guide to looking up the last match shows more ways to do this, including in older Excel.

Next smaller, next larger and wildcard matches#

match_mode controls what counts as a match when the exact value isn't there.

The amounts in column C aren't sorted, and that's fine. XMATCH's next smaller and next larger modes find the closest value anywhere in the list. MATCH is stricter: its types 1 and -1 need sorted data, as explained in MATCH type 0 vs 1 vs -1.

In wildcard mode, * stands for any number of characters, ? for exactly one, and ~ lets you search for a real asterisk or question mark. Wildcards only work when match_mode is 2.

How to use XMATCH with INDEX#

XMATCH finds the row, and INDEX returns the value from that row in another column. To get the amount of Lena's last order:

=INDEX(C2:C7,XMATCH("Lena",B2:B7,0,-1))

XMATCH returns 6, and the sixth value of C2:C7 is 180. Both ranges must start on the same row, or INDEX will pick the wrong cell.

For a two-way lookup, use one XMATCH for the row and another for the column: =INDEX(A2:C7,XMATCH(1004,A2:A7),XMATCH("Amount",A1:C1)) returns 95. The INDEX MATCH guide explains this pattern step by step, and it extends to lookups with multiple criteria.

XMATCH vs MATCH: what changed#

XMATCH fixes the main traps in MATCH:

Forgetting the 0 in MATCH is a classic mistake that gives wrong answers with no error. XMATCH avoids it. For a full side-by-side, read MATCH vs XMATCH, and for MATCH on its own see the MATCH function in Excel and the MATCH function guide.

Common XMATCH mistakes#

1. Using a range with several columns. XMATCH searches one row or one column. B2:C7 returns #VALUE!.
2. Skipping match_mode when setting search_mode. =XMATCH("Lena",B2:B7,-1) sets match_mode to -1, not search_mode. Write 0,-1.
3. Expecting a value instead of a position. If you want the amount, wrap XMATCH in INDEX.
4. Sharing the file with older Excel. Anyone on Excel 2019 or earlier sees #NAME?. Use MATCH with 0 if the file must work everywhere.

Practise it#

Positions click once you've counted them yourself on a real list. Practise XLOOKUP and XMATCH match modes in a live spreadsheet: you type the formula and it checks the result instantly. Then practise INDEX and MATCH to turn positions into full lookups.