エクセル入門
XMATCH関数(MATCH関数を拡張した新関数)

Excelの初心者向け入門解説
公開日:2020-03-14 最終更新日:2022-10-14

XMATCH関数(MATCH関数を拡張した新関数)


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


XMATCH関数はスピルで登場した新しい関数です。
・スピルとは ・スピルの数式例 ・ゴースト ・スピル範囲での独特な挙動について ・スピルのエラー表示 ・スピル範囲演算子 ・暗黙的なインターセクション演算子 ・従来のスピルしないエクセルとの互換性についての注意点 ・スピル関連記事


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関数の問題点 ・解決方法 ・解決方法の具体例 ・解決方法ごとの長所短所


一致モードの使い方

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の数式 ・高速VLOOKUPの数式解説 ・高速VLOOKUPの補足
ここで説明しているように、大量の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 配列またはセル範囲内の項目の相対的な位置を返します。


ワークシート関数一覧

・文字列関数 ・日付と時刻の関数 ・情報関数 ・論理関数 ・検索/行列関数 ・数学/三角関数 ・統計関数 ・財務関数 ・エンジニアリング関数 ・データベース関数 ・アドイン/オートメーション ・キューブ ・Microsoft Office のサポート
ワークシート関数の一覧(2010以降)
・Excel2010で追加されたワークシート関数 ・Excel2013で追加されたワークシート関数 ・Excel2016/Excel2019で追加されたワークシート関数 ・Excel2021で追加されたワークシート関数 ・Excel2021より後の365で追加されたワークシート関数 ・Microsoft 365 Insider ・Microsoft「Office のサポート」



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

RANDARRAY関数(ランダム数値)

・RANDARRAY関数の書式 ・RANDARRAY関数の使用例 ・スピルによって新しく追加された関数
SEQUENCE関数(連続数値)
・SEQUENCE関数の書式 ・SEQUENCE関数の使用例 ・SEQUENCE関数を使ったエクセル問題 ・スピルによって新しく追加された関数
XLOOKUP関数(VLOOKUP関数を拡張した新関数)
・XLOOKUP関数の書式 ・従来の関数の代わりとして ・XLOOKUP関数をスピルさせる ・見つからない場合 ・一致モードの使い方 ・検索モードの使い方 ・XLOOKUP関数をネストして戻り列を可変にする ・XLOOKUP関数の戻りセル範囲を別の関数で使う ・XLOOKUP関数が縦横に同時にスピルしないことについて ・スピルと新関数の練習 ・XLOOKUP関数のVBA使用例 ・スピルによって新しく追加された関数
XMATCH関数(MATCH関数を拡張した新関数)
LET関数(数式で変数を使う)
・LET関数の書式 ・LET関数の基本的使い方 ・LET関数の段階的作成 ・文字列分割での使用例 ・LET関数作成手順の参考例 ・LET関数の今後について
スピルと新関数の練習(XLOOKUP関数、LET関数、VBAまで)
・スピルとXLOOKUP関数の練習問題 ・従来の関数+スピルで数式を作る ・ XLOOKUP関数に書き換える ・LET関数に書き換える ・VBAでユーザー定義関数を作成 ・スピルと新関数の練習のまとめ
スピルとは:スピル基礎から応用までの問題集
・目次 ・スピルって何だ編1 ・スピルって何だ編2 ・スピルって何だ編3 ・スピルって何だ編4 ・スピルって何だ編5 ・スピルって何だ編6 ・ハイレツテイスウっておいしいの編1 ・ハイレツテイスウっておいしいの編2 ・イフでウフフ編1 ・イフでウフフ編2 ・イフでウフフ編3 ・かー‼うんと頑張ってみよう編 ・サムはイフと恋をした編 ・君はヤマダなのかい編 ・毎月月末は彼女とデートなんだよ編 ・SEQUENCE編1 ・SEQUENCE編2 ・FILTER編1 ・FILTER編2 ・SORTBY編1 ・SORTBY編2 ・UNIQUE(そろそろLETも)編 ・XLOOKUP編1 ・XLOOKUP編2 ・ここまでの総合演習1 ・ここまでの総合演習2 ・ここまでの総合演習3 ・ここまでの総合演習4 ・ここまでの総合演習5 ・ここまでの総合演習6
スピルとは:スピル入門の問題と解説
・スピルって何だ編1 ・スピルって何だ編2 ・スピルって何だ編3 ・スピルって何だ編4 ・スピルって何だ編5 ・スピルって何だ編6 ・ハイレツテイスウっておいしいの編1 ・ハイレツテイスウっておいしいの編2
スピルとは:旧関数でスピルを使う問題と解説
・イフでウフフ編1 ・イフでウフフ編2 ・イフでウフフ編3 ・かー‼うんと頑張ってみよう編 ・サムはイフと恋をした編 ・君はヤマダなのかい編 ・毎月月末は彼女とデートなんだよ編
スピルとは:スピルの新関数を使う問題と解説
・SEQUENCE編1 ・SEQUENCE編2 ・FILTER編1 ・FILTER編2 ・SORTBY編1 ・SORTBY編2 ・UNIQUE(そろそろLETも)編 ・XLOOKUP編1 ・XLOOKUP編2
スピルとは:ここまでの総合演習問題と解説
・ここまでの総合演習1 ・ここまでの総合演習2 ・ここまでの総合演習3 ・ここまでの総合演習4 ・ここまでの総合演習5 ・ここまでの総合演習6


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

テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)
イータ縮小ラムダ(eta reduced lambda)|エクセル入門(2023-11-20)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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



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