INDEX関数は複数のセル範囲を抽出範囲として指定できるが、別のシートにあるクロス表は指定できない。シートごとに作成したクロス表から検索抽出するなら、シート名を使い、行見出しを検索値として列見出しの位置をMATCH関数で求めて交差するデータを抽出しよう。
VLOOKUP の基本（書き方・別シート・#N/A を出さない方法・一致しない原因）は、エクセルのVLOOKUPの使い方にまとめています。
目的
複数クロス表検索抽出
使用する関数
目次
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
例題１｜複数のクロス表から検索抽出する
手順：E3 に式を入れて Enter
分類（B 列）に書いたシートの料金表から、区分（C 列）と種別（D 列）に合う金額を取り出します。料金表は「平日」「休日」の 2 つのシートにあります。 E3 に次の式を入力して、Enter キーを押します❶。
E3 に入れる式
=VLOOKUP(C3,INDIRECT(B3&"!A3:C4"),MATCH(D3,INDIRECT(B3&"!A2:C2"),0) ,0) C3 検索値（区分）
INDIRECT(B3&"!A3:C4") 範囲（B3 のシートの表）MATCH(D3,INDIRECT(B3&"!A2:C2"),0) 列番号（種別は何列目）0 検索方法（完全一致）ひとことでB3 に書いたシート（平日）の料金表から、区分「大人」の行と種別「一般」の列が交わる金額を取り出す
式を分けて読むと（色は画像の枠と同じ）
検索値
C3何を探す？
→ 区分「大人」
範囲
INDIRECT(B3&"!A3:C4")どの表で探す？
→ B3 のシート（平日）の A3:C4
列番号
MATCH(D3,INDIRECT(B3&"!A2:C2"),0)何列目？
→ 種別「一般」は見出しの 2 列目
検索方法
0どう探す？
→ 完全に同じものだけ（FALSE と同じ）
自分の表で使うときは
料金表をシートごとに同じ形で作り、シート名（平日・休日）を B 列 に書きます。INDIRECT が「シート名!範囲」の文字を範囲に変えます。例：B2 に書いたシートの A2:D20 から、C2 を探して 3 列目
料金表をシートごとに同じ形で作り、シート名（平日・休日）を B 列 に書きます。INDIRECT が「シート名!範囲」の文字を範囲に変えます。例：B2 に書いたシートの A2:D20 から、C2 を探して 3 列目
=VLOOKUP(C2,INDIRECT(B2&"!A2:D20"),3,0) - B 列の文字とシート名が 1 文字でも違うと #REF! になります。
確認：1,100 は「平日」シートの 大人×一般 の金額
答えは 1,100 になりました❷。式は、次の順に計算しています。
- INDIRECT(B3&”!A3:C4″)で、「平日!A3:C4」という文字を範囲に変える
⇒ 「平日」シートの料金表 - MATCHで、「一般」が見出し（A2:C2）の何列目かを調べる
⇒ 2 列目 - 「大人」の行の 2 列目を取り出す
⇒ 1,100
ヒント：E4 は「休日」シートから
E4 の式は B4 が「休日」なので、休日シートの料金表から 1,300 を取り出します。数式の解説
「=VLOOKUP(C3,INDIRECT(B3&“!A3:C4”),MATCH(D3,INDIRECT(B3&“!A2:C2”),0),0)」の数式は、C3セルの「大人」をB3セルの「平日」シートのA3セル～C4セルから検索し、一致した行にあるD3セルの「一般」がある2列目から「金額」を抽出する。
Excelデータダウンロード
練習用Excelファイル（無料ダウンロード）
この記事の解説で使うExcelデータです。数式と練習データが入っているので、本文の式を同じ表で試せます。
Excelファイル（.xlsx）／登録不要／数式・練習データ入り
コメント