ExcelのVLOOKUP関数とINDIRECT関数を組み合わせる方法

この記事では、ExcelのVLOOKUP関数とINDIRECT関数を組み合わせる方法をご紹介します。

VLOOKUP関数の引数の範囲にINDIRECT関数を組み合わせることで、参照する範囲を変えることができたり、別シートの参照が簡単になります。

以下では、INDIRECTを使う利点や、活用方法をご紹介しています。

INDIRECT関数は、Excelが文字列として判断してしまうテキストなどをセル参照に変換してくれます。

例えば複数シートのデータを1枚にまとめる際、「東京」シートを参照してB3セルの値を「集計」シートのB3セルに表示したい場合、INDIRECT関数を使わずに「=B2&"!B3"」と入力してみます。

=（イコール）でも参照することができそうですが、Excelが「!B3」を文字列と認識してしまうので、B3セルに「東京!B3」と表示されてしまいました。

上記のような場合に活躍するのがINDIRECT関数です。

以下では、VLOOKUP関数とINDIRECT関数を組み合わせた便利な使い方をご紹介します。

上記の画像の例では、商品群（例：果物）と品名（例：みかん）から、VLOOKUP関数で産地（例：愛媛）を抽出しています。

INDIRECT関数を組み合わせると、商品群が「果物」の場合はA6セルからB8セル、「野菜」の場合はA12セルからB14セルのように、カテゴリ別に検索する範囲を変えることができます。

例では商品群が「果物」なので、A6セルからB8セルを検索します。

カテゴリ別に参照範囲を変える方法は、以下の記事「VLOOKUP関数との組み合わせ」セクションでご紹介しています。

別シートのあるカテゴリ別のデータをINDIRECT関数で参照して、VLOOKUP関数に組み合わせる方法をご紹介します。

上記の画像は、VLOOKUP関数の検索範囲をINDIRECT関数でセル参照で指定した例です。

カテゴリ別にシートを管理している場合、シート名の入力やシートを移動して範囲選択をするのは手間がかかりますが、INDIRECT関数を組み合わせるとデータが増えても計算式をコピーするだけで済みます。

上記の画像のようなカテゴリ別に入力されたシートを用意します。

データを集計するシート（例：料金）を用意します。

C7セルには小計を計算する「=SUM(C3:C6)」、C8セルには消費税を計算する「=C7*0.1」、C9セルには合計を計算する「=C7+C8」がそれぞれ入力されています。

検索値に指定する「No.」をA列に入力します。

B列にカテゴリを入力していきます。

この際、集計に使うシート（例：料金）に入力したカテゴリ（例：ルームタイプ）と参照するシート名（例：ルームタイプ）は必ず同じにする必要があります。

また、複数のデータを取り出す際は、カテゴリを手入力すると時間がかかるので以下の数式を入力して自動でカテゴリを表示させます。

B3セルに「=IFS(LEFT(A3)="R", "ルームタイプ", LEFT(A3)="P", "プラン", LEFT(A3)="O", "オプション")」を入力して、Enterを押します。

上記の数式は、もしA3セルの先頭文字が「R」であれば「ルームタイプ」を返し、「P」であれば「プラン」を返し、「O」であれば「オプションを返します。

IFS関数は、指定した1つまたは複数の条件を満たすかどうかを判定します。書式は「=IFS(論理式1,値が真の場合1,[論理式2,値が真の場合2],…)」

IFS関数の詳細は、以下の記事でご紹介しています。

LEFT関数は、文字列の左端（先頭）から指定した文字数を取り出します。書式は「=LEFT(文字列,[文字数])」です。

LEFT関数の詳細は、以下の記事でご紹介しています。

A3セルの先頭の文字は「R」なので、B3セルに「ルームタイプ」が表示されました。

B3セルのフィルハンドルをドラッグして数式をコピーします。

B列にカテゴリが表示されました。

C3セルに「=VLOOKUP(A3,INDIRECT(B3&"!$A$2:$C$10",TRUE),3,FALSE)」を入力して、Enterを押します。

上記の数式のINDIRECT関数でINDIRECT(B3&"!$A$2:$C$10")とすることで、B3セルの値をシート名として指定できます。

つまり、別シートを参照する際の「=ルームタイプ!$A$2:$C$10」と同じ意味になるので、シートを移動して範囲選択する必要がなくなります。

ただし、$A$2:$C$10はどのシートを参照しても変わらないので、すべてのデータが含むようにセル範囲を指定してください。

INDIRECT関数は参照したいセル番地やセルの範囲を文字列として指定できます。書式は「=INDIRECT(参照文字列,[参照形式])」です。

C3セルに「No.」R-03と「カテゴリ」ルームタイプから、「金額」￥10,000を表示することができました。

C3セルのフィルハンドルをドラッグして数式をコピーします。

C列にすべての金額が表示されました。

VLOOKUP関数で別シートを参照する際は、INDIRECT関数を組み合わせることで混乱しやすい別シートの参照を簡単に指定することができます。