エクセル入門
XMATCH関数(範囲から値を検索し一致する相対位置)

Excelの初心者向け入門解説
最終更新日:2020-05-01

XMATCH関数(範囲から値を検索し一致する相対位置)


XMATCH関数は、配列またはセル範囲内で指定された項目を検索し、最初に一致した項目の相対的な位置を返します。
検索値が見つからない場合は、#N/Aを返します。
MATCH関数を機能強化した新関数です。


XMATCH関数はスピルで登場した新しい関数です。
2019年にOffice365のExcelに実装された革新的な機能としてスピルがあります。数式を入力したセルから結果があふれて隣接したセルにも出力されるのがスピルです。今までは数式を入れたセルにしか結果を出せませんでしたが、スピルでは隣接するセルにまで結果が表示されます。

XMATCH関数の書式

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

検索値
必須です。
検索する値を指定します。

MATCH関数は255文字の制限がありましたが、
XMATCH関数は文字数制限がなくなりました。
ただし、エクセルの仕様として32768文字以上はセルに入れられません。

検索範囲
必須です。
検索する範囲または配列を指定します。
指定範囲または配列は、1行または1列のみです。
複数行かつ複数列を指定した場合は、#VALUE!のエラーになります。

一致モード
省略可能。
どのような状態を一致とするかを指定します。
 0 : 完全一致
-1 : 完全一致または次に小さい項目
 1 : 完全一致または次に大きい項目
 2 : ワイルドカード文字との一致
省略した場合は、0(完全一致)になります。

ワイルドカード文字
(?)半角の疑問符:任意の 1 文字
(*)半角のアスタリスク:任意の文字列

検索モード
省略可能。
検索の向きを指定します。
 1 : 先頭から末尾へ検索
-1 : 末尾から先頭へ検索
 2 : バイナリ検索(昇順で並べ替え)
-2 :バイナリ検索(降順で並べ替え)
省略した場合は、1 (先頭から末尾)になります。

従来のMATCH関数との違い

MATCH関数の書式

=MATCH(検査値,検査範囲,[照合の種類])

照合の種類
1 : 以下
0 : 完全一致
-1 : 以上
省略した場合は、1(以下)になります。

0(完全一致)については、XMATCHとMATCHは同じ機能になります。
違いは以下になります。
文字数制限が無くなったことと、
一致モード検索モードを指定した場合になります。

旧来の255文字制限の対処方法については以下を参照してください。
VLOOKUPを他の関数でやる方法
VLOOKUP関数は、エクセルの関数の中でもとても良く使われる重要な関数ですが、いくつかの問題点があります。以下のような問題点がある場合、VLOOKUP関数を使う事が出来ません。そこで、代替え案を考えてみましょう。

一致モードの使い方

0 : 完全一致

XMATCHでは、省略時は0(完全一致)になります。

行方向(縦方向)の完全一致
エクセル Excel XMATCH スピル

=XMATCH(G2,A:A)

G2セルの値をA列で検索し、その位置を返しています。
A列全体を検索範囲にしているので、行位置がそのまま戻ります。
検索範囲をA2:A50のようにした場合は、3が戻ります。
これは、MATCH関数と同じです。

=MATCH(G2,A:A,0)

MATCH関数では、完全一致の場合は照合の型を省略できません。

列方向(横方向)の完全一致
エクセル Excel XMATCH スピル

=XMATCH(A7,2:2)

A7セルの値を2行で検索し、その位置を返しています。
2行全体を検索範囲にしているので、列位置がそのまま戻ります。
検索範囲をA2:F2のようにした場合は、3が戻ります。
これは、MATCH関数と同じです。

=MATCH(A7,2:2,0)

MATCH関数では、完全一致の場合は照合の型を省略できません。

-1 : 完全一致または次に小さい項目

エクセル Excel XMATCH スピル
※検索値の順序をわざと崩しています。

=XMATCH(C2,A2:A7,-1)

