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

エクセル初心者

VLOOKUP関数とINDIRECT関数を同時に使っている数式を見たことあるけど、どういう時に使うのかな？

参照先を変えてデータを検索したいときにVLOOKUP関数とINDIRECT関数を使用するよ。とても便利な組み合わせだから、これから詳しく解説するね。

Dr.オフィス

今回は、VLOOKUP関数とINDIRECT関数を使って参照先を変える方法を解説します。

参照先を変える方法といっても、ピンときませんよね。

では、VLOOKUP関数を使って商品を検索をする場合、[範囲]を指定しますが、この[範囲]に指定する参照先が、食品一覧、飲料一覧、日用品一覧、というようにカテゴリー別に作成された複数の一覧だったら、どうしますか。

おそらく今までは、食品一覧、飲料一覧、日用品一覧が１つにまとまった一覧をVLOOKUP関数の[範囲]に指定していたと思います。

しかし今回は、カテゴリー別に作成された複数の一覧を同時に参照先に指定しなければ商品が検索できません。

このようなときに、VLOOKUP関数とINDIRECT関数を使うと、参照先を変えてデータを検索することができます。

以上の３ステップで参照先を変えることができます。

他にも関数を組み合わせてデータを抽出するクロス抽出があります。

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

関連

【エクセルのクロス抽出】INDEX関数とMATCH関数の組み合わせ

次項より、VLOOKUP関数について詳しく解説するので、ぜひ読み進めてください。

※本記事は『OS：Windows10』画像は『Excelのバージョン：2016』を使用しています。

VLOOKUP関数とは

エクセルでデータの検索に欠かせない関数が、VLOOKUP関数ですよね。
ここで、少しVLOOKUP関数を思い出してみましょう。

↑VLOOKUP関数の書式は、[=VLOOKUP（検索値,範囲,列番号,[検索の型]]になります。

引数[検索値]をもとに指定されたセル範囲の1列目で特定の値を検索し、その行の引数に[列番号]指定した列にあるセルの値を返します。

これがVLOOKUP関数でしたね。

ちょっと自信が無いという場合は、エクセル【VLOOKUP関数】で別シートから簡単に条件にあう値を参照しよう！で詳しく解説しているので、参考にしてみてください。

次項より、INDIRECT関数について詳しく解説するので、ぜひ読み進めてください。

INDIRECT関数とは

INDIRECT関数とは、セルに入力されている文字列を介して、間接的にセルを指定する関数です。

それでは、実際にINDIRECT関数を使って名前検索をしてみましょう。

↑下記の順に設定します。

1. セルB3に[E7]を入力
2. セルC3に[=INDIRECT(B3)]を入力

↑セルC3に『小野　弥生』が表示されました。

どのような動きをしているかというと、INDIRECT関数によってセルB3に入力された値をセル番号として認識し、そのセル番号に入力されている内容をセルC3に返したことになります。

では、ここでINDIRECT関数に指定している『セルB3の値』を削除するとどうなるか、確認してみましょう。

↑セルC3の数式は、[=INDIRECT(B3)]と同じですが、セルB3にセル番号と認識できる値が入力されていないためエラーとなりました。

次に、セルC3の数式を、[=INDIRECT(“E7”)]に変更してみましょう。E7をダブルクォーテーションで囲んでいることに注目してくださいね。

↑[=INDIRECT(“E7”)]と入力したため、セルC3に『小野　弥生』が返されました。

このように、[参照文字列]にセル参照を表す文字列を指定する(セル番号やセルの名前を直接入力する）場合は、『”』(ダブルクォーテーショ)で囲んで指定します。

他にも文字列を結合し、シートを指定することもできます。
INDIRECT関数を使って以下の『会議室A』『会議室B』『会議室C』という3種類のシートを指定し、それぞれ集計されている利用人数を『集計』シートに集約してみましょう。

↑それぞれの会議室の日付毎の利用人数の合計が、セルB8に集計されています。

↑下記の順に設定します。

1. 『集計』シートのセルC3を選択
2. セルC3に[=INDIRECT(B3&”!B8″)]を入力

↑シート『会議室A』のセルB8の値が表示されました。

セルB3の値とシート名が同一のため、セルを参照し、文字列を結合することで、シート『会議室A』のセルB8を指定することができました。オートフィルでセルC5までコピーしましょう。

↑『会議室A』『会議室B』『会議室C』のセルB8の値を参照し、『集計』シートに集約することができましたね。

このようにINDIRECT関数は、[参照文字列]に文字列を指定でき、その文字列を『文字』ではなく、シート名やセル番号として認識します。

INDIRECT関数は、直接セルを指定せずに間接的にセルの参照をするので、使い方を理解するのには、少し時間がかかるかもしれませんが、使い方次第で作業範囲が広がるので、マスターしておきたい関数です。

[=A1]とセル参照する場合と、数式[=INDIRECT(“A1”)]や[=INDIRECT(A1)]とする場合の違いをしっかり理解しておこう！

Dr.オフィス

次項では、VLOOKUP関数とINDIRECT関数で参照先を変更する方法を解説します。

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

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

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

1. セルA7からB9を選択
2. [数式]を選択
3. [名前の定義]を選択

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

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

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

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

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

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

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

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

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関数を使って、参照先を切り替える方法を解説しましたが、他にもINDEX関数とMATCH関数を使ってクロス抽出する方法があります。

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

関連

【エクセルのクロス抽出】INDEX関数とMATCH関数の組み合わせ