複数シートを横断して1件を検索するには、VSTACKで各シートの検索列と戻り列を同じ順番につなぎ、XLOOKUPに渡します。Microsoft 365・Excel 2024向けです。Excel 2021ではVSTACKを使わない入れ子の方法を選びます。
無料Excelサンプルで一緒に試す
「検索」「A」「B」の3シート入り練習ファイルです。検索シートのA3をA002・B001・A003に切り替え、B3のVSTACK式とB6の入れ子式を比較できます。
Microsoft 365・Excel 2024：VSTACK版。Excel 2021：本文の入れ子式。
VSTACKで検索範囲をつなぐ
説明用に、AシートとBシートのA2:A4がID、C2:C4が電話番号、検索シートのA3が検索IDという配置を考えます。
=XLOOKUP(A3,VSTACK('A'!A2:A4,'B'!A2:A4),VSTACK('A'!C2:C4,'B'!C2:C4),"該当なし",0)検索列をA→Bの順でつないだら、戻り列もA→Bにそろえます。片方だけ順番を逆にすると、IDと電話番号が別の行の組み合わせになります。配布ファイルでは各シートに用意された範囲の式をそのまま試してください。
同じIDが複数シートにある場合
XLOOKUPは通常、つないだ範囲で最初に一致した1件を返します。Aシートを先につないでいれば、重複IDがあるとAシート側が優先されます。全明細が必要な場合はFILTERを使うか、IDに拠点などの条件を追加します。
末尾から探す検索モード-1にしても、日付が最新の行を自動判定するわけではありません。
IDと氏名の2条件で検索する
説明例でB列が氏名、検索シートB3が検索氏名の場合です。結果の数式はB3以外の空きセルに入力します。
=XLOOKUP(1,(VSTACK('A'!A2:A4,'B'!A2:A4)=A3)*(VSTACK('A'!B2:B4,'B'!B2:B4)=B3),VSTACK('A'!C2:C4,'B'!C2:C4),"該当なし",0)IDと氏名の両方が一致した行だけ1になります。この追加例は、配布ファイルのID検索を理解してから別の空き領域で試してください。
Excel 2021では入れ子にする
=XLOOKUP(A3,'A'!A2:A4,'A'!C2:C4,XLOOKUP(A3,'B'!A2:A4,'B'!C2:C4,"該当なし",0),0)Aシートで見つからなかった場合にBシートの結果を使います。見つかった行の電話番号が空白である場合は「IDがない」とは異なります。空白と0の扱いはXLOOKUPで空白が0になるときの対処を確認してください。
シート数が増える場合の設計
3枚目はVSTACKの引数へ追加できますが、毎月シートを増やす運用では参照漏れが起こりやすくなります。実務ではシートごとに列順と見出しを統一し、Power Queryで結合した一覧から検索する方法も選択肢です。列全体を大量につなぐより、必要なデータ範囲に絞ります。
エラーが出たら確認すること
- #NAME?：VSTACKに対応する版か確認する。Excel 2021では入れ子式を使用。
- #VALUE!：検索列と戻り列の行数・順番を合わせる。
- 該当なし：IDの前後の空白、文字列と数値の違いを確認する。
検索方法全体の比較は別シート検索の使い分けをご覧ください。
無料Excelサンプルで復習する
「検索」「A」「B」の3シート入り練習ファイルです。検索シートのA3をA002・B001・A003に切り替え、B3のVSTACK式とB6の入れ子式を比較できます。
Microsoft 365・Excel 2024：VSTACK版。Excel 2021：本文の入れ子式。
コメント