Excel VLOOKUP関数で複数シート(セル範囲)から検索する方法

2020年5月13日

VLOOKUP関数は本来、一つのセル範囲を検索対象に指定して結果を取得する関数です。

そのため通常の使い方では複数シート、複数セル範囲を検索対象として指定出来ません。

ここでは複数シート、複数セル範囲の指定をする応用方法を紹介します。

XLOOKUP関数でも同様の方法で実現可能です。

サンプル表

このように三つの表に検索範囲が分かれた例で手順を記載します。

検索対象シート:マグロ
検索対象シート:マグロ
検索対象シート:サバ
検索対象シート:サバ
検索対象シート:カツオ
検索対象シート:カツオ

手順

検索対象とセルで指定する方法と指定しない方法が存在します。

検索対象を指定する方法

セルでシート(セル範囲)を指定します。

マグロシートで検索している例
マグロシートで検索している例

検索値が同じでもシートが異なると結果がこのように切り替わります。

サバシートで検索している例
サバシートで検索している例

実現にはVLOOKUP関数の範囲指定にINDIRECT関数を使用します。

それぞれセル範囲に名前を付けて、INDIRECT関数の引数にしても実現が可能です。

セルの行や列が変わる場合は特に効果的です。

数式は下記のようになります。

=VLOOKUP(C3,INDIRECT(C2&"!A:B"),2,FALSE)
=XLOOKUP(C3,INDIRECT(C2&"!A:A"),INDIRECT(C2&"!B:B"))
VLOOKUP関数での例
VLOOKUP関数での例
XLOOKUP関数での例
XLOOKUP関数での例

検索対象を指定せず、順番に検索する方法

IFERROR関数を使用し、検索対象の全シートを対象とする

VLOOKUP関数を順番に記述しています。

#N/AエラーしなければVLOOKUP関数の結果を返しますが

結果が見つからないと#N/Aエラーとなるため次のVLOOKUP関数を実行します。

複数の検索対象に存在する検索値を指定した場合、

書いた順番(マグロ→サバ→カツオ)で優先されます。

数式は下記のようになります。

=IFERROR(VLOOKUP(C2,マグロ!A:B,2,FALSE),IFERROR(VLOOKUP(C2,サバ!A:B,2,FALSE),VLOOKUP(C2,カツオ!A:B,2,FALSE)))
=IFERROR(XLOOKUP(C2,マグロ!A:A,マグロ!B:B),IFERROR(XLOOKUP(C2,サバ!A:A,サバ!B:B),XLOOKUP(C2,カツオ!A:A,カツオ!B:B)))
IFERROR関数を使用し、複数のVLOOKUP関数を指定する例
IFERROR関数を使用し、複数のVLOOKUP関数を指定する例
IFERROR関数を使用し、複数のXLOOKUP関数を指定する例
IFERROR関数を使用し、複数のXLOOKUP関数を指定する例

関連記事

XLOOKUP(VLOOKUP)関数の使い方・活用方法の記事一覧

Excel 文字列による柔軟なセル指定を行う(INDIRECT関数)