1つの商品IDから商品名・分類・単価をまとめて返すには、XLOOKUPの戻り範囲を複数列にします。数式は左端の1セルに入力し、結果が右へ広がる空間を空けます。同じIDに一致する複数行をすべて返す機能とは異なります。
戻り範囲を3列指定する
B3にB-101を入力し、C3に次の式を入れます。
C3に松牛弁、D3に弁当、E3に600が表示されます。検索範囲I3:I7は1列、戻り範囲J3:L7は3列です。両者の行数は同じ5行にそろえます。
▶ C3の式を、練習ツールで試す ／ 一覧にない番号（C6）の結果を見る
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
XLOOKUP の基本（書き方・一致モード・下から検索・VLOOKUP との違い）は、XLOOKUP関数の使い方にまとめています。
文字列を直接指定するときは引用符を付ける
セル参照の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の全件が返るわけではありません。
【実機で検証】複数列で返すときの落とし穴
複数列を返す XLOOKUP を、空のセルがある表や、検索値が 2 つの場合で試しました。
| 場面 | 結果 | 対処 |
|---|---|---|
| 返す列に空のセルがある | 0 と表示される | 式の最後に &”” を付ける（数値も文字になる） |
| 見つからない（第4引数 “なし”） | 左端の 1 セルだけ「なし」 | {“なし”,””,0} のように列の数だけ指定する |
| 検索値を 2 つ渡す | 最初の列だけ返る | VSTACK で 1 行ずつの結果をつなぐ |
| 離れた列だけほしい | 返す範囲は 1 つのまま | CHOOSECOLS(式,1,3) で列を選ぶ |
| 返す範囲の途中に列を挿入する | 範囲が広がり、挿入した列も返る | 出力先の右を空けておく |
検索値を複数にすると、3 列指定していても最初の列しか返りませんでした。エラーが出ないので、気づきにくい動きです。
#SPILL!が出たら空き領域を確認する
- C3:E3に値・数式・結合セルが残っていないか確認する。
- 見た目が空白でも、空文字を返す数式がないか確認する。
- 数式の出力先はExcelテーブルの外側に置く。
戻り範囲を増やしたときは、その列数分だけ出力先を空けます。詳細はスピルエラーの直し方へ。
横方向に検索して縦へ返すこともできる
見出しが横に並ぶ表では、検索範囲を1行、戻り範囲を複数行にすれば縦に結果が広がります。縦向きの表だけに限定された機能ではありません。具体的なセル配置はXLOOKUPの横方向検索で確認できます。
次に読むと理解が深まる記事
- XLOOKUPで複数該当をすべて抽出できる？FILTERとの違いと2件目の取得
- XLOOKUPで横方向に検索｜年・月の見出しから売上を取得する方法
- Excelの#SPILL!（スピル）エラーを直す方法｜FILTERで原因を確認
参考：Microsoft公式ドキュメント XLOOKUP ／ DYNAMIC ARRAY FORMULAS AND SPILLED ARRAY BEHAVIOR
