複数シート上にある参照リスト VLOOKUPとINDIRECT

VLOOKUP関数で複数の表を切り替えてデータを取り出す

33回の講義で、INDIRECT 関数とVLOOKUP関数で複数の参照先を切り替えて使う方法を紹介しましたが、参照する表は、皆同じシート上でした。
参照する表が複数の別シートの場合は、どうしたらいいのでしょうか?データのあるシートがクラス別になっている場合にVLOOKUP関数でクラスを切り替えながら表引きしたいケースなどです。 実はシートが違っても、考え方は同じでいいのです。
今回は、別シートの場合で、説明しましょう。
(サンプルファイルは、こちらから 2013関数技36回サンプルデータ

生徒名簿の範囲に名前を定義して参照しやすく

下記のような生徒検索シートがあり、3クラス分の生徒名簿が別シート上にあります。生徒名簿がいくつあっても大丈夫ですが、今回は3クラスです。

クラスの名簿は、別シートです。
準備として、それぞれのクラスごとに、範囲を名前定義します。

名前の定義は、数式タブの定義された名前グループの名前の管理から設定します。(他に、名前ボックスからや、選択範囲からも名前ボタンを利用して作成できます。)

名前の管理ダイアログボックスが表示されたら、新規作成ボタンをクリックします。
名簿のクラスごとに名前をつけ、範囲を指定します。これをクラスの数だけ作成します。
名前定義を作成し終わりました。

では、検索シートの氏名を表示したいセルにVLOOKUP関数の式を入力しましょう。

VLOOKUP(検索値, 範囲, 列番号, [検索の型])を
シートに当てはめると、E2セルに入力する式は、
=VLOOKUP(B3,INDIRECT(B2),2)
(検索の型は、省略)

INDIRECT関数は、文字列で間接的にセル範囲を指定できる関数です。
INDIRECT(B2) でB2セルに入力された文字列に対応する範囲という意味になります。
ちなみに、INDIRECT関数を使わずに、セル範囲を指定すると、
Aクラスは、
=VLOOKUP(B3,AClass!B2:C24,2)
Bクラスは、
=VLOOKUP(B3,BClass!B2:C24,2)
Cクラスは、
=VLOOKUP(B3,CClass!B2:C24,2)

となり、式が3種類必要になります。
この3つの式を1つにまとめるのが、
=VLOOKUP(B3,INDIRECT(B2),2)
クラス名を入力するセルB2は、3つのクラス名をリストにしておけば、なおいっそう入力がスピーディになります。

VLOOKUP関数のエラー対策

さて、式はこれでほぼ完成ですが、番号が入力されていないとエラーが表示されてしまいます。

そこで、IF関数を使って、もしB3が空白なら、何も表示しないで、空白出ない時は、VLOOKUP(B3,INDIRECT(B2),2)を実行という式に修正します。
もしB3が空白なら、何も表示しないでの部分、
=IF(B3="","",

つなげると、
=IF(B3="","",VLOOKUP(B3,INDIRECT(B2),2))

VLOOKUP 関数で、複数の表を切り替えてデータを取り出す組み合わせの説明は以上です。お疲れ様でした。

スポンサーリンク
スポンサーリンク