Excel（エクセル）のXMATCH関数の使い方｜MATCHが進化した便利な検索関数
XLOOKUPほど注目されていませんが、XMATCH関数も便利な最新の検索関数です。MATCHの後継の関数で、よりスムーズな操作性に進化しています。ただ、定番のINDEX＋MATCHで実行していたクロス抽出がINDEX＋XMATCHで更に簡単になったものの、XLOOKUPで実装可能になってしまったので有り難みが薄れてしまっているのも事実です。
とは言え、MATCH関数の弱点を補正した進化系の関数であるのは確かなので活用していきたいですね。ここではXMATCH関数の使い方を、できるだけ分かりやすく解説していきます。
使用関数：XMATCH
XMATCH（エックスマッチ）関数とは？
XMATCH関数の基本はMATCH関数と同じで、検索した値の相対位置を返します。検索範囲と検索値を指定して、検索値が検索範囲の最初のセルから何番目に位置しているかを数値で返す関数です。多くは、単独で使うよりINDEXやOFFSETなど他の関数の引数にネストして使います。
XMATCH関数が使えるExcelバージョン
XMATCH関数はMicrosoft365に新加入し、単体のExcelでは2021から一般公開されています。Excel2010、2013、2016、2019ではXMATCHは使えませんのでご注意下さい。
| Excelバージョン | 2010 | 2013 | 2016 | 2019 | 2021 | Office365 |
|---|---|---|---|---|---|---|
| 互換性 | ✕ | ✕ | ✕ | ✕ | ◯ | ◯ |
XMATCH関数の引数と書式
「数式」→「関数ライブラリ」→「検索/行列」からXMATCHを選択します。
XMATCH関数の書式の構成はこうなります。MATCH関数より一つ引数が多いですが、最後の２つは省略可能なので多くの場合MATCH関数より数式がコンパクトです。
XMATCH関数の基本的な使い方
XMATCH関数で簡単な表を例にして、引数の指定を一つずつ確認していきましょう。
以下の表から得点に応じたランクを求めます。
第一引数「検索値」
検索のキーとなる値を指定します。この例では「あなたの得点」の入ったセルをクリックで指定します。
第二引数「検索範囲」
検索値を検索するセル範囲、または配列を指定します。この例では表の「得点」のデータ範囲をドラッグで指定します。
第三引数「一致モード」省略可
検索値の検索方法を以下の対応する値で指定します。省略した場合は「完全一致」を指定したとみなされます。
一致モードに指定する値の一覧
| 値 | 一致モード |
|---|---|
| 0 | 完全一致（既定値） |
| -1 | 検索値の小さい方の近似値 |
| 1 | 検索値の大きい方の近似値 |
| 2 | 曖昧検索（検索値にワイルドカードが使用できる） |
XMATCH関数で検索値にワイルドカードを使う
MATCH関数でもワイルドカードは使えますが、XMATCHでは「一致モード」に「2」を指定することで検索値にワイルドカードが使えます。
例えば以下の表で「一致モード」を2、「検索値」を”三*子”と指定すると、「三原 八重子」がヒットして検索範囲での相対位置「3」が返ります。
但し、検索できるのは「検索範囲」で部分一致した最初のデータだけで、曖昧検索によって合致するすべてのデータが対象になるわけではありません。
この例では一致モードに「-1」を入力して「小さい方の近似値」を指定しました。
第四引数「検索モード」省略可
検索値を検索する順序を以下の対応する値で指定します。省略した場合は「1」の「上から下へ検索」を指定したとみなされます。
検索モードに指定する値の一覧
| 値 | 検索モード |
|---|---|
| 1 | 検索範囲の上（先頭）から下（末尾）へ検索（既定値） |
| -1 | 検索範囲の下（末尾）から上（先頭）へ検索 |
| 2 | 検索範囲のデータが昇順に並んでいるとしてバイナリ検索 |
| -2 | 検索範囲のデータが降順に並んでいるとしてバイナリ検索 |
この例では省略したので「1」を指定したとみなされ、上（先頭）から下（末尾）へ検索されます。
XMATCH関数で得点に対応するランクが返りました。
数式 =XMATCH(D2,B2:B8,-1)
XMATCH関数で複数の検索値を指定してスピルで一括検索
XMATCH関数の第一引数「検索値」をセル範囲や配列で指定してスピル機能によってまとめて検索することができます。
前章で使った表で、得点のセルを３つに増やして、まとめて検索してみましょう。
取得する個数の最初のセルを選択して、XMATCH関数の第一引数「検索値」に「あなたの得点」のセル範囲をドラッグで指定します。
第二～第四引数は前章と同じです。
Microsoft365及びExcel2021ではスピル機能によってExcelが自動で予測した結果を返すため、最初のセルの数式が確定すると同時に、他の２つのセルの戻り値も表示されます。
MATCH関数の「照合の種類」とXMATCH関数の「一致モード」の違い
XMATCHとMATCHの主な違いは第三引数の「照合の種類」と「一致モード」にあります。
XMATCHの既定値は完全一致
MATCH関数の第三引数「照合の種類」の既定値（省略した場合に選択される値）は検索値の大きい方の近似値を返す「1」です。即ち、完全一致で検索するには必ず「0」を指定しなくてはなりません。
XMATCH関数の「一致モード」では既定値が完全一致の「0」になっています。頻度からいえば完全一致での検索が最も多いでしょうから、省略することで数式がシンプルになります。細かいことですが、他の関数にネストして使うことが多いので引数を一つ省略できるのは思う以上の利点です。
XMATCHでは近似値の検索でデータを昇順または降順に並べ替えておく必要がない
MATCH関数では「照合の種類」に1か-1を指定した時は検索範囲のデータを昇順または降順に並べ替えておく必要がありますが、XMATCHではこの並べ替えの手間が不要です。
- XMATCHは検索値が検索範囲の最初のセルから何番目に位置しているかを数値で返すMATCHの機能を拡張させた関数です
- XMATCH関数はExcel2021から一般公開された新関数で、使えるExcelバージョンはMicrosoft365と2021～です
- MATCH関数の「照合の種類」の既定値は大きい方の近似値で、完全一致の場合は「0」指定が必須ですが、XMATCH関数の「一致モード」の既定値は完全一致なので省略できます
- XMATCH関数では近似値を検索する場合、データを昇順（または降順）に並べ替えておく必要がありません
- XMATCH関数で検索値にセル範囲や配列を指定してスピルで一括表示することができます
- XMATCH関数では「検索モード」の指定で検索の順序を「上から、下から、昇順、降順」の選択ができます