これまでの記事でINDEXとMATCHを組み合わせた検索方法を解説しましたが、今回はその最終形態とも言える、INDEXに2つのMATCHを組み合わせるテクニックをご紹介します。
この方法を使えば、「〇〇支店の△△月の売上」や「サイズLでカラーが青の商品の価格」のように、行と列の2つの条件が交差する場所のデータをピンポイントで抽出できます。VLOOKUPでは決して真似できない、このパワフルなテクニックを身につけ、Excelマスターへの道を駆け上がりましょう。
今回のゴール：指定した券種と年齢区分の両方に一致する料金を調べる
以下の「スキーリフト代金表」から、B2セルで指定した**券種（行）と、B3セルで指定した年齢区分（列）**の両方の条件を満たす料金を、B4セルに表示させることが目標です。
解決策：INDEX & MATCH & MATCH
手順：B4 に INDEX と MATCH の式を入れて Enter
券種（B2）と年齢区分（B3）の両方に合う料金を、下の料金表から取り出します。 B4 に次の式を入力して、Enter キーを押します❶。
=INDEX(B8:D10,MATCH(B2,A8:A10,0) ,MATCH(B3,B7:D7,0) ) B8:D10MATCH(B2,A8:A10,0)MATCH(B3,B7:D7,0)青に「値の部分」、オレンジの MATCHに「縦の見出し」、紫の MATCHに「横の見出し」を入れます。例：商品（H1）と月（H2）が交わる売上
=INDEX(B2:M20,MATCH(H1,A2:A20,0) ,MATCH(H2,B1:M1,0) ) - 見出しの範囲は、値の部分と同じ行・同じ列から始めます（縦は 8〜10 行目、横は B〜D 列）。
確認：7,500 は「2日」の行と「小学生」の列が交わる料金
答えは 7,500 になりました❷。式は、次の順に計算しています。
- MATCHで「2日」が券種の何番目か、MATCHで「小学生」が区分の何番目かを調べる
⇒ 2 行目・3 列目 - INDEX で、料金表の 2 行目・3 列目を取り出す
⇒ 7,500
=INDEX(B8:D10,2,3) と同じ意味です。B2・B3 を書き換えるだけで料金が切り替わります。まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
サンプルデータ
以下は、上記画像のExcelデータですので、ダウンロードして練習として使用ください。
以下のサンプルデータをExcelシートにコピーして、操作の準備をしてください。
| A | B | C | D | |
| 1 | スキーリフト代金検索 | |||
| 2 | 券種 | 2日 | ||
| 3 | 年齢区分 | 小学生 | ||
| 4 | 料金 | (ここに数式を入力) | ||
| 5 | ||||
| 6 | スキーリフト代金表 | |||
| 7 | 大人 | 中学生 | 小学生 | |
| 8 | 1日 | 8,500 | 7,000 | 4,700 |
| 9 | 2日 | 13,600 | 10,900 | 7,500 |
| 10 | 3日 | 17,800 | 14,300 | 9,800 |
数式の解説（3ステップで完全理解）
一見すると非常に長く複雑に見えますが、構造は「INDEX(参照範囲, 行番号, 列番号)」という基本形です。行番号と列番号を、それぞれ別のMATCH関数で求めているだけです。
ステップ1：最初のMATCHで「行番号」を探す
まず、INDEX関数の2番目の引数であるMATCH(B2, A8:A10, 0)が動きます。
- 役割: 料金表の**縦（行）**の位置を探します。
- 動作: 「
B2セルの”2日”が、行の見出し範囲A8:A10の中で何番目にあるか？」を調べます。 - 結果: “2日”は範囲の中で2番目なので、数値の「2」を返します。
ステップ2：2番目のMATCHで「列番号」を探す
次に、INDEX関数の3番目の引数であるMATCH(B3, B7:D7, 0)が動きます。
- 役割: 料金表の**横（列）**の位置を探します。
- 動作: 「
B3セルの”小学生”が、列の見出し範囲B7:D7の中で何番目にあるか？」を調べます。 - 結果: “小学生”は範囲の中で3番目なので、数値の「3」を返します。
ステップ3：INDEXが最終的な値を取り出す
最後に、外側のINDEX関数が、2つのMATCH関数が返した結果を使って値を取り出します。
- 数式全体は、
=INDEX(B8:D10, 2, 3)と同じ意味になります。 - 動作: 「料金データ全体の範囲
B8:D10の中から、2行目と3列目が交差する場所の値をください」という命令を実行します。 - 最終結果: 表の2行目・3列目に位置する「7,500」がセルに表示されます。
まとめ
このように、INDEXと2つのMATCH関数を組み合わせることで、縦と横の2つの条件からなるマトリクス表から、目的の値をピンポイントで、かつ動的に抽出することが可能になります。
この方法は、料金表、商品マスタ、配送料金表など、ビジネスのあらゆる場面で応用できる非常に強力なテクニックです。最初は難しく感じるかもしれませんが、ステップごとに分解して考えれば必ず理解できます。ぜひこの最強の検索関数をマスターして、周囲と差がつくExcelスキルを身につけてください。
INDEX＆MATCHで複数の条件を掛け合わせて検索する方法は、こちらの記事で詳しく解説しています。
複数のデータをまとめて取り出すときは、INDEX＋XMATCHとスピルで複数データを一括抽出する方法もあわせてご覧ください。
