VLOOKUPとINDIRECTを組み合わせてできることとは
ExcelのVLOOKUP関数を使用すると、指定範囲から検索値に紐づく値を取得できます。
大量のデータからでも必要なデータを抽出できるため便利ですが、INDIRECT関数との組み合わせでさらに柔軟なデータ検索ができます。
例えば、VLOOKUPとINDIRECTを組み合わせることで、参照範囲を切り替えたり、別シートのデータを動的に抽出したりすることができ、より幅広いデータの取得が可能です。
この記事では、Excelをさらに活用したい方のために、VLOOKUP関数とINDIRECT関数の基本を確認し、組み合わせて使用するパターンや具体例、エラーの回避方法などをわかりやすく解説します。
VLOOKUPとINDIRECTの概要
まず、VLOOKUP関数とINDIRECT関数の構文や役割を確認しておきましょう。
VLOOKUP関数は、指定した範囲内から特定の値を検索し、その値に対応するデータを返すために使います。例えば、商品リストから指定した商品名を検索し、その価格を取得する場合に便利です。
一方、INDIRECT関数は、文字列として指定されたセル参照を動的に変換することができ、セルの位置を柔軟に指定する際に役立ちます。
VLOOKUP関数とINDIRECT関数はExcelだけでなく、Googleスプレッドシートでも使用可能です。
VLOOKUP関数とは
VLOOKUP関数は検索値に設定した値を検索し、その値に対応するデータを別の列から取得します。基本的な構文と引数の意味は、次の通りです。
■VLOOKUPの構文 =VLOOKUP(検索値, 範囲, 列番号, [検索方法])
■検索値 探したい値を指定します。2番目の引数である範囲の中の、1番左端の列にある必要があります。
■範囲 データが含まれているセルの範囲を指定します。範囲は、検索値と取得したい列を含むように設定します。
■列番号 範囲内で取得したいデータが存在する列の番号を指定します。範囲の1番左列を1として、右へ数えて何列目であるかを指定します。
■検索方法 検索値と完全に一致するデータを探す場合（完全一致）は「FALSE」または「0」を、近似値でも良い場合（近似一致）は「TRUE」または「1」を指定します。
意図しない値を取得することを防ぐため、近似一致にする理由がなければ「FALSE」（完全一致）を指定しましょう。
VLOOKUPを使えば、商品リストから特定の商品の価格を簡単に取得することができます。
例えば、担当者名がA列の「担当者名」に入力され、その月間売上がB列にある場合、「=VLOOKUP(“Bさん”,A3:B5,2,FALSE)」のように設定すると、「Bさん」の月間売上を取得できます。
検索値は、値を直接設定するだけでなく、値が入っているセルを指定することもできます。例えば、上図の参考のように、「=VLOOKUP(A9,A3:B5,2,FALSE)」の式では、1つ目の引数であるA9セルに入っている値を検索値として値を取得します。
INDIRECT関数とは
INDIRECT関数を使用すると、指定したテキストをセル参照に変換し、そのセルの値を取得することができます。基本的な構文と引数の意味は、次の通りです。
■INDIRECTの構文 =INDIRECT(参照文字列, [参照形式])
■参照文字列 テキスト形式でセル番地を指定します。例えば、A2セルに「A5」、A5セルには「300」が入っている場合、「=INDIRECT(A2)」とすると、「300」が表示されます。
同じ「=INDIRECT(A2)」を記述しても、A2セルに「A6」、A6セルに「150,000」が入っている場合には、「150,000」が表示されます。
■参照形式 「TRUE」を設定すると「A1形式」、「FALSE」を設定すると「R1C1形式」の参照形式になります。省略可能で、省略するとA1形式（TRUE）が指定されます。
INDIRECT関数の特徴は、値によって参照するセルを変えることができる点です。
これにより、別のシートやセルに入力された値を基に、参照先を柔軟に変更できます。例えば、月ごとに異なるシートのデータを参照したり、条件に応じたセルを動的に指定したりできます。
VLOOKUPとINDIRECTの組み合わせのパターン
VLOOKUP関数とINDIRECT関数を組み合わせて参照範囲を動的に切り替える方法、そして複数の別シートを参照する方法の2つのパターンについて解説します。
参照する範囲を切り替える
VLOOKUPとINDIRECTを組み合わせると、同じExcelシート内で複数の範囲に分かれたデータを参照することができます。
例えば、商品データがカテゴリ別で複数の範囲に分かれており、その範囲を動的に指定して検索したい場合を考えます。
文房具は「A12:B15」、食品は「D12:E15」、衣類は「G12:H15」の範囲に、取得したい価格のデータがあるとします。
まず、商品一覧のC列に、その商品が参照すべき範囲が記載されています。例えば、C3セルに参照したい範囲を入力し、D3セルに「=VLOOKUP(A3,INDIRECT(C3),2,FALSE)」を記述します。
この数式では、INDIRECT関数がC3セルに指定された範囲を参照し、その範囲からVLOOKUP関数がA3セルの商品名を基にデータを検索します。
これにより、参照する範囲を行ごとに切り替えて値を取得できます。対象となる範囲やテーブルに名前を定義し、その名前を使って、よりわかりやすく範囲指定することもできます。
複数の別シートを参照する
VLOOKUPとINDIRECTを組み合わせることで、値を取得する別シートを選択できます。INDIRECT関数で動的にシート名を指定し、VLOOKUP関数でそのシートから必要なデータを取得する方法です。
例えば、商品一覧で検索した商品名の価格を取得する時、商品カテゴリごとに、商品価格を参照するシートが分かれているとします。
メインとなる「商品一覧」シートでは、A列に商品名、B列に参照するシート名（カテゴリ）が入力されています。
参照する別シートは、すべてA2:B5が検索範囲、A列に商品名、B列に価格が記入されているとします。この場合、「商品一覧」のC列に「=VLOOKUP(A2,INDIRECT(B2&"!A2:B5"),2,FALSE)」のような数式を入力します。
この数式では、INDIRECT関数が「商品一覧」のB2セルに入力されたシート名に基づいて参照範囲を設定し、その範囲からVLOOKUP関数がA2セルの商品名に基づいてデータを検索します。
これにより、複数のシートから必要な情報を取得することができます。
VLOOKUPとINDIRECTを組み合わせて別シートを参照する具体例
ここでは、VLOOKUPとINDIRECTを組み合わせて複数の別シートを参照する場合の具体的な手順を解説します。シートを用意するところから、順を追って確認していきましょう。
「集計」シートと複数の別シートを用意する
Excelで複数のシートに分かれたデータを効率的に管理するために、「集計」シートを使い、異なる担当エリアシートから、担当者の売上データを動的に参照することを考えてみます。
まず、Excelファイルに「集計」シートを作成しA列には担当者、B列に担当エリアを入力します。次に「東京」「千葉」「神奈川」「埼玉」の4つのシートを用意します。
それぞれのシートには、A列に担当者、B列に売上を入力します。
VLOOKUP関数にINDIRECTを使い、参照するシートを動的に切り替える
次に、「集計」シートで別のシートにあるデータを動的に参照します。
「集計」シートのC列に売上を取得するVLOOKUP関数を設定します。例えばC2セルには「=VLOOKUP(A2,INDIRECT(B2&"!A2:B5"),2,FALSE)」の式を入力します。
この数式では、VLOOKUPでA2セルの担当者に対応するデータをB2セルで指定されたシートから取得し、C2セルに価格を表示します。
これにより、B2セルに入力されたシート名の「東京」や「千葉」に基づいて、INDIRECT関数が参照するシートを動的に設定しています。
こうすることで、参照するシートを動的に切り替えながら、異なるシートにあるデータを取得することができます。
データ行が増えても式をコピーするだけで良い
この方法でデータを取得する場合、参照するべきシート名を各行で持っているため、データ行が増えても、数式をコピーするだけで簡単に対応できます。
例えば、新しい商品データが追加された場合、既に設定したVLOOKUPとINDIRECTの組み合わせをそのまま次の行にコピーすれば、INDIRECT内で参照する行の位置関係を保持してくれるので、数式の内容を修正する必要がありません。
INDIRECTで別シート参照した時のエラー
VLOOKUPとINDIRECTを組み合わせて別シートを参照する際にエラーが発生する場合、主な原因として、指定したシート名と実際のシート名が異なること、シート名に特定の記号が含まれていることが考えられます。
それぞれのケースについて詳しく説明し、エラーを防ぐための対策も紹介します。
指定した値とシート名が異なる
INDIRECT関数を使用して別シートを参照する際にエラーが発生する主な理由の1つに、指定したシート名と実際のシート名が一致しないことが挙げられます。
例えば、INDIRECT関数で「sheet2023」と指定している場合、実際のシート名が「sheet2024」や「sheeet2023」などと文字列が違うと、「#REF!」エラーが表示されます。
下の図では、C2に「=VLOOKUP(A2,INDIRECT(B2&"!A2:B5"),2,FALSE)」を入力していますが、INDIRECTで指定されているB2セルの内容が「sheet2023」、実際のシート名が「sheet2024」で異なるため、エラーが表示されています。
このエラーを防ぐためには、シート名を正確に指定する必要があります。
数式を手動で入力する時に気づかずに誤入力するケースも多く、エラーの原因を時間をかけて調べた結果、文字列が1文字異なっていただけということもあります。
エラーが出る場合、まずはINDIRECTで指定した値とシート名に相違がないかを確認してください。
シート名に特定の記号が含まれている
INDIRECT関数を使用して別シートを参照する際にエラーが発生するもう1つの理由は、シート名に特定の記号が含まれていることです。
シート名に「（」や「）」（カッコ）、「ー」（ハイフン）、「～」（チルダ・波線）、「＆」（アンパサンド）などが含まれている場合、INDIRECT関数では正しく動作しないことがあります。
例えば、下図ではC2セルに「=VLOOKUP(A2,INDIRECT(B2&"!A2:B5"),2,FALSE)」のような数式で、B2セルに参照するシート名が入力されているとします。
このシート名に特定の記号が含まれている場合、INDIRECT関数は正しくシートを参照できず、エラーが発生します。
この問題を解決するためには、シート名をシングルクォーテーション、ダブルクォーテーションで囲む必要があります。
例えば、「=VLOOKUP(A7,INDIRECT("'"&B7&"'!A2:B5"),2,FALSE)」のように記述すると、空白や特殊記号が含まれているシート名でも正しく参照できます。
「INDIRECT("'"&B7&"'!A2:B5")」の記述は少し複雑ですが、「’」（シングルクォーテーション）を、さらに「”」（ダブルクォーテーション）で囲って＆で結合しています。 この方法で指定すれば、シート名に特殊記号が含まれている場合でも、エラーを回避することができます。
VLOOKUPとINDIRECTを組み合わせてデータ管理に役立てよう
ここまで、VLOOKUPとINDIRECTを組み合わせてデータをより柔軟に取得する方法について具体的な例を通じて解説してきました。動的に参照範囲を切り替えたり、複数の別シートからデータを参照したりできることが分かりました。
INDIRECT関数を使用する際の注意点として、シート名の指定ミスやシート名に特定の記号が含まれている場合にエラーが発生することにも気を付けましょう。
VLOOKUPとINDIRECTの組み合わせを取り入れることで、VLOOKUPだけの時よりもさらに細かく、幅広くデータを取得できます。ぜひ日々のデータ管理に取り入れて、業務効率の向上を目指してください。
その他VLOOKUP関連記事
その他、VLOOKUPに関連する内容は、下記の記事でもご紹介しています。ぜひ参考にご覧ください。
VLOOKUP関数で複数条件を指定する？簡単にできる方法を図解
VLOOKUPで別シートを参照するには？記述方法や実践例を解説
VLOOKUP関数でエラーが出た？#REF!エラーなどの解決法を詳しく解説
VLOOKUP関数がうまく反映されない？その原因と対策を図解
VLOOKUPとIFの組み合わせで何ができる？便利な使い方を解説
VLOOKUPで部分一致検索するには？ワイルドカード使用法を解説
VLOOKUPとXLOOKUPの違いとは？使い方を図解で解説
VLOOKUPで0を表示しない方法とは？発生原因と対処法を解説
マイナビエージェントに無料登録して
転職サポートを受ける
編集部オススメコンテンツ
アンドエンジニアへの取材依頼、情報提供などはこちらから
