スプレッドシートでMATCH関数を使っていて、「末尾から検索できたらいいのに」と感じたことはありませんか？
追記型のリストで同じ名前が並んでいると、最新のデータだけ拾いたいのに先頭側がヒットしてしまいますよね。
そこで活躍するのがXMATCH関数です。逆方向検索やワイルドカード専用モードなど、MATCHにはない機能が揃っています。
この記事では、XMATCH関数の基本から実務活用まで解説します。
スプレッドシートのXMATCH関数とは？
XMATCH関数（読み方: エックスマッチ関数）は、検索範囲から指定した値を探し、その位置（何番目か）を数値で返す関数です。「X」はExtended（拡張）の意味で、MATCH関数を強化した上位互換にあたります。
XMATCH関数にできることをまとめると、次のとおりです。
- 検索値が範囲の何番目にあるかを数値で返す
- 完全一致・近似一致・ワイルドカード・正規表現の4種類の一致モードに対応する
- 先頭から／末尾からの検索方向を選べる（逆方向検索）
- INDEX関数と組み合わせて柔軟な表引きを実現する
NOTE
XMATCH関数が返すのは値そのものではなく「位置番号」です。値を直接取得したい場合は、INDEX関数やXLOOKUP関数と組み合わせて使います。
XMATCH関数の書き方（構文と引数）
基本構文
XMATCH関数の構文はこちらです。
=XMATCH(検索値, 検索範囲, [一致モード], [検索モード])引数は4つあります。一致モードと検索モードは省略できます。
引数の説明
| 引数 | 必須/省略可 | 意味 | 指定例 |
|---|---|---|---|
| 検索値 | 必須 | 探したい値 | “営業部”、E001 |
| 検索範囲 | 必須 | 検索対象の1行または1列の範囲 | A2:A6 |
| 一致モード | 省略可 | 一致の判定方法（0, -1, 1, 2） | 0 |
| 検索モード | 省略可 | 検索する方向（1, -1, 2, -2） | 1 |
省略した場合は「先頭から完全一致で検索」という最もシンプルな動作になります。MATCH関数とは異なり、デフォルトが完全一致です。引数の指定ミスが起きにくいのがメリットです。
一致モード4種の違い
一致モードは検索値と範囲内の値をどう照合するかを決めます。
| 一致モード | 動作 | データの並び順 |
|---|---|---|
| 0（デフォルト） | 完全一致。値がぴったり一致する位置を返す | 不問 |
| -1 | 検索値以下の最大値の位置を返す | 不問 |
| 1 | 検索値以上の最小値の位置を返す | 不問 |
| 2 | ワイルドカード一致（*、?、~） | 不問 |
MATCH関数の近似一致ではデータの並べ替えが必須でしたが、XMATCHなら並べ替え不要で近似一致ができます。
検索モード4種の違い
検索モードは範囲をどの方向から探すかを決めます。
| 検索モード | 動作 | 条件 |
|---|---|---|
| 1（デフォルト） | 先頭から検索 | 不問 |
| -1 | 末尾から逆方向検索 | 不問 |
| 2 | 昇順ソート済みデータでバイナリサーチ | 昇順必須 |
| -2 | 降順ソート済みデータでバイナリサーチ | 降順必須 |
通常は先頭から検索（1）で問題ありません。末尾から検索（-1）はXMATCH関数の目玉機能で、MATCH関数にはない機能です。
XMATCH関数の基本的な使い方
完全一致で位置を取得する（一致モード 0）
次の社員管理表を例に見てみましょう。A1:D6にデータが入っているとします。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 社員番号 | 氏名 | 部署 | 役職 |
| 2 | E001 | 田中太郎 | 営業部 | 課長 |
| 3 | E002 | 鈴木花子 | 経理部 | 主任 |
| 4 | E003 | 佐藤一郎 | 総務部 | 部長 |
| 5 | E004 | 山田美咲 | 営業部 | 主任 |
| 6 | E005 | 高橋健一 | 開発部 | 課長 |
部署列（C2:C6）の中で「総務部」が何番目にあるか調べるにはこう書きます。
=XMATCH("総務部", C2:C6)結果は「3」です。C2:C6の中で「総務部」はC4にあり、先頭から数えて3番目の位置です。
一致モードを省略しても完全一致で検索してくれます。MATCH関数では =MATCH("総務部", C2:C6, 0) と第3引数に0を書く必要がありました。XMATCHなら省略できる分シンプルです。
ワイルドカードで部分一致検索する（一致モード 2）
部分一致で検索したいときは、一致モードに「2」を指定します。*（任意の文字列）と ?（任意の1文字）が使えます。
| ワイルドカード | 意味 | 例 |
|---|---|---|
| *（アスタリスク） | 任意の文字列（0文字以上） | “*太郎” → 太郎で終わる値 |
| ?（クエスチョン） | 任意の1文字 | “E00?” → E001〜E009 |
| ~（チルダ） | *や?自体をエスケープ | “~” → を検索 |
たとえば、氏名列から「鈴木」で始まる人の位置を調べるにはこう書きます。
=XMATCH("鈴木*", B2:B6, 2)結果は「2」です。B2:B6の中で「鈴木花子」が2番目にあるため、その位置が返ります。
NOTE
MATCH関数では完全一致モード（0）でワイルドカードが使えましたが、XMATCHでは専用の一致モード（2）に分離されています。完全一致モード（0）ではワイルドカードは文字として扱われるので注意してください。
近似一致で最も近い値を取得する（一致モード -1 / 1）
数値データに対して「ぴったり一致する値がなければ、近い値で代用する」モードです。
- -1（以下の最大値）: 検索値以下で最も大きい値の位置を返す
- 1（以上の最小値）: 検索値以上で最も小さい値の位置を返す
たとえば、点数テーブルで85点のランクを調べたいとします。テーブルに85がなくても、以下のように書けます。80の位置を返してくれます。
=XMATCH(85, {60,70,80,90}, -1)この式は「3」を返します。85以下の最大値は80で、3番目だからです。
MATCH関数の近似一致ではデータの昇順・降順ソートが必須でした。XMATCHなら並べ替え不要で近似一致ができます。
実践的な使い方・応用例
逆方向検索で最新データの位置を取得する
XMATCH関数の目玉機能が、逆方向検索（検索モード -1）です。末尾から先頭に向かって検索し、最初に見つかった位置を返します。
これが活躍するのは「追記型の台帳」です。日報や作業ログのように下へ追記していくシートを想像してください。同じ担当者名が複数行にあるとき、最新（最後）のエントリの位置を取得できます。
=XMATCH("田中太郎", B2:B100, 0, -1)MATCH関数では逆順検索ができなかったため、回りくどい数式が必要でした。XMATCHなら検索モードに-1を指定するだけで解決です。
TIP
逆方向検索でも返る値は「先頭からの位置番号」です。たとえばB2:B100の80番目に該当データがあれば、検索方向に関係なく「80」が返ります。
INDEX+XMATCHで左側の列を検索する
XMATCH関数が最も活躍するのは、INDEX関数との組み合わせです。
VLOOKUP関数は検索列が範囲の左端にある必要があります。しかしINDEX+XMATCHなら、検索列と戻り値の列を自由に指定できます。
たとえば「営業部の課長の社員番号を知りたい」とします。部署（C列）で検索して、社員番号（A列）を返す数式です。
=INDEX(A2:A6, XMATCH("営業部", C2:C6))処理の流れを順に見ていきます。
- XMATCH(“営業部”, C2:C6) → C2:C6の中で「営業部」は1番目 → 「1」を返す
- INDEX(A2:A6, 1) → A2:A6の1番目 → 「E001」を返す
検索範囲（C列）と戻り値の範囲（A列）を別々に指定できるため、左方向の検索も問題ありません。
INDEX+XMATCHでクロス検索する（行と列を同時に指定）
ちょっとむずかしく見えますが、やっていることはシンプルです。行と列の両方をXMATCHで動的に指定することで、クロス検索ができます。
「社員番号E003の役職を取得したい」とします。行の位置も列の位置もXMATCHで自動判定する数式です。
=INDEX(A2:D6, XMATCH("E003", A2:A6), XMATCH("役職", A1:D1))処理の流れを見てみましょう。
- XMATCH(“E003”, A2:A6) → 「3」（3行目）
- XMATCH(“役職”, A1:D1) → 「4」（4列目）
- INDEX(A2:D6, 3, 4) → 「部長」を返す
列の見出し名で検索するため、途中に列が追加されても数式が壊れません。大きな表を扱うときに重宝するパターンです。
IFERRORと組み合わせてエラーを防ぐ
XMATCH関数は検索値が見つからないと#N/Aエラーを返します。IFERROR関数で囲めば、エラー表示を防げます。
=IFERROR(XMATCH("マーケ部", C2:C6), "該当なし")「マーケ部」はデータに存在しないため、「該当なし」が表示されます。
INDEX+XMATCHと組み合わせる場合は、全体をIFERRORで囲みます。
=IFERROR(INDEX(B2:B6, XMATCH("E010", A2:A6)), "見つかりません")共有シートなど他の人が使う表では、IFERRORを付けておくのがおすすめです。
バイナリサーチで大量データを高速検索する
データが大量（数万行以上）で、すでに並べ替え済みの場合は、バイナリサーチが使えます。
- 検索モード 2: 昇順ソート済みデータに対して二分探索
- 検索モード -2: 降順ソート済みデータに対して二分探索
=XMATCH("E003", A2:A10000, 0, 2)通常の検索より高速ですが、データが正しくソートされていないと誤った結果を返します。少量データなら通常検索で十分です。無理に使う必要はありません。
よくあるエラーと対処法
XMATCH関数で発生しやすいエラーとその原因をまとめます。
| エラー | 主な原因 | 対処法 |
|---|---|---|
| #N/A | 検索値が範囲内に存在しない | データの有無を確認する。IFERRORで囲む |
| #N/A | データ型の不一致（数値と文字列の混在） | 検索値と範囲のデータ型を揃える |
| #N/A | ワイルドカードを一致モード0で使っている | 一致モードを2に変更する |
| #VALUE! | 検索範囲に2次元の範囲を指定した（例: A1:C5） | 1行または1列の範囲に変更する |
| #VALUE! | 一致モードや検索モードに無効な値を指定した | 有効な値（0, -1, 1, 2）を確認する |
特にありがちなのが、データ型の不一致です。セルの見た目は同じ数字でも、片方が数値・もう片方が文字列だと一致と判定されません。
確認するにはセルを選択して、数式バーの表示をチェックしてください。文字列として入力された数字は左寄せで表示されます。VALUE関数（文字列を数値に変換する関数）で型を揃えると解決します。
XMATCH関数とMATCH関数の違い・使い分け
XMATCHはMATCH関数の上位互換です。主な違いを比較表で整理します。
| 比較項目 | MATCH | XMATCH |
|---|---|---|
| デフォルトの一致モード | 近似一致（1） | 完全一致（0） |
| 近似一致の並べ替え | 昇順/降順が必須 | 不要 |
| 逆方向検索（末尾から検索） | 不可 | 可能（検索モード-1） |
| ワイルドカード | 完全一致モード（0）で使用可 | 専用モード（2）で使用 |
| バイナリサーチ | 不可 | 可能（検索モード2/-2） |
| 正規表現 | 不可 | 一致モード3で対応（スプレッドシート独自） |
どちらを選ぶべき？
判断基準はシンプルです。
- 新しく数式を作る場合 → XMATCHがおすすめ。デフォルト完全一致で安全
- 既存のシートを修正する場合 → 既存のMATCHに合わせるのが無難
- 末尾側の一致を取得したい場合 → XMATCHの逆方向検索を使う
- 大量データを高速検索したい場合 → XMATCHのバイナリサーチを使う
なお、値そのものを取得したい場合はXLOOKUP関数のほうがシンプルに書けます。XMATCH関数は位置番号が必要なケースや、INDEX関数との組み合わせでクロス検索をしたいケースで特に威力を発揮します。
まとめ
XMATCH関数のポイントを振り返ります。
- 構文は =XMATCH(検索値, 検索範囲, 一致モード, 検索モード) の4引数。第3・第4引数は省略可
- デフォルトが完全一致なので、引数の指定ミスが起きにくい
- 逆方向検索（検索モード-1）で最新データの位置を簡単に取得できる
- 近似一致で並べ替え不要になり、手間が減った
- ワイルドカード一致は専用モード（一致モード2）で使用する
- INDEX関数と組み合わせれば、縦横同時のクロス検索が可能
まずは =XMATCH("検索値", 範囲) の完全一致パターンから試してみてください。MATCH関数を使っている方は、XMATCHに切り替えるだけで検索がグッと楽になりますよ。
