XMATCH vs MATCH in Excel and Google Sheets

I looked up SKU-110 with MATCH and got position 5. Position 5 was sku-103. The cell I wanted was position 3.

The list in A2:A6 was SKU-101, SKU-103, SKU-110, a second SKU-103, and sku-103. I had written this:

=MATCH("SKU-110",A2:A6)      → 5
=MATCH("SKU-110",A2:A6,0)    → 3

The third argument was missing. Google’s MATCH help and Microsoft’s MATCH function page both treat an omitted or 1 match type as approximate, and approximate MATCH expects the range to be sorted. This range is not. The 5 was not a useful “close” match; it was the wrong row. Once I passed 0 for an exact match, the same lookup returned 3.

That is the main reason I reach for XMATCH at all. In this Sheets file it exists and it defaults to exact:

=XMATCH("SKU-103",A2:A6)     → 2
=MATCH("SKU-103",A2:A6,0)    → 2

Both landed on the first SKU-103. A miss is still #N/A — XMATCH("ZZ",A2:A6) returned #N/A, and MATCH("ZZ",A2:A6,0) did too. That is the usual lookup error path, not a special XMATCH miss code.

XMATCH is not XLOOKUP with a shorter name

I tried to give XMATCH a friendly miss label the way I do with XLOOKUP, and I got #VALUE!:

=XMATCH("ZZ",A2:A6,"missing")    → #VALUE!

Microsoft’s XMATCH syntax is XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]). There is no if_not_found argument. The third argument is match mode, and the text missing is not a mode. Wrap the miss with IFNA or IFERROR instead. I did not test every illegal mode value; I am only reporting this one.

The fourth argument is the one I actually wanted. search_mode -1 walks the list last-to-first:

=XMATCH("SKU-103",A2:A6,0,-1)    → 5
=XMATCH("sku-103",A2:A6)         → 2

Last-to-first of SKU-103 returned 5, which is the lowercase sku-103 at the bottom, not the second exact-case SKU-103 at position 4. A first-to-last lookup of lowercase sku-103 returned 2, the first SKU-103. Case did not matter, which is the same behavior I already documented in Case-Sensitive Lookup. If you need the last exactly-cased row, XMATCH will not do that by itself.

MATCH has no last-to-first switch. For a first-match position, MATCH(...,0) and default XMATCH agreed in every exact case I ran.

Wildcards sit in different places

MATCH with type 0 already treated * and ? as wildcards in this file:

=MATCH("SKU-10*",A2:A6,0)     → 1
=MATCH("SKU-10?",A2:A6,0)     → 1
=MATCH("ZZ*",A2:A6,0)         → #N/A
=XMATCH("SKU-10*",A2:A6,2)    → 1

1 is SKU-101, the first row that fits the pattern. XMATCH needed match mode 2 for the same wildcard search; that is the mode Microsoft documents for *, ?, and ~. I would not pass a wildcard string to default XMATCH and assume it is a pattern — I did not run that exact default-mode trial with a star, so I am not going to write down a result I do not have. For the broader wildcard lookup problem, use FIND vs SEARCH when you are inspecting one string, or the XLOOKUP write-up in XLOOKUP vs INDEX/MATCH vs VLOOKUP when you want a return value rather than a position.

I did not test binary search modes 2 / -2, or approximate XMATCH modes -1 / 1. MATCH without 0 is already enough of a warning: if the list is not sorted for an approximate match, you can get a confident, wrong index. Pass 0, or use XMATCH and leave the mode at its exact default.