検索値と完全に一致していればその行になりますが、
一致する値が無かった場合は、検索値を超えない最初に見つかった行になります。
検索値が15の場合は、15以下で15に最も近い10の行になっています。
検索範囲をA2からにしているので、4が戻されています。
これは、MATCHの1(以下)と同様の一致方法になります。

=MATCH(C2,A2:A7,1)

MATCHとの大きな違いは、検索範囲が昇順に並んでいる必要が無い事です。
上図の場合は、昇順に並んでいないのでMATCHは使えません。
ただし、大小比較で検索するなら検索範囲は並べ替えておくべきでしょう。
そうしておかないと、結果を見た時に分かりづらくなってしまいます。

1 : 完全一致または次に大きい項目

エクセル Excel XMATCH スピル

=XMATCH(C2,A2:A7,1)

-1(完全一致または次に小さい項目)との結果の違いを確認してください。
検索値と完全に一致していればその行になりますが、
一致する値が無かった時は、検索値を超えた最初に見つかった行になります。
検索値が15の場合は、15以上で15に最も近い100の行になっています。
1000000超の検索値では#N/Aとなります。

2 : ワイルドカード文字との一致

エクセル Excel XMATCH スピル

=XMATCH(C2:C5,A2:A13,2)

ワイルドカード文字
(?)半角の疑問符:任意の 1 文字
(*)半角のアスタリスク:任意の文字列
XMATCHでワイルドカードを使うことはあまり多くはないと思いますが、ワイルドカードの使い方だけは覚えておいた方が良いでしょう。

検索モードの使い方

1 : 先頭から末尾へ検索

省略時は1(先頭から末尾)です。
ここまでの使用例が全て1(先頭から末尾)です。
検索値が見つかった最初の行の項目が返されます。

-1 : 末尾から先頭へ検索

エクセル Excel XMATCH スピル

=XMATCH(C2,A:A,0,-1)

検索値が検索範囲に複数ある場合は、一番下の行が採用されます。
このように検索値が複数存在し、その一番下の行を取得したい場合に使います。

2 : バイナリ検索(昇順で並べ替え)

【奥義】大量データでの高速VLOOKUP
大量データからのVLOOKUPを大量行に設定すると再計算がなかなか終わらなくなります… そんな経験したことがある人は、少なからずいると思います、そんな場合に、高速にVLOOKUPを実行する方法です。以下の表で説明します。※Sheet1は、A列で昇順に並び変えておきます。
ここで説明しているように、大量のVLOOKUPやMATCHはとても時間がかかります。
これはXLOOKUPやXMATCHが改善されたと言っても、やはり時間がかかります。
検索範囲のどこに検索値があるか分からないのですから、全て探すしかありません。
最初の方で見つかれば良いですが、一番最後の方にあったとしたら・・・

検索範囲が昇順に並んでいればバイナリ検索で高速検索が可能になるという事です。

MATCH関数の、「照合の種類」の1(以下)と同じモードになります。

-2 :バイナリ検索(降順で並べ替え)

1:バイナリ検索(昇順で並べ替え)と並び順が違うだけです。
検索範囲が降順に並んでいる場合は、こちらを指定してください。

MATCH関数の、「照合の種類」の-1(以上)と同じモードになります。

XMATCH関数をスピルさせる

横にスピルさせる

エクセル Excel XMATCH スピル

=XMATCH(A7:B7,2:2)

検索値に複数列を指定することで、スピルにより複数列を出力できます。
これはMATCHをスピルさせることでも同じことができます。

=MATCH(A7:B7,2:2,0)

縦にスピルさせる

エクセル Excel XMATCH スピル

=XMATCH(C2:C4,A:A)

検索範囲に複数セル範囲を指定することで、スピルにより複数行を出力できます。
これはMATCHをスピルさせることでも同じことができます。

=MATCH(C2:C4,A:A,0)

これはどちらを使ってもあまり変わらないかもしれません。

XMATCHの応用例

VLOOKUPの縦横同時スピルの代替

エクセル Excel XMATCH スピル
※INDEX関数の戻りは書式設定されないので日付はシリアル値で表示されています。

