【エクセル】XMATCH関数の使い方と事例を紹介。INDEX関数やVLOOKUP関数と組み合わせ！

Excel(エクセル)の関数の中で、XMATCH関数は、XLOOKUP関数と一緒に登場した新関数です。

MATCH関数と基本的な機能は同じで、探したい値が表の中でどの位置にあるかを知りたい時に役立つ関数です。

ただ、MATCH関数より利便性がUPしています！

この記事では、XMATCH関数の基本的な使い方から、INDEX関数など別の関数と組みわせた応用編の使い方まで幅広く解説します。

XMATCH関数とは？

XMATCH関数の読み方は、エックス・マッチ関数です。

XMATCH関数は、Microsoft365(サブスク)とExcel2021、Excel2024のバージョンで利用できます。

XMATCH関数は、指定した検索範囲内で、数値や文字列などの検索値が、どの位置にあるかを数値で返す関数です。

例えば、下の表の「A、B、C、D」という文字の「B」の位置を調べるために、MATCH関数を使うと、左から２番目にあるので、「２」という結果が得られます。

この説明だけだと、「文字の位置を知って何に役立つの？」と思うかもしれません。

そう、そのとおりです。

実際には、XMATCH関数は、INDEX関数などの他の関数と組み合わせて利用することが多いです。

そうすることで、他の関数の引数の変更を自動化させたり、表から特定の値を検索する際に利用することで、その効果を発揮します。

XMATCH関数とMATCH関数の違い

MATCH関数の書式は、以下のとおりです。

＝MATCH(検査値,検査範囲,[照合の型])

XMATCH関数の書式は、以下のとおりです。

=XMATCH(検索値,検索範囲,[一致モード],[検索モード])

書式からも分かるとおり、XMATCH関数とMATCH関数の基本的な機能は同じで、「指定した検索値が何行目(何列目)にあるかを数値で返す関数」です。

しかし、XMATCH関数はMATCH関数の後継関数で、より強化しています。

そのため、MATCH関数では出来なかった以下の２点の処理がXMATCH関数では行えます。

上の２点を活用しないのであれば、MATCH関数で十分実務に利用できます。下のリンク記事でMATCH関数の使い方を紹介しているので、参考にしてください。

XMATCH関数の書式と引数

XMATCH関数の書式は以下のとおりで、４つの引数で構成されています。

=XMATCH(検索値,検索範囲,[一致モード],[検索モード])

先述のとおり、XMATCH関数は、指定した検索範囲内で、数値や文字列などの検索値が、相対的にどの位置にあるかを数値で返す関数です。

最後の２つの引数「一致モード」と「検索モード」は省略可能なので、特殊な使い方をしなければ、「＝XMATCH(検索値,検索範囲)」という構文で理解しても問題ありません。

続いて、それぞれの引数について詳しく解説します。

XMATCH関数/第1引数：検索値

１番目の引数「検索値」は、どの文字を探したいかを指定します。(例：A1、”商品A”)

この引数は必ず設定が必要です。

検索した値やセルを参照して指定します。文字列などの値を指定する時は、”商品A”というようにダブルクォーテーションで囲って使用します。

検索値は２番目の引数「検索範囲」の中から検索されるので、この範囲内に探したい文字が含まれている必要があります。

３番目の引数「一致モード」で「2」を指定した場合は、「検索値」に疑問符(？)やアスタリスク(*)などのワイルドカードも使用することが出来ます。

XMATCH関数/第2引数：検索範囲

２番目の引数「検索範囲」は、どのセル範囲から検索値を探したいかを指定します。

検索範囲の中から、検索値の相対的な位置を返す関数のため、検索範囲は、「B2:B9」や「B2:E2」というように、１行もしくは１列で指定する必要があります。

XMATCH関数/第3引数：[一致モード]

３番目の引数「一致モード」は、「検索値の探し方」のことです。

「一致モード」は、「-1」「0」「1」「2」のいずれかを指定し、「0」であれば引数の指定は省略できます。

「一致モード」は、基本的に「0」(もしくは省略)で利用されることが非常に多いと覚えておきましょう。 

XMATCH関数/第4引数：[検索モード]

４番目の引数「検索モード」は、「検索値を探す向き」のことです。

「検索モード」は、「-1」「1」「-2」「2」のいずれかを指定し、「1」であれば引数の指定は省略できます。

「検索モード」は、基本的に「1」(もしくは省略)で利用されることが非常に多いです。 

【基本編】XMATCH関数を使う方法

MATCH関数の基本的な使い方を３つ解説します。

