スポンサーリンク

VLOOKUPの検索範囲を間接的に指定する(セル参照させる)

関数の応用

VLOOKUPの検索範囲をセル参照させる

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

例:=VLOOKUP(F6,A:B,2,FALSE)

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

ですが、式に直接検索範囲を書き込むと、違う範囲を検索したい時に、書き直す必要がでてきます。

そこで思いつくのは、検索範囲を別のセルに用意しておき、それを参照させる方法が思い浮かぶでしょう。

ですが、VLOOKUPの検索範囲は、セル参照を許してくれず、エラーとなってしまいます。

 

VLOOKUPの検索範囲はセル参照できないのか

VLOOKUPの検索範囲に、他セルの値を参照させると、たしかにエラーとなってしまいます。

そしてエラーになってしまった時点で、あぁ、無理なんだなと諦めてしまう人も少なくないでしょう。

ですが、INDIRECT関数を挟むことで、それを可能にできるのです。

例:=VLOOKUP(F6,INDIRECT(G2),2,FALSE)

 

実際にやってみる

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

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

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

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

=VLOOKUP(B4,E:F,2,FALSE)

このように、最終的にはエラーになってしまうような式になっていますが、INDIRECTを挟むことでエラーを回避することができます。

 

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

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

ありそうな例

このVLOOKUPとINDIRECTの組み合わせが本領発揮するのは、検索範囲に他シートを指定したい場合だと思っています。

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

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

今回は、各シートのレイアウトは同じものとして、検索範囲の列の部分はA:Bで固定します。

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

=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を使う事をオススメします。

 

エラーの実験

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

まずは、シンプルにセル参照した場合、#N/Aとなります。

 

次に、セル参照したうえに&で文字列を接続しようとすると、#VALUE!となります。

 

 

コメント

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