=INDEX(C2:E51,XMATCH(G2:G4,B2:B51),XMATCH(H1:J1,C1:E1))

2つのXMATCHは、名前の行位置と項目の横位置を取得しています。
INDEXもXMATCHも検索値に範囲を指定することでスピルします。
これは、単純にINDEXの行番号と列番号に配列を指定して縦横スピルさせたものになります。
これなら、MATCHで検索できさえすれば自由に条件設定できますし、項目順序の入れ替えも可能です。。

エクセル Excel XMATCH スピル

INDEX+XMATCHはとても汎用的で、今後も使われ続けることになると思います。
なお、このXMATCHはMATCHでも同じことができます。

OFFSETの行/列に使用する

エクセル Excel XMATCH スピル

このような表が良いかどうかは、この際置いときましょう。
P2セルの商品でA列を検索して、各月の売上、予算、予算比を取得します。

=OFFSET(C1:N1,XMATCH(P2,A:A)-1,0,3)

このXMATCHはMATCHでも同じことができます。

スピルによって新しく追加された関数

関数名 説明
FILTER フィルターは定義した条件に基づいたデータ範囲です。
SORT 範囲または配列の内容を並べ替えます。
SORTBY 範囲または配列の内容を、対応する範囲または配列の値に基づいて並べ替えます。
UNIQUE 一覧表または範囲内から重複データを削除した一覧を返します。
RANDARRAY 0から1までのランダムな数値の配列を返します。
SEQUENCE 1、2、3、4など、配列内の連続した数値の一覧を生成します。
XLOOKUP 範囲または配列を検索し、見つかった最初の一致に対応する項目を返します。
一致が存在しない場合、XLOOKUP は最も近い (概算) 一致を返すことができます。
XMATCH 配列またはセル範囲内の項目の相対的な位置を返します。


ワークシート関数一覧

Excelワークシート関数の一覧と解説です、詳細解説ページへのリンクもあります。Excel2007までの関数です。2010以降追加の関数一覧 ワークシート関数の一覧(2010以降) 引数、関数構文については Excelシートの複雑な計算式を解析するVBAの関数構文 文字列関数…27 日付と時刻の関数…21 情報関数…17 論理関数…7 検索/行列関数…1…

ワークシート関数の一覧(2010以降)
Excelワークシート関数の一覧と解説です。Excel2010以降に追加された関数一覧になります。2007までのワークシート関数一覧ワークシート関数の一覧 引数、関数構文については Excelシートの複雑な計算式を解析するVBAの関数構文 Excel2010で追加されたワークシート関数 Excel2013で追加されたワークシート関数 関数名 種類と説明 …



同じテーマ「エクセル入門」の記事

SWITCH関数(複数値での切替)
XLOOKUP関数とスピル入門
スピルについて
FILTER関数(範囲をフィルター処理)
SORT関数、SORTBY関数(範囲を並べ替え)
UNIQUE関数(一意の値)
RANDARRAY関数(ランダム数値)
SEQUENCE関数(連続数値)
XLOOKUP関数(範囲を検索し一致する対応項目を返す)
XMATCH関数(範囲から値を検索し一致する相対位置)
LET関数(数式で変数を使う)


新着記事NEW ・・・新着記事一覧を見る

VBAのインデントについて|VBA技術解説(7月15日)
「VBA Match関数の限界」についての誤解|エクセル雑感(7月15日)
省略可能なVariant引数の参照不可をラップ関数で利用|VBA技術解説(7月12日)
100桁の正の整数値の足し算|エクセル雑感(7月9日)
LSetとユーザー定義型のコピー(100桁の足し算)|VBA技術解説(7月9日)
Variant仮引数のByRefとByValの挙動違い|エクセル雑感(7月5日)
Variant仮引数にRange.Valueを配列で渡す方法|エクセル雑感(7月5日)
Variantの数値型と文字列型の比較|エクセル雑感(7月1日)
VBAのVariant型について|VBA技術解説(6月30日)
VBAのString型の最大文字数について|エクセル雑感(6月20日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