VLOOKUPの検索範囲を間接的に指定する

関数の応用

VLOOKUPの検索範囲を固定したくない

VLOOKUPを使う際、探しに行く表を指定する時、基本は直接指定するしかありません。

例:=VLOOKUP(A1,B:C,2,FALSE)

検索範囲というのはこれでいう、B:Cにあたる部分になります。

今回は、検索範囲をVLOOKUP関数に直接入力するのではなく、別のセルに書いたものを参照するようにしていきます。

実際にやってみる

例えば下の図では、検索範囲をC7に用意し、それをVLOOKUP関数内に読み込ませるという事をしています。

なぜそうなるかを、順を追って読み解いていきましょう。

=VLOOKUP(B4,INDIRECT(C7),2,FALSE)

これの中のINDIRECT(C7)が、この場合C7セルに書かれているE:Fに置き変わり、次のような扱いになっています。

=VLOOKUP(B4,E:F,2,FALSE) こうなればもう普通のVLOOKUPと同じですね。

僕は を E:F列のE列から探して 2列目(F列)を表示する。

結果、アドン という文字が表示されています。

ですがこれだけでは、実際にどう活用すればいいのか、いまひとつイメージしにくいでしょう。

これの便利なところは、検索範囲を別シートにするとき、シート名まで可変にできることにあります。

ありそうな例

こういうエクセル表がある時、INDIRECTは本領発揮します。

毎年の売上、仕入をシートごとに管理していて、検索できるようにしたい。

その場合、図のような使い方ができるようになります。

また順を追って読み解いていきましょう。

=VLOOKUP(B3,INDIRECT(C3 & “!A:B”),2,FALSE)

そして今、検索シートのC3には2015と入力されているので・・・

=VLOOKUP(B3,2015!A:B,2,FALSE) と置き変わり、

売上総額 を 2015シートのA:B列のA列から探して 2列目(B列)を表示する となります。

なので、

2億という結果がかえってきます。

INDIRECTを使わないで検索範囲を別シートに切り替えようとすると、VLOOKUP関数の文を直接編集しなくてはいけません。

慣れてる人には何も問題ではないのですが、エクセル操作に慣れてない人に使ってもらえるようにするときなどは、INDIRECTを使う事をオススメします。

この例の場合、関数がはいっているD3セルを保護して、B列を選択式にしてあげれば、ちょっとした検索フォームの完成になります。

エラーの実験

ちなみにですが、INDIRECTを使わないと、このようにエラーがかえってきます。

この時、なんだ、できないのかー と諦めた方は少なくないんじゃないでしょうか。

私もしばらく諦めてまして、初めてINDIRECTに出会った時はつい嬉しくなって、過去に作ったエクセルファイルを一気に手直ししはじめたものです。

 

コメント

タイトルとURLをコピーしました