XMATCH と MATCH の違い
MATCH で SKU-110 を探すとき、第 3 引数を省略したら位置 5 が返った。そこは sku-103 で、本当の SKU-110 は位置 3。リストは未整列。Google も Microsoft も、省略または 1 の一致タイプを近似一致として扱い、近似は並べ替えを前提とする。正確一致の 0 を付けたら、同じ検索は 3 を返した。
目次
要点
MATCH で SKU-110 を探すとき、第 3 引数を省略したら位置 5 が返った。そこは sku-103 で、本当の SKU-110 は位置 3。リストは未整列。Google も Microsoft も、省略または 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 引数はない。第 3 引数に missing と書いたら VALUE エラーになった。第 3 引数は match_mode で、文字列 missing は有効なモードではない。IFNA か IFERROR で包む。
注意点と互換性
XMATCH の第 4 引数 search_mode を -1 にすると末尾から探す。SKU-103 では 5 が返り、一番下の小文字 sku-103 であって、位置 4 の 2 つ目の 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.