【Excel・エクセル】INDIRECT関数とVLOOKUP関数で、セル範囲を切り替える

抽出,検索

もりの

VLOOKUP関数を、複数の表を切り替えながら使えないかな?

やまの

INDIRECT関数と組み合わせれば、いいんよ。

今回のポイント
  • VLOOKUP関数とINDIRECT関数を組み合わせると、VLOOKUP関数の範囲を切り替えられる
  • 前準備として、データベースに名前を付けておく
  • =VLOOKUP(検索値,INDIRECT(範囲を書くセル),列番号,0)
  • 範囲を書くセルに、データベースにつけた名前を入れることで、範囲を切り替えられる

スポンサーリンク

 VLOOKUP×INDIRECTの練習ファイルダウンロード

もりの
 
 

一緒にやってみませんか?

【Excel・エクセル】VLOOKUP関数とINDIRECT関数のおさらい

VLOOKUP関数は、いわずと知れた検索の関数ですね。

VLOOKUP関数の書き方

=VLOOKUP(検索値,範囲,列番号,検索方法)

検索値この値を基準にして
範囲この範囲の中の
列番号左から何番目にある値を抽出して
検索方法基本は0(FALSE)でOK
VLOOKUP関数の例
VLOOKUP関数の例

INDIRECT関数は、あまり知られていませんが、知ってると結構便利なセルを参照する関数です。

INDIRECT関数の例
INDIRECT関数の例
INDIRECT関数の書き方

=INDIRECT(参照文字列,参照形式)

INDIRECTこのセルの値を参照してね
参照文字列参照したいセル(文字列で指定)
参照形式基本は省略でOK
やまの

このVLOOKUP関数とINDIRECT関数を組み合わせると、複数の表から検索できるようになるんよ。

【Excel・エクセル】VLOOKUP関数とINDIRECT関数を組み合わせて複数セルを切り替える

次の表の種類をお菓子にしたらお菓子の表から、家具にしたら家具の表から、VLOOKUPを使って商品名を検索できるようにしたいと思います。

VLOOKUP関数とINDIRECT関数の練習問題
種類で検索範囲を分岐したい
やまの

=VLOOKUP(B2,INDIRECT(B1),2,0)で、求められるよ。説明するね。

ステップ1

まず前準備として、お菓子と家具の表に名前を付けます

お菓子の表をドラッグして、名前ボックスにお菓子と入力してください。

表に名前を付けた画像
セル範囲に名前を付ける
もりの

セル範囲に名前なんて付けられるんだね。

ステップ2

もう一つの表にも名前をつけます。

今回は家具、という名前にしました。

切り替えたい表が他にもある場合、すべての表に名前を付けてください。

もう一つの表にも名前を付けた画像
もう一つの表にも名前を付ける

ステップ3

これで準備は完了です。

ではまずはVLOOKUP関数から書いていきます。

「=VLOOKUP(」まで入力してください。

「=VLOOKUP(」まで入力した画像
まずはVLOOKUP関数

ステップ4

次に検索値です。

今回はNoから商品名を検索したいので、Noが書かれたセルB2をクリックします。

そして「,(カンマ)」

検索値を決めた画像
検索値を決める

ステップ5

次に範囲ですが、ここでINDIRECT関数を使います。

「INDIRECT(」と入力してください。

範囲にINDIRECT関数を使う画像
範囲にINDIRECT関数を使う

ステップ6

今回は種類からお菓子の表、家具の表を切り替えたいので、種類を書く予定のセルB1をクリックします。

INDIRECT関数はこれで終わりなので、カッコを閉じて「,」です。

INDIRECT関数の参照文字列を入力した画像
種類で表を切り替えたい

ステップ7

次に列番号です。

商品名は表の2列目にあるので2、最後の検索方法は基本は0でOKです。

なので「2,0)」と入力します。

VLOOKUP関数とINDIRECT関数を組み合わせた式
これで式は完成!

完成!

これで完成です!

エンターキーを押せば、答えが……

答えがエラーになった式
エラーなのはB1が空欄だから
もりの

エラーになったけど?

やまの

INDIRECT関数で指定したセルB1が、空欄だからさ。

種類に「お菓子」を入力すると、お菓子の表からNo1の商品名を。

お菓子の表から検索できた画像
お菓子の表から検索できた

種類に「家具」を入力すると、家具 の表からNo1の商品名を取り出せました!

家具の表から検索できた画像
家具の表から検索できた

これで、複数の範囲を切り替えながらVLOOKUP関数で検索ができますね!
それでは、お疲れさまでした!

今回のおさらい
  • VLOOKUP関数とINDIRECT関数を組み合わせると、VLOOKUP関数の範囲を切り替えられる
  • 前準備として、データベースに名前を付けておく
  • =VLOOKUP(検索値,INDIRECT(範囲を書くセル),列番号,0)
  • 範囲を書くセルに、データベースにつけた名前を入れることで、範囲を切り替えられる