はじめに
Excelで複数の表からデータを検索する場合、VLOOKUP関数だけでは対応が難しいことがあります。そこで、INDIRECT関数と組み合わせることで、参照する表を動的に切り替え、効率的な検索が可能になります。この記事では、VLOOKUP関数とINDIRECT関数を連携させる方法を、画像例を交えて詳しく解説します。
VLOOKUP の基本（書き方・別シート・#N/A を出さない方法・一致しない原因）は、エクセルのVLOOKUPの使い方にまとめています。
基本の使い方：分類を切り替えて商品名を探す
手順：J4 に VLOOKUP と INDIRECT の式を入れて Enter
果物（A3:C6）と野菜（E3:G5）の表には、それぞれ「果物」「野菜」という名前を付けてあります（名前の付け方は下の「名前の定義」）。J2 の分類と J3 の品番から、商品名を探します。J4 に次の式を入力して、Enter キーを押します❶。
=VLOOKUP(J3,INDIRECT(J2),2,FALSE) J3INDIRECT(J2)2FALSE表ごとに名前を付けておき、INDIRECTの中に「その名前を書いたセル」を入れます。ほかは普通の VLOOKUP と同じです。例：A2 に書いた名前の表から、B2 の品番の 3 列目を出す
=VLOOKUP(B2,INDIRECT(A2),3,FALSE) - J2 の文字と表の名前が 1 文字でも違うと #REF! になります。
確認：かぼちゃは「野菜」の表の品番 102 の商品名
答えは かぼちゃ になりました❷。式は、内側の INDIRECT から順に計算しています。
- INDIRECT(J2)で、J2 の文字「野菜」を、名前「野菜」の範囲に変える
⇒ E3:G5（野菜の表） - 品番 102を野菜の表の左の列から探し、2 列目（商品名）を取り出す
⇒ かぼちゃ
VLOOKUP関数とは？
VLOOKUP関数は、表の左端の列で特定の値を検索し、同じ行の別の列から値を返す関数です。「Vertical Lookup（垂直検索）」の略です。
基本的な構文（復習）
Excel
=VLOOKUP(検索値, 範囲, 列番号, [検索の型])
各引数の意味は以下の通りです。
- 検索値（必須）: 検索する値を指定します。画像例では、セルJ3の「品番」が検索値です。
- 範囲（必須）: 検索対象となるセル範囲を指定します。通常はセル範囲を指定しますが、この例ではINDIRECT関数を使って動的に範囲を指定します。
- 列番号（必須）: 返す値がある列の番号を指定します。「範囲」の左端の列が1、右にいくにつれて2、3と増えていきます。画像例では「2」が指定されており、商品名が返されます。
- 検索の型（省略可能）:
- TRUE（または1）：近似一致。検索値が見つからない場合、検索値以下の最大の値が使用されます。「範囲」の左端の列は昇順に並べ替えられている必要があります。
- FALSE（または0）：完全一致。検索値と完全に一致する値のみが返されます。通常はFALSE（0）を使用します。画像例では「FALSE」が指定されています。
INDIRECT関数とは？
INDIRECT関数は、文字列として指定されたセル参照を、実際のセル参照に変換する関数です。これにより、文字列を元に動的に参照先を変更できます。
基本的な構文
Excel
=INDIRECT(参照文字列, [参照形式])
- 参照文字列（必須）: セル参照を表す文字列を指定します。例：
"A1"、"Sheet2!B3"、"名前"など。画像例では、セルJ2の内容（「果物」または「野菜」）が参照文字列となります。 - 参照形式（省略可能）: 参照文字列の形式を指定します。
TRUE（または省略）でA1形式、FALSEでR1C1形式になります。通常はTRUE（または省略）を使用します。
「名前」の定義
この例では、事前に参照する表に「名前」を定義しています。これは、INDIRECT関数で表を参照するために不可欠です。
「名前」の定義方法（画像を参照）
画像では、セル範囲A3:C6に「果物」、セル範囲E3:G5に「野菜」という名前が付けられています。これは、範囲を選択した状態で、数式バーの左にある名前ボックスに直接名前を入力するか、「数式」タブの「名前の定義」から行うことができます。
＜Excelサンプルデータダウンロード＞
VLOOKUPとINDIRECTの連携：複数テーブルの切り替え（画像を参照）
画像例の具体的な解説
画像では、セルJ2に「果物」または「野菜」と入力することで、参照する表を切り替えています。セルJ4には以下の数式が入力されています。
Excel
=VLOOKUP(J3, INDIRECT(J2), 2, FALSE)
この数式を分解して解説します。
J3: 検索値（品番）が入力されています。例：101, 102など。INDIRECT(J2): これがこの数式の核心部分です。J2: ここには「果物」または「野菜」という文字列が入力されています。INDIRECT()関数は、この文字列をセル範囲への参照に変換します。- もしJ2が「野菜」なら、
INDIRECT("野菜")は名前「野菜」で定義された範囲E3:G5を参照します。 - もしJ2が「果物」なら、
INDIRECT("果物")は名前「果物」で定義された範囲A3:C6を参照します。
- もしJ2が「野菜」なら、
2: 列番号です。VLOOKUP関数は、指定された範囲の2列目（商品名）を返します。FALSE: 完全一致検索を指定します。検索値と完全に一致する品番のみが返されます。
つまり、セルJ2の入力に応じて、VLOOKUP関数が参照する表が自動的に切り替わるのです。
Excelのサンプルデータダウンロード
以下は、上記画像のExcelデータですので、ダウンロードして練習などに使用してください。
CHOOSE関数を使った代替案（補足）
画像のMemo部分には、「名前」を使いたくない場合の代替案として、CHOOSE関数を使った方法が紹介されています。
Excel
=VLOOKUP(J3,CHOOSE(IF(J2="果物",1,2),A3:C6,E3:G5),2,FALSE)
この数式は以下の手順で動作します。
IF(J2="果物",1,2): J2の値が「果物」なら1、そうでなければ2を返します。CHOOSE(..., A3:C6, E3:G5): IF関数の結果（1または2）に基づいて、CHOOSE関数がA3:C6（果物の範囲）またはE3:G5（野菜の範囲）を選択します。VLOOKUP(J3, ..., 2, FALSE): 選択された範囲に対してVLOOKUP関数が実行されます。
この方法は「名前」定義が不要ですが、数式がやや長くなります。
VLOOKUPとINDIRECTの連携のメリット
- 柔軟性: 複数の表を簡単に切り替えて検索できます。
- 効率性: 複数のVLOOKUP関数を書く代わりに、1つの数式で済みます。
- 保守性: 表の追加や変更があっても、「名前」の定義を変更するだけで済みます。
VLOOKUPとINDIRECTの連携のデメリット
- 数式の複雑さ: INDIRECT関数を使うため、数式が少し複雑になります。
- 「名前」の管理: 「名前」を多用すると、管理が煩雑になる可能性があります。
まとめ
VLOOKUP関数とINDIRECT関数を組み合わせることで、複数の表を効率的に検索できます。特に、表を切り替えて検索を行いたい場合に非常に有効です。「名前」定義を活用する方法と、CHOOSE関数を使った代替案を理解し、状況に応じて適切な方法を選択しましょう。この解説で、画像の内容がより明確になったかと思います。この情報を活用し、Excelでのデータ分析をより効率的に行いましょう。
VLOOKUP関数と関連する検索関数の使い分けは、こちらのまとめで整理しています。
コメント