Excelでデータを検索する際、「行タイトル」と「列タイトル」を指定して、対応するセルの値を取得すしたいこと、多いと思います。その際に便利なのが、INDEX関数とMATCH関数の組み合わせです。
本記事では、INDEX関数とMATCH関数を使い、行タイトルと列タイトルをもとにデータを取得する方法を詳しく解説します。
最初に結論！
MATCH関数で指定のタイトルがどの行、列にあるかを確認します。その行と列にある数値や文字列をINDEX関数で検索することで取ってくることができます。
例として、画像のような行タイトルを1月、2月…、列のタイトルに予算、実績、誤差としたときの関数式をコピペできるようにしておきます。
=INDEX(A1:D13, MATCH(F3, A1:A13, 0), MATCH(F4, A1:D1, 0))
=INDEX(タイトルを含めた検索範囲, MATCH(検索行タイトル,検索行タイトル範囲, 0), MATCH(検索列タイトル, 検索列タイトル範囲, 0))
使用する関数の解説
まず、それぞれの関数の基本的な役割を確認しましょう。使っている関数を理解することで、応用が利くようになるので、ぜひ覚えていってください。
INDEX関数とは？
指定した行番号・列番号にあるデータを取得する関数です。
=INDEX(範囲, 行番号, 列番号)例：A1:C5の範囲で、2行3列目のデータを取得
=INDEX(A1:C5, 2, 3)MATCH関数とは？
指定した値が範囲内の何番目にあるかを取得する関数です。
=MATCH(検索値, 検索範囲, 検索方法)- 検索方法 は
0（完全一致）を指定するのが一般的。 - 例：A列に「2月」が何行目にあるか探す
=MATCH("2月", A1:A13, 0)INDEXとMATCHを組み合わせる理由
INDEX関数は「行番号・列番号」を指定しないと使えません。しかし、通常のExcel表ではデータの位置が変動することが多いため、MATCH関数を使って行番号・列番号を取得すると、柔軟に対応できます。
具体的な例を見てみましょう。
行タイトルと列タイトルを指定して値を取得する方法
使用するデータ表
例えば、「2月の実績」を取得する場合、行タイトル『2月』と列タイトル『実績』を指定して該当セルの値を取得します。
INDEXとMATCHの組み合わせ
次の数式を使うことで、行タイトルと列タイトルを指定して値を取得できます。
=INDEX(A1:D13, MATCH("2月", A1:A13, 0), MATCH("実績", A1:D1, 0))数式の説明
- MATCH(“2月”, A2:A4, 0) → 「2月」がA1:A13の何行目にあるかを取得（結果：3）
- MATCH(“
実績“, A1:C1, 0) → 「実績」がA1:C1の何列目にあるかを取得（結果：3） - INDEX(A1:D13, 3, 3) → A1:C13の範囲で「3行3列目の値」を取得（結果：「22」）
可変セル参照を使って柔軟な検索をする
固定値ではなく、セルに入力した値を参照する方法を紹介します。
セルに検索条件を入力
この表のB3セルに在庫数を取得する数式を入力します。
=INDEX(A1:D13, MATCH(F3, A1:A13, 0), MATCH(F4, A1:D1, 0))- F3（2月）を参照して行番号を取得
- F4（実績）を参照して列番号を取得
- 該当セルの値をF6に表示
この方法なら、B1やB2の値を変えるだけで、異なる商品の情報を取得可能です！
セルの値をプルダウンのリスト(データの入力規制)で選択可能にすれば、見たいデータのみを抽出することができます。
応用編：エラー処理を追加する
MATCH関数で検索する値が見つからない場合、エラー（#N/A）が発生します。そのため、IFERROR関数を組み合わせると便利です。
=IFERROR(INDEX(A1:D13, MATCH("2月", A1:A13, 0), MATCH("実績", A1:D1, 0)), "該当なし")IFERROR(数式, "該当なし") を追加することで、値が見つからないときに「該当なし」と表示されます。
まとめ
INDEX関数とMATCH関数の基本
INDEX(範囲, 行番号, 列番号)→ 指定したセルの値を取得MATCH(検索値, 検索範囲, 0)→ 検索値の位置を取得
行タイトル・列タイトルを指定して値を取得する方法
=INDEX(データ範囲, MATCH(行タイトル, 行範囲, 0), MATCH(列タイトル, 列範囲, 0))
可変セル参照を活用して柔軟に検索
- セルに商品名や項目名を入力し、数式で自動取得
エラー処理を追加
IFERROR()を使って、「該当なし」と表示
INDEXとMATCHの組み合わせを活用すれば、複雑な表から瞬時に欲しいデータを取得できます。
実務でも役立つテクニックなので、ぜひ試してみてください！
コメント