今回は、ExcelのXMATCH関数を使って、表やリストの中から項目の位置を検索する方法と、実務で使える活用のコツを紹介します。
XMATCH関数は、Microsoft 365のExcel、Excel 2021以降、Web版のExcelなどで利用できます。Excel 2019以前の買い切り版では使えないため、ファイルを共有する相手のExcelのバージョンにも注意してください。本記事の操作説明は、Windows版のMicrosoft 365を前提にしています。
XMATCH関数とは
XMATCH関数は、指定した値が範囲や配列の中で何番目にあるかを返す関数です。値そのものではなく、「上から何番目」「左から何番目」という相対位置を求めるのが特徴です。
従来からあるMATCH関数と目的は同じですが、XMATCH関数は既定の動作が完全一致になっているほか、末尾から検索する機能やワイルドカード検索の指定が分かりやすくなっています。
XMATCH関数の構文
=XMATCH(検索値, 検索範囲, [一致モード], [検索モード])
- 検索値：探したい値（必須）
- 検索範囲：探す対象の範囲または配列（必須）
- 一致モード：一致の判定方法（省略可）
- 検索モード：検索する方向や方式（省略可）
一致モードの種類
- 0：完全一致（省略時の既定）
- -1：完全一致、なければ次に小さい値
- 1：完全一致、なければ次に大きい値
- 2：ワイルドカード（*、?、~）を使った一致
検索モードの種類
- 1：先頭から末尾へ検索（省略時の既定）
- -1：末尾から先頭へ検索
- 2：昇順に並べ替えた範囲を対象にしたバイナリ検索
- -2：降順に並べ替えた範囲を対象にしたバイナリ検索
検索モードの2と-2は、範囲が正しく並べ替えられていないと正しい結果が得られません。通常の用途では、省略するか1または-1を使うのが無難です。
XMATCH関数の基本的な使い方
たとえば、A2:A8に商品名の一覧があり、D2に入力した商品名がリストの何番目にあるかを調べたい場合は、次のように入力します。
=XMATCH(D2, A2:A8)
一致モードを省略すると完全一致で検索されるため、MATCH関数のように最後に0を付け忘れて想定外の結果になる、という心配がありません。見つからない場合は#N/Aエラーが返ります。
見つからないときの表示を整える
#N/Aエラーがそのまま表示されると、表の見た目が乱れたり、ほかの数式に影響したりします。IFNA関数やIFERROR関数と組み合わせると、見つからない場合の表示を指定できます。
=IFNA(XMATCH(D2, A2:A8), “該当なし”)
入力ミスに気付きやすくなるので、入力用のシートでは特に役立ちます。
XMATCH関数の活用例
INDEX関数と組み合わせて値を取り出す
XMATCH関数で求めた位置をINDEX関数に渡すと、対応する値を取り出せます。たとえば、A列の商品名に対応するC列の価格を取り出すには、次のようにします。
=INDEX(C2:C8, XMATCH(D2, A2:A8))
検索する列と取り出す列を別々に指定できるため、検索列が表の左端にない場合でも使えます。
行と列の両方から交差する値を探す
縦に担当者名、横に月が並んだ集計表から、特定の担当者と月が交わるセルの値を取り出したいときは、XMATCH関数を2回使います。
=INDEX(C6:E12, XMATCH(B3, B6:B12), XMATCH(C3, C5:E5))
1つ目のXMATCHで行の位置、2つ目のXMATCHで列の位置を求め、INDEX関数が交差するセルの値を返します。B3やC3の値を切り替えるだけで参照先が変わるので、簡易的な検索シートを作るときに便利です。
最後に登場した位置を探す
同じ値が複数回出てくるリストで、最後に登場した位置を知りたい場合は、検索モードに-1を指定します。
=XMATCH(D2, A2:A20, 0, -1)
たとえば、日付順に記録した履歴の中から、特定の顧客や品目の最新の行を探すときに使えます。INDEX関数と組み合わせれば、最新の日付や内容を取り出せます。
ワイルドカードで部分一致検索をする
一致モードに2を指定すると、ワイルドカードを使った検索ができます。
=XMATCH(“東京*”, A2:A20, 2)
この例では、「東京」で始まる最初の項目の位置を返します。「*」は任意の文字列、「?」は任意の1文字を表します。「*」や「?」そのものを検索したい場合は、直前に「~」を付けます。
段階表から該当する区分を探す
一致モードに-1や1を指定すると、完全に一致する値がない場合に、近い値の位置を返せます。たとえば、点数の区切りを昇順に並べた表で、ある点数がどの区分に入るかを調べる場合は、-1（次に小さい値）を使うと、その点数以下で最も大きい区切りの位置が求められます。
区分表の並び順と一致モードの組み合わせを確認しながら設定すると、意図どおりの結果になりやすくなります。
XMATCH関数を使うときのコツと注意点
範囲の大きさをそろえる
INDEX関数と組み合わせる場合は、検索範囲と取り出す範囲の行数（または列数）をそろえることが大切です。開始行がずれていると、1行ずれた値が返ってくるため、結果が正しいかどうかを数件確認しておくと安心です。
テーブル機能と組み合わせる
リストを［テーブルとして書式設定］でテーブルにしておくと、行を追加したときに参照範囲が自動で広がります。数式も「テーブル名[列名]」の形で書けるため、何を検索しているのかが読み取りやすくなります。
余分なスペースや表記ゆれに注意する
見た目は同じ文字でも、末尾にスペースが入っていたり、全角と半角が混在していたりすると、完全一致では見つかりません。#N/Aエラーが出たときは、TRIM関数で余分なスペースを取り除くなど、データ側の表記を確認してみてください。
XLOOKUP関数との使い分け
値を1つ取り出すだけなら、XLOOKUP関数のほうが数式を短く書けることがあります。一方、XMATCH関数は位置そのものが必要な場面や、行と列の両方を動的に指定したい場面に向いています。目的に応じて使い分けると、数式が読みやすくなります。
まとめ
ExcelのXMATCH関数は、範囲の中で値が何番目にあるかを返す、位置検索のための関数です。最後に要点を整理します。
- 構文は「=XMATCH(検索値, 検索範囲, [一致モード], [検索モード])」で、既定は完全一致
- INDEX関数と組み合わせると、位置に対応する値を取り出せる
- XMATCH関数を2つ使うと、行と列が交差する値を参照できる
- 検索モードに-1を指定すると、最後に登場した位置を探せる
- 一致モードに2を指定すると、ワイルドカードで部分一致検索ができる
- Microsoft 365、Excel 2021以降などで利用でき、古いバージョンでは使えない
XMATCH関数は単体でも便利ですが、INDEX関数やIFNA関数と組み合わせることで、検索シートや集計表の作成に幅広く活用できます。まずは身近なリストで位置を求めるところから試してみてください。