=XMATCH(E2,A2:A6) returns the position of E2 in A2:A6: 1 for the first cell, 2 for the second. Unlike MATCH, it looks for an exact match unless told otherwise. XMATCH needs Excel 2021 or Microsoft 365; in older versions use MATCH(E2,A2:A6,0).
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Look for | Position | |
| 2 | Apple | Fruit | $1.20 | Carrot | 3 | |
| 3 | Pear | Fruit | $1.50 | |||
| 4 | Carrot | Vegetable | $0.80 | |||
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
Carrot is the third item, so F2 shows 3. Type Kiwi and it returns #N/A. Like every Excel lookup, it ignores case.
XMATCH syntax
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
| Argument | Values |
|---|---|
match_mode | 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard match |
search_mode | 1 from the first item (default), -1 from the last item, 2 binary search ascending, -2 binary search descending |
The binary modes are faster on very large sorted lists and return wrong positions on unsorted ones. The other modes work on data in any order.
Next smaller or next larger, without sorting
match_mode 1 finds the smallest value that is greater than or equal to the lookup value. That answers "the smallest box that fits" even when the sizes are listed in no order. With MATCH you would need the list sorted from largest to smallest.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Box | Holds (liters) | Item (liters) | 18 | |
| 2 | S | 5 | Position | 4 | |
| 3 | XL | 50 | Box | L | |
| 4 | M | 12 | |||
| 5 | L | 25 |
The sizes that hold 18 liters are 25 and 50, and the smaller is 25, the fourth item, box L. Change E1 to 12 for an exact match (M) or to 40 for XL. match_mode -1 is the opposite: the largest value less than or equal to the lookup value, the rule for tax brackets and commission bands. XLOOKUP has the same two modes and returns the value directly.
Search from the last item
search_mode -1 starts at the bottom, so XMATCH returns the position of the last match. Positions still count from the top of the range.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Customer | Amount | Customer | Ben | |
| 2 | 2026-03-02 | Ben | 120 | Last position | 5 | |
| 3 | 2026-03-05 | Ana | 80 | Last date | 2026-03-20 | |
| 4 | 2026-03-09 | Ben | 45 | First position | 1 | |
| 5 | 2026-03-12 | Cara | 200 | |||
| 6 | 2026-03-20 | Ben | 60 | |||
| 7 | 2026-03-24 | Ana | 95 |
Ben appears in positions 1, 3 and 5. The forward search returns 1 and the reverse search returns 5, his order on 2026-03-20. Before XMATCH, the last position took an array trick: =MATCH(2,1/(B2:B7=F1)).
Wildcards need match_mode 2
With match_mode 2, * stands for any characters and ? for exactly one. In the other modes they are ordinary characters, while MATCH treats them as wildcards whenever match_type is 0.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Pattern | Position | |
| 2 | Green tea | *coffee | 2 | |
| 3 | Iced coffee | ?lack* | 4 | |
| 4 | Coffee beans | |||
| 5 | Black tea | |||
| 6 | Orange juice |
*coffee (anything that ends in "coffee") matches Iced coffee at position 2, and ?lack* matches Black tea at position 4. Coffee beans does not match *coffee, because the pattern has no * at the end. Without match_mode 2 the same pattern is searched for as literal text, so in Excel this returns #N/A:
=XMATCH("*coffee", A2:A6) #N/A: no cell is literally "*coffee"
=XMATCH("*coffee", A2:A6, 2) 2
XMATCH vs MATCH
| MATCH | XMATCH | |
|---|---|---|
| Default when the mode is left out | Approximate (1), data must be sorted | Exact |
| Next smaller value | 1, data sorted ascending | -1, any order |
| Next larger value | -1, data sorted descending | 1, any order |
| Wildcards | Always on with 0 | Only with match_mode 2 |
| Last match | No | search_mode -1 |
| Versions | All | Excel 2021 and Microsoft 365 |
The signs are reversed: MATCH's -1 finds the next larger value and XMATCH's -1 the next smaller. When you convert MATCH(E2,A2:A6,0) to XMATCH, drop the 0; when you convert an approximate MATCH, flip the sign and the sort order no longer matters. The MATCH page covers the older function.
Practice: the last order's position
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Customer | Amount | Customer | Ben | |
| 2 | 2026-03-02 | Ben | 120 | Last position | ||
| 3 | 2026-03-05 | Ana | 80 | |||
| 4 | 2026-03-09 | Ben | 45 | |||
| 5 | 2026-03-12 | Cara | 200 | |||
| 6 | 2026-03-20 | Ben | 60 | |||
| 7 | 2026-03-24 | Ana | 95 |
Your turn: In F2, return the position of the last order of the customer in F1.
Frequently Asked Questions
What is the difference between MATCH and XMATCH?
XMATCH defaults to an exact match (MATCH defaults to approximate), finds the next smaller or larger value without needing sorted data, can search from the last item up, and only treats * and ? as wildcards when you ask with match_mode 2. It needs Excel 2021 or Microsoft 365.
How do I find the last occurrence with XMATCH?
Set search_mode, the fourth argument, to -1: =XMATCH("Ben",B2:B7,0,-1) returns the position of the last Ben in B2:B7. Positions still count from the top of the range.
What does XMATCH return if nothing matches?
#N/A, like MATCH. Wrap it in IFNA to show something else: =IFNA(XMATCH(E2,A2:A6),"Not in list").
Can XMATCH replace MATCH inside INDEX?
Yes. =INDEX(C2:C6,XMATCH(E2,A2:A6)) works like INDEX with MATCH(E2,A2:A6,0), with no 0 to forget. Use MATCH instead when the file must open in Excel 2019 or older.
