XLOOKUPで別シート・複数シートから検索する方法｜VSTACK・INDIRECT

XLOOKUPで別シートを検索する場合は、検索範囲と戻り範囲にシート名を付けます。複数シートをまとめて探したいときはVSTACK、セルに指定した1枚のシートを探したいときはINDIRECTを組み合わせます。目的に合う方法を選びましょう。

別シート・複数シートのどちらを検索するか

サンプルはAシートとBシートに「会員ID・氏名・電話番号」があります。検索用シートのA3に会員IDを入れ、B3へ電話番号を表示する例で説明します。

まずはXLOOKUPで別シートから抽出する

=XLOOKUP(A3,'A'!$A$2:$A$3,'A'!$C$2:$C$3,"該当なし")

AシートのA2:A3でIDを検索し、同じ行のC列の電話番号を返します。A3がA002なら「080-****-0002」です。数式を入力するときに、検索範囲・戻り範囲を別シート上で選択しても参照を作れます。

シート名に空白などがあるときは '会員 A'!A2:A100 のように、シート名をシングルクォーテーションで囲みます。検索範囲と戻り範囲は開始行・終了行をそろえてください。

複数シートをVSTACKでまとめて検索する

Aシートに2件、Bシートに1件ある場合、ID列どうしと電話番号列どうしを同じ順番で結合します。

=XLOOKUP(A3,VSTACK('A'!A2:A3,'B'!A2:A2),VSTACK('A'!C2:C3,'B'!C2:C2),"該当なし")

A003など存在しないIDなら「該当なし」、B001ならBシートの電話番号が返ります。見出し行や空白行を大量に含めず、必要なデータ範囲を指定します。

VLOOKUPで書く場合

=VLOOKUP(A3,VSTACK('A'!A2:C3,'B'!A2:C2),3,FALSE)

VSTACKで3列の表を縦に結合し、左端のIDを完全一致で探して3列目を返します。VLOOKUP自体は古いExcelにもありますが、この式はVSTACKを含むためExcel 2021・2019・2016では使えません。

シート名をセルで選んで検索する

検索用シートのE2に「A」または「B」を入力し、その1枚だけを検索する例です。A3は検索したいID、各シートのA列はID、C列は電話番号とします。

=XLOOKUP(A3,INDIRECT("'"&E2&"'!A2:A100"),INDIRECT("'"&E2&"'!C2:C100"),"該当なし")

E2を変更すると参照するシートが切り替わります。これは複数シートを総当たりする式ではありません。シート名にシングルクォーテーションを含む場合は追加のエスケープが必要なので、この例ではA・Bのような名前を使います。

VSTACKが使えないExcelではどうする？

Excel 2021では、2枚のシートを順にXLOOKUPで調べられます。AシートになければBシートを探す式です。

=XLOOKUP(A3,'A'!A2:A3,'A'!C2:C3,XLOOKUP(A3,'B'!A2:A2,'B'!C2:C2,"該当なし"))

対象シートが増える場合は、Power Queryなどで「シート名」列を持つ1つの表へ統合すると管理しやすくなります。Excel 2019・2016では統合した表をVLOOKUPで検索できます。

見つからない・違う結果が出るとき

別ブックをINDIRECTで参照する場合は、参照先ブックを開く必要があります。同じIDの全件を抽出するなら、XLOOKUPの1件検索ではなくFILTERを使います。

次のステップ：データ追加に合わせて検索範囲を広げる

次のステップ：条件に一致した全行を抽出する

仕様の確認：Microsoft公式・XLOOKUP関数 ／ Microsoft公式・VSTACK関数 ／ Microsoft公式・INDIRECT関数