XMATCH 與 MATCH 的區別
用 MATCH 查詢 SKU-110 時，我漏了第三引數，結果得到位置 5，那裡是 sku-103，真正的 SKU-110 在位置 3。列表是未排序的。Google 和微軟都把省略或取 1 的匹配型別當作近似匹配，近似匹配要求已排序。補上精確匹配的 0 之後，同樣的查詢返回 3。
本文目錄
快速解答
用 MATCH 查詢 SKU-110 時，我漏了第三引數，結果得到位置 5，那裡是 sku-103，真正的 SKU-110 在位置 3。列表是未排序的。Google 和微軟都把省略或取 1 的匹配型別當作近似匹配，近似匹配要求已排序。補上精確匹配的 0 之後，同樣的查詢返回 3。
=MATCH("SKU-110",A2:A6) → 5
=MATCH("SKU-110",A2:A6,0) → 3檢查重點
本 Google 表格裡有 XMATCH，並且預設就是精確匹配。XMATCH("SKU-103") 和 MATCH(...,0) 都返回 2，即第一處 SKU-103。未命中是 N/A，不是別的錯誤碼。XMATCH 沒有 if_not_found 引數。我把 "missing" 寫在第三引數時得到 VALUE 錯誤，因為第三引數是 match_mode，文本 missing 不是合法模式。要用 IFNA 或 IFERROR 包一層。
邊界與相容性
XMATCH 第四引數 search_mode 為 -1 時從後往前找。對 SKU-103 得到 5，是最下面的小寫 sku-103，不是位置 4 的第二個 SKU-103。小寫 sku-103 從前往後查得到 2。大小寫不敏感。MATCH 的精確匹配（型別 0）在本表裡已經把星號和問號當萬用字元：SKU-10* 和 SKU-10? 都返回 1（SKU-101）。XMATCH 要用模式 2 才做同樣的萬用字元搜尋。
完整英文範例、公式與作者備註
Excel ComparisonI 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) → 3The 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) → 2Both 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) → 2Last-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) → 11 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.