XLOOKUP 萬用字元與部分匹配

I typed this and expected the first SKU that started with SKU-10:

=XLOOKUP("SKU-10*",A2:A6,B2:B6,"missing")

The cell said missing. The list in A2:A6 was SKU-101, SKU-103, SKU-110, A*B-9, and SKU-10X. Nothing in that list is the literal text SKU-10*, and by default XLOOKUP is not doing a wildcard search. The asterisk is just another character.

The fifth argument is match_mode. Google’s XLOOKUP help calls 2 a wildcard match. Microsoft’s XLOOKUP function page says the same, and it spells out that *, ?, and ~ have special meaning in that mode. Once I passed 2, the formula moved:

=XLOOKUP("SKU-10*",A2:A6,B2:B6,"missing",2)    → 12.5
=XLOOKUP("SKU-10?",A2:A6,B2:B6,"missing",2)    → 12.5
=XLOOKUP("SKU-1*0",A2:A6,B2:B6,"missing",2)    → 4.1
=XLOOKUP("ZZ*",A2:A6,B2:B6,"missing",2)        → missing

12.5 is the price on SKU-101, the first row that matches SKU-10*. SKU-103 and SKU-10X also fit that pattern. XLOOKUP does not return all of them, and it does not pick the “best” one. It returns the first hit, which is the same first-match rule I already see in the exact-match XLOOKUP vs INDEX/MATCH vs VLOOKUP write-up.

? is one character. SKU-10? matches SKU-101, SKU-103, and SKU-10X. It does not match SKU-110 (SKU-11 plus a 0). SKU-1*0 is the one that landed on SKU-110 and returned 4.1.

A miss with an if_not_found string stays that string. Drop the "missing" argument and a real miss is #N/A, which is the usual lookup error path.

When the SKU itself contains an asterisk

One of the keys in the test list is the literal text A*B-9, priced at 99. Exact mode finds it, because that is the actual cell value:

=XLOOKUP("A*B-9",A2:A6,B2:B6,"missing")        → 99

Turn wildcard mode on and the same lookup still returns 99. That is not proof of an exact match. In mode 2, A*B-9 means “A, then anything, then B-9,” and A*B-9 happens to fit its own pattern. If another row such as AXB-9 appeared above it, I would expect that earlier row to win. I did not add that row, so I am not going to write down a result I did not see.

To look up the asterisk as a character in mode 2, Microsoft’s page says ~ is the escape. In this file:

=XLOOKUP("A~*B-9",A2:A6,B2:B6,"missing",2)     → 99

That found the literal A*B-9 key. Use the tilde when the stored SKU might contain * or ? and you still need wildcard mode for other searches.

Case is not a wildcard problem

=XLOOKUP("sku-10*",A2:A6,B2:B6,"missing",2)    → 12.5

Lowercase sku-10* still hit SKU-101. Wildcard mode did not become case-sensitive. If the list can hold both SKU-103 and sku-103 with different prices, this is the wrong function; I walked through that mess in Case-Sensitive Lookup.

FIND and SEARCH are a different tool if you are inspecting a single string rather than walking a column. SEARCH already treats * and ? as wildcards; FIND does not. That comparison is in FIND vs SEARCH.

I did not test VLOOKUP with a TRUE / approximate match as a substitute, and I did not test FILTER with a regex. If you need every SKU-10* row, not the first, XLOOKUP is the wrong function anyway.