この表の中から、特定の文字が何行目にあるか、何列目にあるかを求めます。この見出しで引数の使い方を覚えましょう。

使い方(その１)：検索値が何列目(何行目)か調べる

下のG3セルにXMATCH関数を挿入して、「鳥取県」が表の３行目から数えて何行目にあるかを調べてみましょう。

G3セルに、以下のXMATCH関数の数式を挿入します。

=XMATCH(F3,C3:C8)

１つ目の引数「検索値」⇒「鳥取県」の位置を探したいので「F3」セルを指定
２つ目の引数「検索範囲」⇒「鳥取県」を含んだ「地域」列の「C3:C8」を指定
３つ目の引数「一致モード」⇒「鳥取県」と完全に一致するセルの位置を探すため省略(「0」でも可)
４つ目の引数「検索モード」⇒先頭方向から探すため探すため省略(「0」でも可)

その結果、XMATCH関数で返された数値は「4」となり、「鳥取県」が表の3行目から数えて４行目の位置にあることが分かりました。

XMATCH関数を使って、行見出しの「地域」という文字が何列目の位置にあるかを調べることもできます。

G5セルに以下のXMATCH関数を挿入することで、「地域」が２列目にあることが分かります。

=MATCH(F5,B2:D2,0)

使い方(その２)：[一致モード]の使い方

上でも説明しましたが、XMATCH関数の第3引数「一致モード」は、「-1」、「0」(もしくは省略)、「1」、「2」のどれかを指定します。

「0」で利用する場合がほどんどですが、「-1」「1」「2」の使った事例を１つずつ紹介します。

ますは、XMATCH関数の第３引数「一致モード」を「-１」で利用する方法です。

「-1」を利用すると、『検索値以下の中から最大値のセルの位置を検索』します。

下の表のように、D列が昇順に並べ替えされている数値に対して、「850」を検索値にしてXMATCH関数を使用します。

=XMATCH(F3,D3:D8,-1)

そうすると、検索値「850」はD列にはありませんが、850以下で最大の値はD4セルの「800」なので、XMATCH関数の結果は「2」と返されました。

続いて、XMATCH関数の第３引数「一致モード」を「１」で利用する方法です。

「1」を利用すると、『検索値以上の中から最小値のセルの位置を検索』します。

下の表のように、D列が降順に並べ替えされている数値に対して、「850」を検索値にしてXMATCH関数を使用します。

=XMATCH(F3,D3:D8,1)

そうすると、検索値「850」はD列にはありませんが、850以上で最小の値はD6セルの「900」なので、XMATCH関数の結果は「4」と返されました。

最後に、XMATCH関数の第３引数「一致モード」を「２」で利用する方法です。

「２」を利用すると、『ワイルドカード文字を使って文字の位置を検索』できます。

ワイルドカード文字は、”*”（アスタリスク）や”？”(疑問符)といった記号のことで、これらの文字を使うことで部分一致する検索が可能となります。

例えば、下の表から「ん」で終わる文字の位置を検索するため、以下のXMATCH関数を挿入します。

=XMATCH("*ん",B3:B8,2)

そうすると、「ん」で終わる文字列は、B5セルの「みかん」なので、XMATCH関数の結果は「3」と表示されます。

ワイルドカードの使い方を詳しく知りたい方は、以下の記事を参考にしてください。

使い方(その３)：[検索モード]

XMATCH関数の４番目の引数「検索モード」は、「検索値を探す向き」のことです。

この「検索モード」は基本的に「１」を入力しますが、「-1」を指定することで、選択したセル範囲の末尾からの検索が可能となります。

例えば、下の表のC列には「和歌山県」という文字がC5セルとC8セルの２つに入力されています。

この表の下方向から「和歌山県」の位置を検索するために、以下の数式を挿入します。第３引数は使わないので、第４引数の手前にカンマを2つ挟みます。

=XMATCH(F3,C3:C8,,-1)

その結果、第４引数を「-1」で指定したため、C8セルの位置である「6」が返されます。

【応用編】XMATCH関数を使った事例

XMATCH関数の応用編の使い方を４つ紹介します。

特に、XMATCH関数はVLOOKUP関数やINDEX関数と一緒に利用することで、利便性がUPします。

事例１．XMATCH関数×INDEX関数(値を検索)

INDEX関数とXMATCH関数を組み合わせて利用することで、表の中から任意の値を抽出することができます。

INDEX関数は、指定した行と列が交差するセルの値を抽出する関数です。

INDEX関数が『VLOOKUP関数の代わりに使う関数』で、『XMATCH関数が検索範囲から抽出する位置を指定する役割』を果たします。

