これまでの記事でINDEXとMATCHを組み合わせた検索方法を解説しましたが、今回はその最終形態とも言える、INDEXに2つのMATCHを組み合わせるテクニックをご紹介します。
この方法を使えば、「〇〇支店の△△月の売上」や「サイズLでカラーが青の商品の価格」のように、行と列の2つの条件が交差する場所のデータをピンポイントで抽出できます。VLOOKUPでは決して真似できない、このパワフルなテクニックを身につけ、Excelマスターへの道を駆け上がりましょう。
今回のゴール：指定した券種と年齢区分の両方に一致する料金を調べる
以下の「スキーリフト代金表」から、B2セルで指定した**券種（行）と、B3セルで指定した年齢区分（列）**の両方の条件を満たす料金を、B4セルに表示させることが目標です。
サンプルデータ
以下は、上記画像のExcelデータですので、ダウンロードして練習として使用ください。
Excel-g728-1.xlsx （ダウンロード）
以下のサンプルデータを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 |
解決策：INDEX & MATCH & MATCH
セルB4に、以下の数式を入力します。
Excel
=INDEX(B8:D10, MATCH(B2, A8:A10, 0), MATCH(B3, B7:D7, 0))
この数式を入力すると、券種「2日」と年齢区分「小学生」が交差する「7,500」という料金が正しく表示されます。
数式の解説（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スキルを身につけてください。
コメント