XLOOKUPで複数列をまとめて取得｜スピルで商品名・分類・単価を表示

1つの商品IDから商品名・分類・単価をまとめて返すには、XLOOKUPの戻り範囲を複数列にします。数式は左端の1セルに入力し、結果が右へ広がる空間を空けます。同じIDに一致する複数行をすべて返す機能とは異なります。

無料Excelサンプルで一緒に試す

商品リストはI3:L7です。B3の商品番号を変更すると、C3:E3に商品名・分類・単価が広がる例を確認できます。

無料サンプルファイルをダウンロード（.xlsx）

Microsoft 365向け。XLOOKUP・FILTER・UNIQUEの例はExcel 2024・2021でも利用できます。本文の対応版もご確認ください。

戻り範囲を3列指定する

B3にB-101を入力し、C3に次の式を入れます。

=XLOOKUP(B3,$I$3:$I$7,$J$3:$L$7,"該当なし",0)

C3に松牛弁、D3に弁当、E3に600が表示されます。検索範囲I3:I7は1列、戻り範囲J3:L7は3列です。両者の行数は同じ5行にそろえます。

文字列を直接指定するときは引用符を付ける

=XLOOKUP("B-101",$I$3:$I$7,$J$3:$L$7,"該当なし",0)

セル参照のB3と、商品番号そのものの文字列B-101は別物です。B-101を引用符なしで入力すると正しい商品名として認識されません。検索値を変更する練習ではセル参照にしておくと便利です。

コピーするのは数式の起点

商品番号をB4、B5にも入力した場合、C3の式をC4、C5へコピーすると、それぞれの結果が右側に広がります。$I$3:$I$7と$J$3:$L$7は固定し、B3だけをB4、B5へ動かします。

D3やE3はスピル結果なので個別に書き換えられません。変更はC3の数式で行います。結果を固定したい場合は、コピーして値として貼り付けます。

複数列と複数行の違い

同じ商品IDが元表に2行あると、この式は通常先頭の1行分を返します。3列に広がったからといって、重複IDの全件が返るわけではありません。

#SPILL!が出たら空き領域を確認する

• C3:E3に値・数式・結合セルが残っていないか確認する。
• 見た目が空白でも、空文字を返す数式がないか確認する。
• 数式の出力先はExcelテーブルの外側に置く。

戻り範囲を増やしたときは、その列数分だけ出力先を空けます。詳細はスピルエラーの直し方へ。

横方向に検索して縦へ返すこともできる

見出しが横に並ぶ表では、検索範囲を1行、戻り範囲を複数行にすれば縦に結果が広がります。縦向きの表だけに限定された機能ではありません。具体的なセル配置はXLOOKUPの横方向検索で確認できます。

無料Excelサンプルで復習する

商品リストはI3:L7です。B3の商品番号を変更すると、C3:E3に商品名・分類・単価が広がる例を確認できます。

無料サンプルファイルをダウンロード（.xlsx）

Microsoft 365向け。XLOOKUP・FILTER・UNIQUEの例はExcel 2024・2021でも利用できます。本文の対応版もご確認ください。

次に読むと理解が深まる記事

• XLOOKUPで複数該当をすべて抽出できる？FILTERとの違いと2件目の取得
• XLOOKUPで横方向に検索｜年・月の見出しから売上を取得する方法
• Excelの#SPILL!（スピル）エラーを直す方法｜FILTERで原因を確認

参考：Microsoft公式ドキュメント XLOOKUP ／ DYNAMIC ARRAY FORMULAS AND SPILLED ARRAY BEHAVIOR