例えば、下の商品リストから、『B店』の『シャンプー』の値段を抽出する場合、G9セルに以下の数式をすると、表の３行目と３列目が交差するセルの値「720」円を抽出できます。

=INDEX(B4:E10, XMATCH(G4,B4:B10), XMATCH(H4,B3:E3))

G4セルとH4セルを『G店』と『石鹸』に変更すると、数式を変更しなくても、G店の石鹸の値段「360」円を自動で抽出することができます。

INDEX関数の使い方を詳しく知りたい方は、以下の記事を参考にしてください。

事例２．XMATCH関数×INDEX関数(複数条件で検索)

INDEX関数とXMATCH関数を複数条件で利用して、表から値を抽出することもできます。

例えば、下の表から「大阪府」の「A店」の「目薬」の値段を抽出する、といった場合です。

まず、「販売店」の右に列(キー列)を追加します。「＆」演算子を利用して、「地域」と「販売店」の文字列を結合します。

=B4&C4

I9セルに、XMATCH関数とINDEX関数を組み合わせた以下の数式を挿入します。

１つ目のXMATCH関数の第１引数を「H4&I4」と入力することで、作成したキー列の文字が何行目にあるかを調べることができます。

=INDEX(D4:F10,XMATCH(H4&I4,D4:D10),XMATCH(J4,D3:F3))

そうすると、「大阪府のA店の目薬の値段」1,800円を検索することができます。

事例３．XMATCH関数×VLOOKUP関数(列番号の自動化)

VLOOKUP関数は表から検索値に該当する値を抽出する関数です。

しかし、VLOOKUP関数を複数のセルで利用する際、引数「列番号」の数値をいちいち手入力で変更していくのは面倒です。

そんな時、下の図のように、VLOOKUP関数の引数「列番号」にXMATCH関数を組み込むと、列番号の入力を自動で行うことが出来ます。

例えば、下の表から商品番号「25」の商品カテゴリを調べるために、以下のVLOOKUP関数を挿入します。

=VLOOKUP($G$3,$B$3:$E$9,XMATCH(H2,$B$2:$E$2),FALSE)

第２引数「列番号」には「XMATCH(H2,$B$2:$E$2)」と入力することで列番号を取得できるので、VLOOKUP関数の結果は正しく「薬」と表示されます。

このVLOOKUP関数をI3セル、Jセルにコピペしても、列番号はXMATCH関数によって自動で取得してくれるので、商品番号25の「商品名」や「値段」が正しく抽出されます。

VLOOKUP関数の引数「列番号」にMATCH関数を組み合わせる手順は、下の記事で紹介していますので、合わせて参考にしてみて下さい。

HLOOKUP関数も同様に、XMATCH関数と組み合わせることで引数「行番号」を自動化することができます。

事例４．XMATCH関数×OFFSET関数

INDEX関数に似た関数に、OFFSET関数があります。

OFFSET関数は、『指定した分だけ移動して、単一セルやセル範囲を選択する関数』です。

=OFFSET(基準,行数,列数,[高さ],[幅])

OFFSET関数の中にXMATCH関数を挿入することで、表の中から指定した縦項目と横項目がクロスする位置の値を抽出することができます。

⇩OFFSET関数の使い方

例えば、下の料金表を使って、G10セルに以下のOFFSET関数を挿入します。

OFFSET関数の第１引数「基準値」を表の左上のB2セルにします。第2引数と第3引数にそれぞれXMATCH関数を挿入して、C9セルとC10セルの検索条件と一致する表の位置まで基準値を移動させて、料金表から該当する料金を検索します。

=OFFSET(B2,XMATCH(C9,B3:B7),XMATCH(C10,C2:G2))

C9セルとC10セルの条件を変更すると、検索したい料金が自動で更新されて出力されます。

事例５．XMATCH関数×スピル

XMATCH関数は、スピル機能で利用することができます。

スピルとは、「数式の結果がセルからあふれて、隣接したセルにも拡張されて出力される機能」です。

例えば、XMATCH関数の第１引数は基本的に１つのセルを指定しますが、下の数式のようにセル範囲で指定します。

=XMATCH(F3:F5,C3:C8)

そうすると、XMATCH関数はG3セルにしか入力していませんが、スピル機能が働いて、G4、G5セルにもXMATCH関数が反映されます。

結果、１つのXMATCH関数でF4セルとF5セルの文字の位置まで返すことができます。

スピル機能の詳しい使い方を知りたい方は、以下の記事を参考にしてください。