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

2022年9月24日

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

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

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

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

サンプル表

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

検索値に一致するものが複数存在する場合、

VLOOKUP関数XLOOKUP関数の仕様上、

そのまま使用すると最初に一致する値が取得されます。

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

手順

VSTACK関数・HSTACK関数で検索対象を結合する方法

2022年9月にMicrosoft365(Office365)のExcelに追加された新関数を使うので

古いバージョンのExcelでは使用できません。

VSTACK関数・HSTACK関数はセル範囲を結合し、

その結果をVLOOKUP関数XLOOKUP関数に利用することが可能です。

=VSTACK(マグロ!A2:B4,サバ!A2:B5,カツオ!A2:B5)
VSTACK関数の結果
=VLOOKUP(C2,VSTACK(マグロ!A2:B4,サバ!A2:B5,カツオ!A2:B5),2,FALSE)
VLOOKUP関数に利用する例

XLOOKUP関数に利用する場合は検索範囲と戻り範囲を、それぞれ結合する必要があります。

=XLOOKUP(C2, VSTACK(マグロ!A2:A4,サバ!A2:A5,カツオ!A2:A5), VSTACK(マグロ!B2:B4,サバ!B2:B5,カツオ!B2:B5) )
XLOOKUP関数に利用する例

数式で列全体を指定したい場合

検索対象の行数が不定で、列そのものを指定したいケースがありますが、

そのままではExcelの行数の限界を超えてしまいエラーになってしまいます。

この場合、結合前に有効行だけに制限する必要があります。

有効行だけにする数式をそのまま利用しても構いませんが、

名前の管理で数式を登録すると利用しやすくなります。

「新規作成」をクリックします、

名前に任意の関数名、参照範囲に次の数式を指定します。

=LAMBDA(指定範囲,LET(中間範囲,HSTACK(BYROW(指定範囲,LAMBDA(行,COUNTA(行))),指定範囲),MAP(DROP(FILTER(中間範囲,CHOOSECOLS(中間範囲,1)>0),,1),LAMBDA(セル,IF(セル="","",セル)))))

指定の関数名で利用することが可能になります。(例では「有効行」)

=VSTACK(有効行(マグロ!A:B),有効行(サバ!A:B),有効行(カツオ!A:B))

ヘッダー行が存在する場合は、DROPでヘッダー分の行数を削除します。

(検索条件によっては不要)

=VSTACK(DROP(有効行(マグロ!A:B),1),DROP(有効行(サバ!A:B),1),DROP(有効行(カツオ!A:B),1))

このセル範囲は、そのままVLOOKUP関数に利用可能です。

=VLOOKUP(C2,VSTACK(DROP(有効行(マグロ!A:B),1),DROP(有効行(サバ!A:B),1),DROP(有効行(カツオ!A:B),1)),2,FALSE)

XLOOKUP関数にも利用可能ですが、範囲から列を抽出するCHOOSECOLS関数を併用します。

=XLOOKUP(C2,CHOOSECOLS( VSTACK(DROP(有効行(マグロ!A:B),1),DROP(有効行(サバ!A:B),1),DROP(有効行(カツオ!A:B),1)),1),CHOOSECOLS( VSTACK(DROP(有効行(マグロ!A:B),1),DROP(有効行(サバ!A:B),1),DROP(有効行(カツオ!A:B),1)),2))

VLOOKUP関数の方が数式は簡潔ですが、XLOOKUP関数では冗長になります。

XLOOKUP関数で数式を短くする方法
有効行のセル範囲を展開してから指定

まず有効行をどこかに展開にします。(例ではE1)

有効行のみにする数式のあるセルに#を付けると

結合したセル範囲全体になるためそれを利用することも可能です。

=XLOOKUP(C2, CHOOSECOLS(E1#,1), CHOOSECOLS(E1#,2) )
LET関数で名前をつける

LET関数を利用すると有効行をどこにも展開せずに実現可能です。

=LET(対象範囲,
VSTACK(DROP(有効行(マグロ!A:B),1),DROP(有効行(サバ!A:B),1),DROP(有効行(カツオ!A:B),1)),
XLOOKUP(C2,CHOOSECOLS(対象範囲,1),CHOOSECOLS(対象範囲,2))
)

検索対象を指定する方法

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

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

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

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

実現には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関数を指定する例

Excelを効率的に習得したい方へ

当サイトの情報を電子書籍用に読み易く整理したコンテンツを

買い切り950円またはKindle Unlimited (読み放題) で提供中です。

Word-A4サイズ:1,400ページの情報量で

(実際のページ数はデバイスで変わります)

基本的な使い方、関数の解説(140種類)、

頻出テクニックと実用例(109種類)、

XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピルなど便利な新機能を紹介。

体系的に学びたい方は是非ご検討ください。

アップデートなどの更新事項があれば随時反映しています。

なお購入後に最新版をダウンロードするには

Amazonへの問い合わせが必要です。

関連記事

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

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