1つの商品IDから商品名・分類・単価をまとめて返すには、XLOOKUPの戻り範囲を複数列にします。数式は左端の1セルに入力し、結果が右へ広がる空間を空けます。同じIDに一致する複数行をすべて返す機能とは異なります。
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
戻り範囲を3列指定する
B3にB-101を入力し、C3に次の式を入れます。
C3に松牛弁、D3に弁当、E3に600が表示されます。検索範囲I3:I7は1列、戻り範囲J3:L7は3列です。両者の行数は同じ5行にそろえます。
▶ C3の式を、上の練習ツールで試す ／ 一覧にない番号（C6）の結果を見る
文字列を直接指定するときは引用符を付ける
セル参照のB3と、商品番号そのものの文字列B-101は別物です。B-101を引用符なしで入力すると正しい商品名として認識されません。検索値を変更する練習ではセル参照にしておくと便利です。
コピーするのは数式の起点
商品番号をB4、B5にも入力した場合、C3の式をC4、C5へコピーすると、それぞれの結果が右側に広がります。$I$3:$I$7と$J$3:$L$7は固定し、B3だけをB4、B5へ動かします。
D3やE3はスピル結果なので個別に書き換えられません。変更はC3の数式で行います。結果を固定したい場合は、コピーして値として貼り付けます。
複数列と複数行の違い
| 目的 | 使う方法 |
|---|---|
| 同じ商品の名前・分類・単価 | XLOOKUPの戻り範囲を3列にする |
| 同じ商品に関する全明細 | FILTERで該当行を抽出する |
| 縦横の条件が交わる値 | XLOOKUPの入れ子など |
同じ商品IDが元表に2行あると、この式は通常先頭の1行分を返します。3列に広がったからといって、重複IDの全件が返るわけではありません。
#SPILL!が出たら空き領域を確認する
- C3:E3に値・数式・結合セルが残っていないか確認する。
- 見た目が空白でも、空文字を返す数式がないか確認する。
- 数式の出力先はExcelテーブルの外側に置く。
戻り範囲を増やしたときは、その列数分だけ出力先を空けます。詳細はスピルエラーの直し方へ。
横方向に検索して縦へ返すこともできる
見出しが横に並ぶ表では、検索範囲を1行、戻り範囲を複数行にすれば縦に結果が広がります。縦向きの表だけに限定された機能ではありません。具体的なセル配置はXLOOKUPの横方向検索で確認できます。
次に読むと理解が深まる記事
- XLOOKUPで複数該当をすべて抽出できる？FILTERとの違いと2件目の取得
- XLOOKUPで横方向に検索｜年・月の見出しから売上を取得する方法
- Excelの#SPILL!（スピル）エラーを直す方法｜FILTERで原因を確認
参考：Microsoft公式ドキュメント XLOOKUP ／ DYNAMIC ARRAY FORMULAS AND SPILLED ARRAY BEHAVIOR
