関数

【関数の組み合わせ】VLOOKUP関数とINDIRECT関数で参照先を変える技!

VLOOKUP関数とINDIRECT関数で参照先を変更しよう

それでは、VLOOKUP関数とINDIRECT関数を使って参照先を変更できるように設定してみましょう。

参照先に名前を定義参照先に名前を定義

INDIRECT関数は、参照先の名前を(文字列)範囲として指定できるため、使用する3つの参照先に下記の順で名前を付けます。

  1. セルA7からB9を選択
  2. [数式]を選択
  3. [名前の定義]を選択
[新しい名前]ダイアログボックスより設定[新しい名前]ダイアログボックスより設定

↑[新しい名前]ダイアログボックスが表示されたら下記の順に設定しましょう。

  1. [名前]-[食料品]を入力
  2. [OK]を選択

同様に残り2つの参照先にも下記の内容で名前をつけます。

[新しい名前]ダイアログボックスより設定[新しい名前]ダイアログボックスより設定

↑セルD7からE9には、『飲料品』と名前を付けます。

[新しい名前]ダイアログボックスより設定[新しい名前]ダイアログボックスより設定

↑セルG7からH9には、『日用品』と名前を付けます。

名前ボックスを確認名前ボックスを確認

↑セルやセル範囲に名前をつけると、画面左上にある名前ボックスに一覧が表示されます。それぞれ名前を選択し、正しく参照範囲が反映されるか確認しておきましょう。

また、名前ボックスに直接名前を入力して、セル範囲に名前を付けることもできます。

VLOOKUP関数とINDIRECT関数を設定VLOOKUP関数とINDIRECT関数を設定

↑参照先に名前を付け終わったら下記の順に設定します。

  1. セルA2に[食料品]を入力
  2. セルB2に[パン]を入力
  3. セルC2を選択
  4. セルC2に[=VLOOKUP(B2,INDIRECT(A2),2,FALSE)]を入力
名前の定義で指定した[食料品]を参照している名前の定義で指定した[食料品]を参照している

↑セルC2に食料品のパンの価格が表示されましたね。

他の飲料品と日用品の品名も正しく検索されるか確認してみましょう。
今度は、セルA2に飲料品、セルB2にコーラを入力します。

名前の定義で指定した[飲料品]を参照している名前の定義で指定した[飲料品]を参照している

↑セルC2に飲料品のコーラの価格が表示されましたね。

最後に、セルA2に日用品、セルB2にとシャンプーを入力します。

名前の定義で指定した[日用品]を参照している名前の定義で指定した[日用品]を参照している

↑セルC2に日用品のシャンプーの価格が表示されましたね。

このように、[検索値]と[範囲]に応じて動的に参照先を変えて、データを検索することができます。

また、エクセルのIFERROR関数を使うと、値が見つからないときやその他エラーが起こる場合は、空白を返してくれます。
セルC2には、[=IFERROR(VLOOKUP(B2,INDIRECT(A2),2,FALSE),””) ]と入力しておくとスマートになりますよ。

VLOOKUP関数とINDIRECT関数を使って、ワンランク上のデータ検索をしよう!

VLOOKUP関数とINDIRECT関数を使うことによって、複数の参照先からデータを検索できるようになりましたね。

[検索値]と[範囲]に応じて参照先を切り替えることができるため、IF関数を使った複雑な条件式を考える必要もありません。また、数式もスマートになり見栄えも良くなります。

参照先が1つでもVLOOKUP関数とINDIRECT関数を使って設定しておくといいですよ。
後日発生するかもしれない、参照先が増えた時などのメンテナンスがしやすくなります。

VLOOKUP関数とINDIRECT関数を組み合せる方法のまとめ
  • 参照先になるセル範囲に名前を付ける
  • VLOOKUP関数とINDIRECT関数を使うと、簡単に参照先を切り替えることができる
  • VLOOKUP関数とINDIRECT関数を使うと、データ検索の数式が簡素化できる

これからは、VLOOKUP関数とINDIRECT関数を使って、ワンランク上のデータ検索を心がけましょう。

今回は、VLOOKUP関数とINDIRECT関数を使って、参照先を切り替える方法を解説しましたが、他にもINDEX関数とMATCH関数を使ってクロス抽出する方法があります。

クロス抽出については、【エクセルのクロス抽出】INDEX関数とMATCH関数の組み合わせで詳しく解説しているので、参考にしてみてください。

タイトル
【エクセルのクロス抽出】INDEX関数とMATCH関数の組み合わせエクセルのクロス抽出について解説します。クロス抽出は、データから行番号と列番号を指定し、重なったセルの値を取り出します。今回は『INDEX関数』と『MATCH関数』を組み合わせてクロス抽出します。クロス抽出で、データの値を素早く取り出すことは仕事効率アップにつながります。ぜひ活用されてみてください。...
1 2 3
ABOUT ME
Dr.オフィス
Dr.オフィス
エクセルの使い方や応用法を日々研究しています。 初心者から上級者までわかりやすく解説していきます。 エクセルについてわからないことや気になることなどございましたら是非、コメントやお問い合わせからご質問ください。
暑中見舞いの締め切りは8/7です!梅雨が明けたら忘れる前に送りましょう。おすすめの暑中見舞い作成サービスをまとめました。
おすすめランキングはコチラ
おすすめの暑中見舞い作成サービスをランキング形式でまとめました。
詳細はコチラ