Lookup function

Excel XMATCH function

Q: How do I find the last occurrence of a value in Excel?

Use =XMATCH("Harbor Cafe",B2:B7,0,-1). It searches from the bottom and returns the position of the last match; wrap it in INDEX to return a value from that row.

XMATCH returns the position of a value in a row or column: 1 for the first item, 2 for the second, and so on. It is the modern MATCH. It finds an exact match by default, can search from the bottom up, and does not need sorted data for a next smaller or next larger match.

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

Arguments

lookup_value: The value to find. A list such as {"Pine & Co","Ashby Deli"} returns one position for each item.
lookup_array: The single row or column to search.
match_mode: Optional. 0 exact match (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard match with * and ?.
search_mode: Optional. 1 searches first to last (default), -1 last to first. 2 and -2 run a binary search on data sorted ascending or descending.

XMATCH examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

	A	B	C	E	F
1	Order	Customer	Total	Order from	Discount
2	SO-1041	Harbor Cafe	180	0	0
3	SO-1042	Pine & Co	420	100	0.05
4	SO-1043	Harbor Cafe	95	250	0.1
5	SO-1044	Northgate Gym	610	500	0.15
6	SO-1045	Ashby Deli	260
7	SO-1046	Harbor Cafe	135

Download this sheet as an Excel practice file (.xlsx)

=XMATCH("Pine & Co",B2:B7)
Result: 2
Pine & Co is the second customer in the list, so this returns 2.
=XMATCH("Harbor Cafe",B2:B7,0,-1)
Result: 6
Search mode -1 searches from the bottom, so it finds the last Harbor Cafe order. The position still counts from the top: 6.
=INDEX(F2:F5,XMATCH(C3,E2:E5,-1))
Result: 0.1
Match mode -1 takes the next smaller value. Order SO-1042 is 420, which falls in the 250 tier, so INDEX returns that discount: 0.1.
=XMATCH("North*",B2:B7,2)
Result: 4
Match mode 2 turns on wildcards. North* matches Northgate Gym in position 4.

How to use XMATCH

Click the cell for the result.
Type =XMATCH( then the value to find, or a cell that holds it.
Add a comma and select the single row or column to search.
Optionally add a match mode (0, -1, 1 or 2) and a search mode (1 or -1).
Press Enter. Wrap it in INDEX to return a value from the same row of another column.

Common XMATCH mistakes

Expecting MATCH’s default

MATCH defaults to an approximate match on sorted data. XMATCH defaults to an exact match, so a missing value gives #N/A instead of a wrong row.

Wildcards that do nothing

In match mode 0 an asterisk is just a character. Set match_mode to 2 to use * and ? as wildcards: =XMATCH("North*",B2:B7,2).

Searching a whole table

lookup_array must be one row or one column, such as B2:B7. To find a row and a column, use one XMATCH for each inside INDEX.

Using it in older Excel

XMATCH needs Microsoft 365, Excel 2021 or later, or Excel for the web. In Excel 2019 and earlier use MATCH(value,range,0).

XMATCH FAQ

What is the difference between MATCH and XMATCH?

Both return a position. XMATCH defaults to an exact match, can search last to first, and finds the next smaller or larger value without sorted data. MATCH works in every version of Excel.

How do I find the last occurrence of a value in Excel?

Use =XMATCH("Harbor Cafe",B2:B7,0,-1). It searches from the bottom and returns the position of the last match; wrap it in INDEX to return a value from that row.

Why does XMATCH return #N/A?

The value is not in the list, often because of an extra space or a number stored as text. Check with TRIM or VALUE, or use match mode -1 or 1 if a nearby value is good enough.

Is XMATCH case-sensitive?

No, "harbor cafe" matches "Harbor Cafe". For a case-sensitive position use =XMATCH(TRUE,EXACT(B2:B7,"Harbor Cafe")).

Related functions

Updated November 3, 2026