XMATCH 関数について紹介します。
XMACH 関数は MATCH 関数の上位版です。機能はどちらも、検索値が指定したセル範囲内の何番目にあるかを返す関数です。
今回は XMATCH 関数です。この関数もサブスクリプション版の「Microsoft 365」アプリの「Excel for Microsoft 365」と永続ライセンス版の最新版 Excel 2021で使用できます。
XLOOKUP 関数が VLOOKUP 関数の上位版であるのと同じく、XMATCH 関数は MATCH 関数の上位版です。違いを比較しながら見ていきましょう。
機能的には同じなんですよね。どこが違うのかなぁ？
今回もよろしくお願いしますm(__)m
【この記事でわかることは】
・XMATCH 関数について使用例を使って解説します。
・MATCH 関数との違いがわかります。
XMATCH 関数の構文
まずは、XMATCH 関数の「数式オートコンプリート」を確認してみます。
さらに、表示される引数の表示画像がこちらです。
XMATCH(検索値, 検索範囲, [一致モード], [検索モード])
この関数のオートコンプリートはそのままでもわかりますね(^^)
=XMATCH(検索値, 検索範囲, [一致モード], [検索モード])
※[ ]の引数は省略可能です。
引数を詳しく見てみます。
| 引数 | 内容 |
|---|---|
| 検索値 | 【必須】検索値を指定します ※全角と半角は区別されます |
| 検索範囲 | 【必須】検索対象範囲(配列)を指定します ※セル範囲は1行または1列で指定します |
| [一致モード] | 次の数値で検索値との一致の種類を指定します 0：完全一致（省略した場合の既定値） -1：完全一致または次に小さい値 1：完全一致または次に大きい値 2：ワイルドカードを使用した検索が可能 |
| [検索モード] | 次の数値で検索方法を指定します 1：先頭から末尾へ検索（省略した場合の既定値） -1：末尾から先頭へ(逆方向に) 検索 2：昇順に並んだ検索範囲をバイナリ検索 -2：降順に並んだ検索範囲をバイナリ検索 |
XMATCH 関数の使用例
それでは使用例を見ていきましょう。
列方向に指定検索する例
検索値と検索範囲だけ指定した基本的な例
G列の結果には、品名の列から検索値が最初にヒットした位置を返しています。
位置のスタートは１から始まります。列の場合は範囲を上から順番に検索しています。
XMATCH 関数 は [一致モード] が既定で「完全一致」になっています。
該当がない場合のエラーを表示させないようにしたい場合、IFERROR関数を使いましょう。
この例では、=IFERROR(XMATCH(F2,$C$2;$C$13),””) とすれば空白表示になります。
[一致モード][検索モード]を指定する例
[一致モード]を「1」にした場合
![XMACH関数の[一致モード][検索モード]を指定する例](https://www.kurumico.com/wp-content/uploads/2022/04/xmatch-06.jpg)
[検索モード]も「-1」として降順に検索しています。
[一致モード]が「1」の場合は、一致しなかった場合次に大きい値の位置を返します。
[一致モード]が「-1」の場合
![XMACH関数の[一致モード]が[-1]の例](https://www.kurumico.com/wp-content/uploads/2022/04/xmatch-05.jpg)
[一致モード]が「-1」の場合は、検索値が一致しなかった場合次に小さい値の位置を返します。
この例では「商品F」は該当が無かったので、次に小さい値「商品E」の位置を返しています。
数値列[販売数量]を検索範囲とした例
この例で[一致モード]を「-1」に変更した場合は、エラーとなっているG9セルは「11」を返します。
「150」の次に小さい値は「139」です。「139」は 11番目の値です。
ワールドカードを使って指定する例
[一致モード]に「2」を指定すると、ワールドカードを使った検索が可能になります。
ワイルドカード文字自体を検索対象にする場合は、検索文字前に“~”「チルダ」を添えて使用します。
例では、「?」の文字を検索しています。「*」を検索する場合は「~*」と指定します。
2列を指定する場合の例
この例は、検索値が [店舗コード]&[商品] のような組み合わせとしたい場合の例です。
スピル機能では、セル範囲を結合することができます。検索範囲も [店舗コード]&[商品] のように範囲$B$2:$B$13&$C$2:$C$13 と「&」で連結して指定すれば複数条件に対応できるという事例です。
でも、検索範囲を「$B$2:$C$13」と2列を指定すると #VALUE! エラーになります。
※検索範囲は、1列または1行でしか指定できません。
行方向に指定検索する例
この例では、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
【検討中の今後の記事内容は・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思っています・・・・
・今後の記事にご期待ください(^^)/
過去記事のサンプルファイルをダウンロードできます
過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら（このリンク先）からご利用ください
