XLOOKUP 通配符与部分匹配
默认的 XLOOKUP 把星号当成普通字符。测试表里对 SKU-10* 查找，返回的是我写的 missing。把第五参数 match_mode 设为 2 之后才按通配符匹配，并返回第一处命中 SKU-101 的价格 12.5，而不是后面同样符合的 SKU-103 或 SKU-10X。Google 和微软的 XLOOKUP 说明都把 2 定为通配符模式。
本文目录
快速解答
默认的 XLOOKUP 把星号当成普通字符。测试表里对 SKU-10* 查找，返回的是我写的 missing。把第五参数 match_mode 设为 2 之后才按通配符匹配，并返回第一处命中 SKU-101 的价格 12.5，而不是后面同样符合的 SKU-103 或 SKU-10X。Google 和微软的 XLOOKUP 说明都把 2 定为通配符模式。
=XLOOKUP("SKU-10*",A2:A6,B2:B6,"missing")检查要点
问号匹配单个字符。SKU-10? 同样先打中 SKU-101。SKU-110 不符合 SKU-10?，但符合 SKU-1*0，该查找返回 4.1。没有命中时，会回到我提供的 missing。若省略这个参数，未命中通常是 N/A 错误，不要把它和通配符失败混为一谈。
边界与兼容性
列表里有一个字面值键 A*B-9，精确模式下能找到价格 99。打开通配符模式后，A*B-9 既是键本身，也符合“A、然后任意内容、然后 B-9”的模式，这里仍然返回 99。若要在模式 2 里查找带星号的键，微软说明用波浪号转义；本表中 A~*B-9 也返回 99。小写 sku-10* 同样打中 SKU-101，通配符并不会变成区分大小写。
完整英文示例、公式与作者备注
Excel How-toI 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) → missing12.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") → 99Turn 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) → 99That 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.5Lowercase 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.