XMATCH関数の基本的な使い方

基本的なXMATCH関数の使用法ですが、
=XMATCH(検索値,検索範囲,[一致モード],[検索モード])
となっております。

‘[]’に囲まれている2つの引数はオプションとなっており、省略可能です。
実行すると「検索値が検索範囲中の上（検索範囲が行方向の場合は左）から何番目に位置するか（相対位置）」が数字で返ってきます。

　　　

例えば、上記のような表があったとして、
どこか適当な表外のセルに
=XMATCH(“smith”,C3:C17,0,1)
と入力した場合、そのセルには 1 という数字が表示されます…そんな使い方する人はいないと思いますが…

また、大きな特徴として、スピルすることが挙げられます。
スピルについては後述します。

引数について

XMATCH関数使用時に入力する引数についてざっくり説明します。

検索値

探したい値を入力します。
文字列、数字、セル番号が入力可能ですが、
セル番号を使用するのが一般的です。

検索範囲

検索値を検索する範囲指定をここで行います。
表全体を指定することはできず、
1行、あるいは1列で指定する必要があります。

一致モード（オプション）

一致とする基準を指定することができます。
一致モードは省略可能で、その場合「完全一致」での検索となります。
ほとんどの場合完全一致での使用になるかと思います。

数字	一致モード	説明
0	完全一致	検索値と完全に一致した値の存在するセルの位置を返します。完全一致するセルがない場合はエラーとなります
-1	完全一致または次に小さい項目	完全一致するセルがあればそのセル、存在しない場合は検索値より小さく、最も近似した値のセルの位置を返します
1	完全一致または次に大きい項目	完全一致するセルがあればそのセル、存在しない場合は検索値より大きく、最も近似した値のセルの位置を返します
2	ワイルドカード文字との一致	0文字以上の任意の文字列を表す「*」や、任意の1文字を表す「?」などを含めた検索ができます

検索モード（オプション）

検索をかける方法を指定することができます。
検索モードも省略可能で、
記載しなかった場合は先頭から末尾の方向で検索をかけます。

例えば、上記の表でお小遣いが3000円のセルがどこにあるか知りたい、という場合
(3000,G3:G17,0,1)と記載すると上から数えて1番目のG3にあるため1が返ってきます。
(3000,G3:G17,0,-1)と記載すると下から見ていくと7番目のG11に検索値と同じ値があるため、
対象のセルはG11となります。
値は7と返ってきそうですが、G3（上）から数えて9番目に位置するという見方をするため、返ってくる値は9となります。

数字	検索モード	説明
1	先頭から末尾	先頭から末尾方向に検索をかけます
-1	末尾から先頭	末尾から先頭方向に検索をかけます
2	バイナリ検索（昇順で並べ替え）	昇順で並べ替えられているデータに対して、バイナリ探索（二分探索）をかけることで効率的に検索可能になります
-2	バイナリ検索（降順で並べ替え）	降順で並べ替えられているデータに対して、バイナリ探索（二分探索）をかけることで効率的に探索可能になります

MATCH関数との違い

MATCH関数は
=MATCH(検索値,検索範囲,[照合の種類])
と記述します。
XMATCH関数と書き方は少々異なりますが、基本的な使い方は同じです。

大きな違いはXMATCH関数における「一致モード」に当たる「照合の種類」のデフォルト値が1（検索値以下の最大値）
となっており、完全一致で探したい場合はわざわざ0を指定しなければ想定とは異なる数値が返されます。


目視で確認できる規模のデータならまだしも、
扱うデータが大きくなるとミスに気付かないままになってしまうかもしれませんね…。

INDEX関数との組み合わせ

XMATCH関数は単体ではあまり使えない（と私は思っている）関数ですが
INDEX関数と組み合わせることで、LOOKUP関数のように、特定のデータを探して表示させることができます！
例えば、上で作成した表の中から、「番号が1,3,5番の名前と年齢とお小遣いだけ表示させる」といったことができるようになります。

もともとINDEX + MATCHも強力で広く使われているようですが、
XMATCHへ拡張したことでさらに強力になっています。

