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シートのＡ：Ｂ列のＡ列から探して ２列目（Ｂ列）を表示する という式になり、２億という結果がかえってきます。
INDIRECTを使わずに検索範囲を別シートに切り替えようとすると、VLOOKUP関数の文を直接編集しなくてはいけません。
慣れてる人には何も問題ではないのですが、エクセル操作に慣れてない人に使ってもらう場合は、式を直接編集するようなことは避けたいので、INDIRECTを使う事をオススメします。
エラーの実験
ちなみにですが、INDIRECTを使わないと、このようにエラーがかえってきます。
まずは、シンプルにセル参照した場合、#N/Aとなります。
次に、セル参照したうえに&で文字列を接続しようとすると、#VALUE!となります。
コメント