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.