INDEX関数について

INDEX関数は特定の範囲内での行番号と列番号を指定すると、そのセルに入っている値を返してくれる関数です。
例えば、下記の表からSmithさんのお小遣いの金額を知りたいとします。
そこで、=INDEX(C3:G17,1,5) と書きます。「C3からG17までを検索範囲として1行目、5列目の値を表示してね」という命令です。そうすることで見事Smithさんのお小遣いの金額である3000という値が表示されるのです。

INDEX関数を使用するには、そもそも検索したい値の入ってるセルの行と列の位置を知ってなくてはならないので、
単体で活躍する場面としては少なさそうな印象です。

今回の関数の組み合わせでは、INDEX関数の引数である行と列の位置をベタ打ちで指定するのではなく、
XMATCH関数で返ってきた数字を使うことで検索したい項目と番号に合わせた値を参照しよう、というわけです。

具体例

先ほどの表のJ3に=INDEX($C$3:$G$17,XMATCH($I$3:$I$17,$B$3:$B$17),XMATCH($J$2:$N$2,$C$2:$G$2))
と数式を記載しました。
INDEX関数の第一引数には検索したい値が含まれている範囲を指定します。
元となる表のデータが入っている部分（下記表の青枠部分）を指定しました。

続いて第二引数に行番号を指定しますが、XMATCH関数を使用しています。
XMATCH関数の引数には検索したい番号（橙枠部分）と検索範囲（紫部分）を指定し、
完全一致したセルの相対位置が数字でINDEX関数の第二引数として渡されます。

INDEX関数の第三引数にもXMATCH関数を使用して列番号を指定しています。
第二引数と同様に検索したい項目（緑枠部分）と検索範囲（桃枠部分）を指定し、
完全一致したセルの相対位置が数字でINDEX関数の第三引数として渡されます。

具体的にJ3セルに絞ってみてみると、番号1番に該当する行番号1と、項目「名前」に該当する列番号1を取得し、
INDEX関数の第一引数で指定した検索範囲の中の1行1列目にある”Smith”を参照して表示してくれています。

スピルについて

上記の例ではXMATCH関数の検索値にセル範囲を指定しているため、INDEX関数の引数に対して複数個検索結果が返ってくることになります。
ここで冒頭で触れたスピルが発生し、J3～N17まで自動的に対応する検索結果を返してくれます。

スピルとは「こぼれる」という意味で、1つのセルに複数の値が返る数式を記載した際に、隣接するセルにこぼれるように対応する結果を自動的に表示する便利機能です。

1つのセルに数式を書くだけで対象となる範囲すべてに対応する値を返してくれるのはとても便利ですし、
項目名や番号で検索をかけているので、順番を入れ替えてもちゃんと参照して正しい値を返してくれます。

まとめ

XMATCH関数はINDEX関数と組み合わせることで、V(X)LOOKUP関数のようにデータを参照するのに役立ちます。
また、項目の順番を入れ替えたり、項目数を減らしたりしても順次に対応する値を表示できるのは
おそらくXLOOKUP関数にない強みと言えます。
Excelを社内外の方に見せながら説明する際にも使えそうだなぁと思っております。

自分はまだまだExcel初心者ですが、いろいろ試してみてできることを増やしていきたいです。
最後までお読みいただき、ありがとうございました。

その他の記事も読む

・地域住民の生活行動範囲に基づいた「生活圏エリアマーケティング -LAM-」
・【Excel】CSVファイルの文字化けを解消する方法
・【Excel】住所から都道府県名だけを抜き出す方法
・Excel Power Pivotをリボンに表示する方法
・【Excel】bin切りをpivot table内で実行
・ExcelのデータバーをPowerpointに貼る方法
・【Excel】XMATCH関数の基本的な使い方とINDEX関数の組み合わせ
・Excelで2つのシートを比較する方法
・【Excel】重複なしのデータ個数を算出する方法
・【Excel】セルの数式をそのままコピーペーストする方法

一覧へ戻る