【EXCEL】配列や範囲から一致する場所を探す！XMATCH関数

配列や範囲から一致する場所を探す！

今回は、配列またはセル範囲内で指定された項目を検索し、最初に一致した項目の相対的な位置を返すXMATCH関数を紹介します。

おなじみのMATCH関数の上位互換関数みたいな感じです(^o^)

この関数も、スピル機能が搭載されたことでできた、新しい関数です(^o^)

XMATCH関数

検索値・・・検索する値の設定（必須）

検索範囲・・・検索する範囲、または配列を指定。１行か１列で設定する（必須）

[一致モード]・・・「０」完全一致、「-1」完全一致、または次に小さい項目、「1」完全一致または次に大きい項目、「2」ワイルドカード文字との一致（省略した場合０の完全一致）　※ワイルドカード文字：「?」任意の一文字、「*」任意の一文字

[検索モード]・・・検索の向きの指定。「1」先頭から末尾ｈげ検索、「-1」末尾から先頭へ検索、「2」バイナリ検索/昇順で並べ替え、「-2」バイナリ検索/降順で並べ替え。（省略した場合１先頭から末尾）

使い方

次のサンプルで使い方をみていきましょう。

一致モード・検索モードを省略する

一番基本の形である、一致モードと検索モードの引数を省略した形です。

F3セルに次の数式を入力します。

検索値にはE3を指定して、ワークシート上のセルに入力した値を使います。検索範囲はA列を列ごと指定（A:A）しています。

引数「一致モード」と「検索モード」は省略しているので、それぞれ、一致モード：０の完全一致、検索モード：１の先頭から末尾へ検索となります。

E2セルに「からあげ」と入力すると、次の図のように数式を入れたF3セルには２が表示されます。

指定したA列で何番目に、「からあげ」があるかを探してくれます。

今回、範囲を列ごと指定しているので、変える値は行番号と同じ値になります。

範囲を、「A2:A11」と指定すると「１」が返ります。

基本のこの形では、単純に検索値が範囲の上から（行方向に範囲を指定した場合には、左から）何番目にあるかを返します。

一致モード

一致モードを指定した、次の数式をF3セルに入力します。

数値の検索をするので、検索範囲はC列（C:C）にします。

一致モードの引数は、-1（完全一致、または次に小さい項目）に指定。

サンプルのE3セルのは、適当に155を入れてみます。

一致モードの引数が「-1」の場合、完全一致したものがない場合、次に小さいものの場所が返されます。

サンプルの場合、検索値を155にしているので、完全一致した数値はありません。次に小さい数字は150なので、10番目の10が返ります。

感の良い方ならもう分かっていると思いますが、一致モードの引数を「1」にすると、次に大きい300が選ばれ、7が返ります。

一致モード「2」は、ワイルドカードの指定が出来ます。

文字列の検索のために、検索範囲をA列(A:A)にします。複数の答えを一気に配列で返すために、検索値も範囲で指定しています。

【ワイルドカード】

「*」は、任意の文字列、「?」は、任意の1文字となります。これを組み合わせてあいまい検索を実現出来る記号です。

検索地に指定した、あいまい検索に一致する文字列がF列に返っていることが分かります。

検索モード

検索モードについては、大きく通常の「1」と「-1」、バイナリ検索の「2」と「-2」があります。

通常の「1」「-1」は、簡単に言うと検索値が複数一致した場合、一番最初に見つかったものの場所を返すか、最後に見つかったものの場所を返すのかの違いになります。

サンプルで確認します。

検索モード１（省略したときと同じ）

検索モード「１」のときは、検索範囲の上から（行方向の時は左から）検索をします。一番最初に見つかったものの場所を返すので、この場合「2」が返ります。

検索モード-１（省略したときと同じ）

検索モード「-１」のときは、検索範囲の下から（行方向の時は右から）検索をします。一番最初に見つかったものの場所を返すので、この場合「7」が返ります。

※結果的に、範囲の一番最後の一致が返るのと同じ

バイナリ検索「２（昇順で並べ替え）」、「-2（降順で並べ替え）」

ここでは、あまり詳しくやりませんが、VLOOKUPのなどの関数と同じ様に、XMATCH関数も検索に時間がかかる関数です。

もともとのデータを降順や昇順に並べ替えた状態にするとこのバイナリ検索モードが使え、高速に検索することができるようになります。

まとめ

XMATCH関数を紹介しました。単体で使うというよりは、他の関数と組み合わせて威力を発揮する関数です。