使用関数：MATCH, INDEX, XMATCH
MATCH（マッチ）関数は検索したい値（検査値）を指定した範囲（検査範囲）内で探して照合し、一致する値を抽出します。MATCHは「合う」「一致する」を意味します。単独で使うことはあまりなく、INDEX関数やVLOOKUP関数など他の関数と組み合わせた使い方をすることで真価を発揮します。
MATCH関数の基本的な使い方
簡単な使用例を挙げて、MATCH関数の使い方の基本を説明します。得点の高い順に氏名と点数を並べた表があります。その中の誰か一人の順位をMATCH関数で求めてみます。
MATCH関数の引数は３つ。「検査値」「検査範囲」「照合の種類」です。
- 第一引数「検査値」
- 「検査値」は検索のキーで、照合する元の値です。この例では順位を知りたい氏名の一つを検査値に指定します。
- 第二引数「検査範囲」
- 「検査範囲」は同一行か同一列の連続したセル範囲で、その先頭のセルを「1」として検索値とマッチする値の位置までを数えた数字が返されます。この例では検査値を検索する氏名のデータ範囲をドラッグで指定します。
- 第三引数「照合の種類」
- 「照合の種類」は検査値の照合方法です。検査値と検査範囲内の値をどう照合するかを選びます。
MATCH関数で照合する方法は３種類です。- 「0」・・・完全一致の値を検索します。※ワイルドカードを使えば部分一致で検索可。
- 「1」・・・昇順一致。「検索値」以下で最大値を検索。検索範囲のデータを昇順に並べ替えておく必要があります。
- 「-1」・・・降順一致。「検索値」以上で最小値を検索。検索範囲のデータを降順に並べ替えておく必要があります。
この例では完全一致の「0」を入力します。
MATCH関数で検査値に指定した氏名の順位が取得できました。
MATCH関数でワイルドカードを使った曖昧検索と抽出
検索にワイルドカードが使えるのはMATCH関数の利点です。ワイルドカードを使って部分一致の文字列を指定して検索し、一致する値の場所を抽出できます。使えるワイルドカードは「*」と「?」です。
MATCH関数の検査値に「”三*子”」と指定して、三で始まり、子で終わる氏名の順位を抽出します。アスタリスク*は任意の文字列を表すワイルドカードです。
任意の1文字を表す半角疑問符?を使って、「”?????子”」としても同じ氏名の抽出ができます。文字列の中の空白も1文字として数えます。
MATCH関数とINDEX関数を組み合わせる使い方
MATCH関数とINDEX関数は相性がよく、使用頻度の高い組み合わせです。この使用例では、INDEX関数の２つの引数「行番号」「列番号」にMATCH関数をネストして、「品名」と「サイズ」の複数条件に応じた金額をメニューから抽出します。
- 最初にINDEX関数の引数「配列」にメニューの金額のデータ範囲を指定し、F4キーで絶対参照にしておきます。
- INDEX関数の「行番号」に入れ子するMATCH関数で1つ目の条件を指定します。
「検査値」に注文品名の入ったセルをクリックで参照、「検査範囲」にメニューの品名の一覧をドラッグで指定し、F4キーで絶対参照にします。「照合の種類」は完全一致の「0」を入力します。
数式は「MATCH(F2,$A$2:$A$7,0)」となります。「照り焼きチキンと完全にマッチする文字列がメニュー一覧の上から何番目にあるか」という意味の数式です。この答えは「2」で、INDEX関数の「行番号」に「2」と指定したことになります。 - INDEX関数の「列番号」に入れ子するMATCH関数で2つ目の条件を指定します。「検査値」に注文サイズの入ったセルをクリックで指定、「検査範囲」にメニューのサイズの一覧をドラッグで指定し、F4キーで絶対参照にします。「照合の種類」は完全一致の「0」を入力します。
数式は「MATCH(H2,$B$1:$D$1,0)」となります。
「Lと完全にマッチする文字列がサイズ一覧の左から何番目にあるか」という意味の数式です。この答えは「3」で、INDEX関数の「列番号」に「3」と指定したことになります。 - MATCH関数によって縦方向へ２、横方向へ３検索して交差する位置の金額データを抽出する数式ができました。
- 注文には個数のセルもあるので、この数式に個数のセルを掛け算します。
数式 =INDEX($B$2:$D$7,MATCH(F2,$A$2:$A$7,0),MATCH(H2,$B$1:$D$1,0))*G2
他の注文のセルにオートフィルコピーして完了です。注文の総計金額にはSUM関数「=SUM($I$2,$I$3)」が入っています。
INDEX関数の使い方はこちらの記事で詳しく！
MATCH関数で範囲の可変に対応する引数の指定方法
前章で解説した例ではMATCH関数の引数「検索範囲」とINDEX関数の引数「配列（または参照）」には絶対参照にしたセル範囲を指定しました。データ表が固定的なものなら問題ありませんが、データが増える度に数式の範囲を指定し直すのは現実的ではありません。範囲の可変に対応できるようにMATCH関数・INDEX関数の引数を指定しておきます。
- INDEX関数の引数「配列」にシートの全範囲を絶対参照で指定します。シートの左上角をクリックして全範囲を指定し、F4キーで絶対参照にしておきます。
- INDEX関数の「行番号」に入れ子するMATCH関数の引数「検索範囲」に列番号をクリックしてA列を指定します。
- INDEX関数の「列番号」に入れ子するMATCH関数の引数「検索範囲」に行番号をクリックして1行を指定し、F4キーで絶対参照にしておきます。
- 上記以外は前章の操作と同じです。「照合の種類」はどちらのMATCH関数も完全一致の0を指定します。
数式はこうなります =INDEX($1:$1048576,MATCH(F2,A:A,0),MATCH(H2,$1:$1,0))*G2 - 表にデータを追加し、検査値の文字列を入れ替えてみました。範囲の可変に対応して自動で金額データが抽出されます。
MATCH関数の代わりにXMATCH関数＋INDEX関数の組み合わせ
Microsoft365とExcel2021から追加されたXMATCHはMATCH関数を進化させた新関数です。前章で解説したINDEX関数と組み合せる例では、MATCH関数の代わりにXMATCH関数を使っても同じ結果を得られます。
MATCH関数とXMATCH関数の違いは、①XMATCH関数では引数「検査値」をセル範囲や配列で指定できる、②MATCH関数で完全一致を検索するには「照合の種類」に0を指定する必要があるが、XMATCHではこれを省略ができる、③XMATCHは上からも下からも検索できる、などがあります。
この例ではXMATCH関数の「検査値」を２つまとめて指定し、「照合の種類」は省略しました。
MATCH関数を入れ子したINDEX関数の数式に個数を掛け合わせるのも２つのセルをまとめて「*G2:G3」と指定します。
新しいExcelバージョンで使えるスピル機能により、複数のセルそれぞれの値を抽出し、オートフィルコピーなしでまとめて自動計算されます。
範囲の可変に対応する指定方法で数式を作る場合も、XMATCHならシート全体の指定と行の指定を絶対参照で行う必要はありません。
数式はこうなります➔ =INDEX(1:1048576,XMATCH(F2:F3,A:A),XMATCH(H2:H3,1:1))*G2:G3
XMATCH関数の使い方、MATCH関数との違いの詳細はこちらの記事で。
MATCH関数を使ってエラーが出る要因と対処法
MATCH関数を使って出るエラーは、照合の種類を「1（検査値以下の近似値）」か「-1（検査値以上の近似値）」にしたケースで起きやすいかもしれません。
多くの場合、表データの並び順の不備が要因となって「#N/Aエラー」が返ります。
下図はMATCH関数をINDEX関数の引数に入れ子して配送料を求めようとした例ですが、#N/Aエラーが返されています。
このエラーは「行番号」に入れたMATCH関数「MATCH(C8,F2:F6,-1)」で起こっています。
MATCH関数の引数「照合の種類」を「-1」にする時は、第二引数の「検査範囲」を降順（大きい値から小さい値へ）にする必要があります。
この例の検査範囲のデータが昇順になっているのがエラーの原因でした。データを降順に作り変えればエラーは消えて求める値が抽出できます。
逆に、MATCH関数の引数「照合の種類」を「1」にする時は、第二引数の「検査範囲」を昇順（小さい値から大きい値へ）にしておきましょう。
MATCH関数を他の関数と組み合せた使い方を覚えてExcelのスキルアップ！
ExcelのMATCH関数はINDEX関数、VLOOKUP関数、OFFSET関数など他の関数と組み合せて使うととても便利です。検索する範囲の可変に対応するようにMATCH関数の引数を指定すれば、データの追加や削除が自動で計算結果に反映されるのでデータベースの効率的な管理にも役立ちます。MATCH関数の「特定の値を検索し、照合するセルの場所を抽出して単純な数字で返す」仕組みを頭に入れて、他の関数の引数に上手に組み込んで使ってみて下さい。Excelのスキルアップを目指すならMATCH関数の使い方を習得しましょう。