XLOOKUPで別シートを検索する場合は、検索範囲と戻り範囲にシート名を付けます。複数シートをまとめて探したいときはVSTACK、セルに指定した1枚のシートを探したいときはINDIRECTを組み合わせます。目的に合う方法を選びましょう。
別シート・複数シート検索の早見表｜どの方法を選ぶか
| 目的 | 使う方法 | 対応の目安 |
|---|---|---|
| 決まった1枚から検索 | XLOOKUPで別シートを直接参照 | Microsoft 365・2024・2021 |
| A・B両シートをまとめて検索 | XLOOKUP＋VSTACK | Microsoft 365・2024 |
| 入力したシート名へ切り替え | XLOOKUP＋INDIRECT | Microsoft 365・2024・2021 |
| XLOOKUPが使えない | VLOOKUP、または表を統合して検索 | Excel 2019・2016など |
サンプルはAシートとBシートに「会員ID・氏名・電話番号」があります。検索用シートのA3に会員IDを入れ、B3へ電話番号を表示する例で説明します。
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
XLOOKUPで別シートを参照して検索する（シートをまたぐ書き方）
AシートのA2:A3でIDを検索し、同じ行のC列の電話番号を返します。A3がA002なら「080-****-0002」です。数式を入力するときに、検索範囲・戻り範囲を別シート上で選択しても参照を作れます。
複数シートをVSTACKでまとめて検索する
Aシートに2件、Bシートに1件ある場合、ID列どうしと電話番号列どうしを同じ順番で結合します。
VLOOKUPで書く場合
シート名をセルで選んで検索する
検索用シートのE2に「A」または「B」を入力し、その1枚だけを検索する例です。A3は検索したいID、各シートのA列はID、C列は電話番号とします。
VSTACKが使えないExcelではどうする？
Excel 2021では、2枚のシートを順にXLOOKUPで調べられます。AシートになければBシートを探す式です。
VLOOKUPで別シートを検索する書き方（XLOOKUPが使えないExcel）
Excel 2019・2016などXLOOKUPが無いExcelでは、VLOOKUPで別シートを検索できます。範囲の前にシート名と「!」を付けるだけで、考え方はXLOOKUPと同じです。
VLOOKUPの基本はVLOOKUPで別シートからデータを検索する方法、VLOOKUPとほかの検索関数の違いはVLOOKUP関数の使い方まとめで解説しています。
別シート検索で「該当なし」やエラーになるときの確認表
| 症状 | 原因と確認点 |
|---|---|
| 同じIDの別のデータが出る | 通常は最初の一致。A→Bの結合順とID重複を確認 |
| #REF! | INDIRECTのシート名や範囲が存在するか確認 |
| #NAME? | XLOOKUP・VSTACKに対応するExcelか確認 |
| IDがあるのに該当なし | 数値と文字列、前後スペース、検索範囲を確認 |
次のステップ：データ追加に合わせて検索範囲を広げる
次のステップ：条件に一致した全行を抽出する
仕様の確認：Microsoft公式・XLOOKUP関数 ／ Microsoft公式・VSTACK関数 ／ Microsoft公式・INDIRECT関数
コメント