複数の参照表を切り替えながら「表引き」をする

エクセルの関数技2

複数の参照表を自由に切り替えて「表引き」をする技

クラスごとの成績表や営業部所ごとの売上表など、参照しなければならないリストが複数ある場合って結構あります。
他にも、大人と子供で料金が違うものや、シニアは別料金とか、複数のリストを参照するケースは多いものです。
このような時に、複数の参照表を自由に切り替えて「表引き」をすることがでれきれば便利ですが、それには、VLOOKUP関数とINDIRECT関数を組み合わせて使います。
(サンプルファイルは、こちらから 関数技34回サンプルデータ

VLOOKUPとINDIRECTで複数の参照表を切り替える

運賃早分かり表です。行き先を入れて運賃を表示できるようにします。
INDIRECT関数の書式

=INDIRECT(参照文字列,参照形式)
INDIRECT関数
■文字列として入力したセル番地や範囲名を、計算式で参照できる形に変換する。参照先を間接的に指定し、切り替えられるようにできます。
INDIRECT関数の引数の参照文字列は、参照したいセルが文字列で入力されているセルを指定します。

VLOOKUP関数の書式
=VLOOKUP (検索値,範囲,列位置,[検索の型])
VLOOKUP関数
■範囲の左端の列で値を検索し、値の見つかった行の、列位置で指定した列にあるセルの内容を返します。

大人と子供の料金表を切り替えて表引き 

 行き先を入れて運賃を表示するだけなら、VLOOKUP関数を使えばよいのですが、大人と子供の料金表を切り替えて表引きしたいので、引数にINDIRECT関数を使います。
(1)2つの料金表にそれぞれ「大人」「子供」と範囲に名前を付けておきます。
範囲に名前をつける
名前を設定したい範囲を選択後、
数式バーの左隣にある「名前ボックス」をクリックすると
文字カーソルが入り、入力できるようになります。
子供の運賃表も同様に、 範囲を選択後、
数式バーの左隣にある「名前ボックス」をクリックして、入力可能状態にして、「子供」と入力します。
これで、それぞれの範囲、 A2:B7 には、大人  A9:B13 には 子供 という名前がつきました。

セルF2にVLOOKUPの式をいれます。
(2)具体的には、=VLOOKUP (検索値,範囲,列位置,[検索の型]) なので、
  検索値は D2セル
  範囲は、E2セルの文字列をINDIRECTで参照、
  列は料金なので、2 (列)
  検索の型は、検索値に一致する値のみを検索したいので、FALSEとします。  
VLOOKUP(D2,INDIRECT(E2),2,FALSE)
何も入力していないとエラーになります。
行き先や、大人と子供の切り替えは、ドロップダウンリストにしています。
今日の講義は以上です。お疲れ様でした。