本ページには広告が含まれています。

XMATCH 関数 セル範囲(配列)から検索値の相対位置を返す

XMATCH 関数について紹介します。
XMACH 関数は MATCH 関数の上位版です。機能はどちらも、検索値が指定したセル範囲内の何番目にあるかを返す関数です。

くるみこ
くるみこ

今回は XMATCH 関数です。この関数もサブスクリプション版の「Microsoft 365」アプリの「Excel for Microsoft 365」と永続ライセンス版の最新版 Excel 2021で使用できます。
XLOOKUP 関数が VLOOKUP 関数の上位版であるのと同じく、XMATCH 関数は MATCH 関数の上位版です。違いを比較しながら見ていきましょう。

機能的には同じなんですよね。どこが違うのかなぁ?
今回もよろしくお願いしますm(__)m

【この記事でわかることは】
・XMATCH 関数について使用例を使って解説します。
・MATCH 関数との違いがわかります。

スポンサーリンク

XMATCH 関数の構文

まずは、XMATCH 関数の「数式オートコンプリート」を確認してみます。

XMATCH関数の数式オートコンプリート

さらに、表示される引数の表示画像がこちらです。

XMATCH関数の数式オートコンプリートの引数表示

XMATCH(検索値, 検索範囲, [一致モード], [検索モード])
この関数のオートコンプリートはそのままでもわかりますね(^^)

【構文】

=XMATCH(検索値, 検索範囲, [一致モード], [検索モード])
 ※[ ]の引数は省略可能です。

引数を詳しく見てみます。

引数内容
検索値【必須】検索値を指定します
 ※全角と半角は区別されます
検索範囲【必須】検索対象範囲(配列)を指定します
 ※セル範囲は1行または1列で指定します
[一致モード]次の数値で検索値との一致の種類を指定します
0:完全一致(省略した場合の既定値
-1:完全一致または次に小さい値
1:完全一致または次に大きい値
2:ワイルドカードを使用した検索が可能
[検索モード]次の数値で検索方法を指定します
1:先頭から末尾へ検索(省略した場合の既定値
-1:末尾から先頭へ(逆方向に) 検索
2:昇順に並んだ検索範囲をバイナリ検索
-2:降順に並んだ検索範囲をバイナリ検索
[]表示は省略可能な引数です

XMATCH 関数の使用例

それでは使用例を見ていきましょう。

列方向に指定検索する例

検索値と検索範囲だけ指定した基本的な例

XMACH関数の既定値だけの基本的な例

G列の結果には、品名の列から検索値が最初にヒットした位置を返しています。
位置のスタートは1から始まります。列の場合は範囲を上から順番に検索しています。

XMATCH 関数 は [一致モード] が既定で「完全一致」になっています。

MATCH 関数 で「完全一致」を指定するには引数に「0」を指定する必要があります。

該当がない場合のエラーを表示させないようにしたい場合、IFERROR関数を使いましょう。
この例では、=IFERROR(XMATCH(F2,$C$2;$C$13),””) とすれば空白表示になります

[一致モード][検索モード]を指定する例

[一致モード]を「1」にした場合
XMACH関数の[一致モード][検索モード]を指定する例

[検索モード]も「-1」として降順に検索しています。
[一致モード]が「1」の場合は、一致しなかった場合次に大きい値の位置を返します。

MATCH 関数 との大きな違いは、XMATCH 関数 の場合は検索対象を昇順や降順に並べ替えておく必要が全く無いことです。
MATCH 関数 の場合は「照合の型」の指定に応じて、あらかじめ昇順または降順に並べ替えておく必要があります。

[一致モード]が「-1」の場合
XMACH関数の[一致モード]が[-1]の例

[一致モード]が「-1」の場合は、検索値が一致しなかった場合次に小さい値の位置を返します。
この例では「商品F」は該当が無かったので、次に小さい値「商品E」の位置を返しています。

数値列[販売数量]を検索範囲とした例
XMACH関数で検索範囲が数値列の例

この例で[一致モード]を「-1」に変更した場合は、エラーとなっているG9セルは「11」を返します。
「150」の次に小さい値は「139」です。「139」は 11番目の値です。

ワールドカードを使って指定する例

XMACH関数でワールドカードを使用する例

[一致モード]に「2」を指定すると、ワールドカードを使った検索が可能になります。

ワイルドカード文字自体を検索対象にする場合は、検索文字前に“~”「チルダ」を添えて使用します。
例では、「?」の文字を検索しています。「*」を検索する場合は「~*」と指定します。

2列を指定する場合の例

XMACH関数で2列を指定する例

この例は、検索値が [店舗コード]&[商品] のような組み合わせとしたい場合の例です。

スピル機能では、セル範囲を結合することができます。検索範囲も [店舗コード]&[商品] のように範囲$B$2:$B$13&$C$2:$C$13 と「&」で連結して指定すれば複数条件に対応できるという事例です。

でも、検索範囲を「$B$2:$C$13」と2列を指定すると #VALUE! エラーになります。
※検索範囲は、1列または1行でしか指定できません。

行方向に指定検索する例

XMACH関数の行方向に検索する例

この例では、H1:L2 の範囲に「商品単価リスト」があります。

E列の「単価」部分に「商品単価リスト」から単価を引き当てています。

E列の数式は、まず XMATCH(C5,$H$1:$L$1) で XMATCH 関数を使って各行C列の「品名」から単価表の品名位置を返しています。その位置番号でG2セルから OFFSET 関数 を使って「単価」を入力セットしています。

まとめ(おわりに)

以上、XMATCH 関数 について、使用例を使って解説しました。

くるみこ
くるみこ

XMATCH 関数は検索対象を昇順や降順に並べ替えておく必要が無いことと「完全一致」検索が既定になっている点で、従来のMATCH関数より使いやすくなっていると思います。

MATCH関数より使いやすそうですね! でも、最後の行方向の事例の場合XLOOKUP関数を使えばOFFSET関数は使わなくてできそうですね(^^;

くるみこ
くるみこ

そう、そのとおりです。よく気が付きましたね(^^)/
=XLOOKUP(C2,$H$1:$L$1,$H$2:$L$2,,0,1) “とすれば同じ結果が得られますしこちらの方が簡単ですね!

※「スピル」の解説についてはこちらをご覧ください。

すでに使用できるバージョンのExcelユーザーのみなさんは、いろいろ試して使い方に慣れておきましょう。職場のExcelがまだ対応していないバージョンの方も、近い将来には対応するExcelバージョンにアップグレードされるはずですから。

★★★ ブログランキング参加中! クリックしてね(^^)/ ★★★

【今後の記事について】

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思っています・・・・
・今後の記事にご期待ください(^^)/

スポンサーリンク
スポンサーリンク

過去記事のサンプルファイルをダウンロードできます

過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら(このリンク先)からご利用ください