g729｜【Excel神業】INDEX関数の最終形態！複数テーブルを瞬時に切り替える究極の検索テクニック

INDEXとMATCHを組み合わせた行列検索は非常に強力ですが、「もし検索対象のテーブルが複数あったら？」——例えば、「日中料金」と「夜間料金」のように、構造は同じでも内容が異なる複数の表から、条件に応じてデータを引きたい場合、どうすればよいでしょうか。

IF関数で切り替える方法もありますが、もっとスマートで拡張性の高い解決策があります。それは、INDEX関数の隠れた引数「領域番号」を使う方法です。今回は、この神業とも言えるテクニックをマスターし、Excelスキルを別次元へと引き上げましょう。

今回のゴール：指定した表No.から、券種と年齢区分に一致する料金を検索する

「表1（日中）」と「表2（夜間）」という2つの料金表があります。M2セルで表No.を切り替えることで、指定した表から、M3セルの券種とM4セルの年齢区分に一致する料金をM5セルに表示させることが目標です。

サンプルデータ

以下は、上記画像のExcelデータですので、ダウンロードして練習として使用ください。

Excel-g729-1.xlsx  （ダウンロード）

このテクニックを試すために、以下のサンプルデータをExcelシートにコピーしてください。

料金表

	A	B	C	D	E	F	G	H	I
1	表1	リフト代金（日中）				表2	リフト代金（夜間）
2		大人	中人	小人			大人	中人	小人
3	1日	8,500	7,000	4,700		1日	8,800	7,200	4,800
4	2日	13,600	10,900	7,500		2日	13,900	11,100	7,600
5	3日	17,800	14,300	9,800		3日	18,100	14,500	9,900

検索エリア

	L	M
1	リフト代金検索
2	表No	2
3	券種	2日
4	年齢区分	大人
5	料金	(ここに数式を入力)
6
7		表No
8		1
9		2

解決策：INDEX関数の「領域番号」を活用する

セルM5に、以下の数式を入力します。

Excel

=INDEX((B3:D5,G3:I5), MATCH(M3,A3:A5,0), MATCH(M4,B2:D2,0), M2)

この数式により、M2セルで指定された「表2」から、「2日」「大人」に該当する「13,900」が正しく抽出されます。

数式の解説（4つの引数を分解）

この数式の最大のポイントは、INDEX関数の最初の引数と4番目の引数です。

INDEX(参照,行番号,列番号,領域番号)

1. 参照：(B3:D5,G3:I5)

• 超重要ポイント: 検索対象となる複数の範囲（表1のデータ範囲B3:D5 と 表2のデータ範囲G3:I5）をカンマ（,）で区切り、全体をもう一つのカッコ()で囲みます。
• このように記述することで、INDEX関数はこれらの範囲を別々の「領域」として認識します。B3:D5が領域1、G3:I5が領域2となります。

2. 行番号：MATCH(M3,A3:A5,0)

• これは通常の行列検索と同じです。「M3セル（2日）」が、行の見出しA3:A5の2番目にあることを見つけ、「2」を返します。

3. 列番号：MATCH(M4,B2:D2,0)

• 同様に、「M4セル（大人）」が、列の見出しB2:D2の1番目にあることを見つけ、「1」を返します。

4. 領域番号：M2

• 超重要ポイント: 参照に複数の領域を指定した場合にのみ有効になる、4番目の引数です。
• ここでM2セル（値は「2」）を指定することで、「領域2（つまり表2のG3:I5）を検索対象にしなさい」とINDEX関数に命令しています。もしM2セルが「1」なら、領域1（表1）が検索対象になります。

まとめ

この数式は、以下のステップで動作します。

1. MATCH関数が行番号「2」と列番号「1」を特定する。
2. INDEX関数が4番目の引数M2（=2）を見て、検索対象を2番目の領域（夜間料金表）に決定する。
3. 最終的に、夜間料金表の「2行目、1列目」にある「13,900」を返す。

複数のテーブルを扱う際、IF関数を何重にもネストさせると数式が複雑化し、管理が大変になります。しかし、INDEX関数の領域番号を使えば、数式は非常にスマートなまま、参照する表を3つ、4つと簡単に追加できます。

これはまさにExcel関数の真髄とも言えるテクニックです。ぜひこの方法をマスターして、誰からも一目置かれるExcelの達人を目指してください。