ExcelのXLOOKUP関数で検索値を配列、戻り範囲を複数列にすると、戻り範囲を2列以上指定してもスピルは先頭の1列分しか展開されません。日本語圏では「縦横同時にスピルしない問題」と呼ばれており、Microsoft公式XLOOKUPドキュメントには明記されていない動作ですが、Excel 2024(ビルド19929)で再現します。本記事では、原因とHSTACK関数による本命解、MAKEARRAY/REDUCEなどの別解、そしてやってはいけない5つのパターンを実機スクショ付きで解説します。
- 結論: 多列戻りはHSTACKで個別XLOOKUPを横に並べる
- 問題の再現: 検索値の渡し方で結果が変わる
- なぜ1列しかスピルしないのか
- 本命の解決策: HSTACKで個別XLOOKUPを水平結合する
- 別解1: MAKEARRAY + LAMBDA(列数を変数で扱える)
- 別解2: REDUCE + VSTACK(動くが遅く、未マッチ処理に注意)
- やってはいけない 5パターン
- 補足: スピル妨害は通常通り #SPILL!、隣列の障害物は干渉しない
- パフォーマンス比較(参考、体感差はほぼない)
- IFNA / 第4引数 / IFERROR の使い分け
- 参考: VLOOKUPもExcel 2024では配列でスピルする
- まとめ
- 配布サンプル
- 関連記事
結論: 多列戻りはHSTACKで個別XLOOKUPを横に並べる
XLOOKUPで配列の検索値と複数列の戻り範囲を同時に指定すると、戻り範囲がどれだけ広くても1列しかスピルしません。
=XLOOKUP(G2:G6, A2:A16, B2:C16)
→ 5行 × 1列のみ(C列データは展開されない)本命の解決策は、列ごとに個別のXLOOKUPを書いてHSTACK関数で水平結合することです。
=HSTACK(
XLOOKUP(G2:G6, A2:A16, B2:B16),
XLOOKUP(G2:G6, A2:A16, C2:C16)
)
→ 5行 × 2列で正しくスピル問題の再現: 検索値の渡し方で結果が変わる
同じXLOOKUP関数でも、検索値が単一のときと配列のときで、戻り範囲の扱いが変わります。書籍マスタを例にパターン別に動作を比較します。検索値はG2:G6、マスタはA2:E16です。
パターンA 単一検索値 × 多列戻り(正常)
=XLOOKUP(G2, A2:A16, B2:E16)
→ 1行 × 4列で正常スピル(在庫・書名・発刊日・金額が横並び)
パターンB 配列検索値 × 単列戻り(正常)
=XLOOKUP(G2:G6, A2:A16, B2:B16)
→ 5行スピル(検索値ごとに在庫値が縦に並ぶ)
パターンC 配列検索値 × 多列戻り(問題のケース)
=XLOOKUP(G2:G6, A2:A16, B2:C16)
→ 5行 × 1列のみ(B列の在庫だけが展開され、C列の書名は失われる)
戻り範囲を3列(B2:D16)、4列(B2:E16)に広げても結果は同じで、常に先頭1列だけがスピルします。
| 検索値 | 戻り範囲 | 結果 |
|---|---|---|
| 単一(G2) | 単列(B2:B16) | 1セル(普通の検索) |
| 単一(G2) | 多列(B2:E16) | 1行 × 4列スピル ◯ |
| 配列(G2:G6) | 単列(B2:B16) | 5行 × 1列スピル ◯ |
| 配列(G2:G6) | 多列(B2:E16) | 5行 × 1列のみ ✕(縦横同時にスピルしない) |
なぜ1列しかスピルしないのか
XLOOKUPの公式ドキュメント(Microsoftサポート)には、この制約は明記されていません。サードパーティの解説では、XLOOKUPに配列検索値と多列の戻り範囲を同時に渡すと縦横の2方向には展開せず先頭列だけになる挙動があり、英語圏では「array of arrays problem」として知られています。
具体的には、検索値が配列(N行)で戻り範囲が多列(K列)のとき、出力は本来N×Kの2次元になります。XLOOKUPはこの組み合わせでは縦横2方向に展開せず、戻り範囲の先頭1列だけを採用してN行 × 1列にして返します。横方向の検索値配列でも同様で、1行 × N列(先頭1行のみ)に折り畳まれます。SEQUENCEやMAKEARRAYは普通に2次元スピルできるため、これはExcel全体の制約ではなくXLOOKUPと配列検索値の組み合わせ固有の挙動です。
本命の解決策: HSTACKで個別XLOOKUPを水平結合する
「複数列が必要なら、列ごとに別々のXLOOKUPを書いて、HSTACKで横に並べる」のが本命解です。
=HSTACK(
XLOOKUP(G2:G6, A2:A16, B2:B16),
XLOOKUP(G2:G6, A2:A16, C2:C16)
)
列を増やしたいときは、HSTACKの引数にXLOOKUPを追加するだけです。発刊日・金額まで含めるなら次のようになります。
=HSTACK(
XLOOKUP(G2:G6, A2:A16, B2:B16),
XLOOKUP(G2:G6, A2:A16, C2:C16),
XLOOKUP(G2:G6, A2:A16, D2:D16),
XLOOKUP(G2:G6, A2:A16, E2:E16)
)
未マッチ行の#N/Aを「該当なし」などに置き換えるなら、外側にIFNAを1個だけ付けます。HSTACK全体に効くため、各XLOOKUPに第4引数を書く必要はありません。
=IFNA(HSTACK(
XLOOKUP(G2:G6, A2:A16, B2:B16),
XLOOKUP(G2:G6, A2:A16, C2:C16),
XLOOKUP(G2:G6, A2:A16, D2:D16),
XLOOKUP(G2:G6, A2:A16, E2:E16)
), "該当なし")
別解1: MAKEARRAY + LAMBDA(列数を変数で扱える)
戻り列数を引数で動的に変えたい場合は、MAKEARRAYとLAMBDAの組み合わせが便利です。第2引数の4を変えるだけで戻り列数を切り替えられます。
=MAKEARRAY(ROWS(G2:G6), 4, LAMBDA(r,c,
XLOOKUP(INDEX(G2:G6,r), A2:A16, INDEX(B2:E16,,c))
))
パフォーマンス上はHSTACKと差がありません(後述のH2-9の表を参照)。式の見た目はやや複雑ですが、戻り列数を変動させたいケースではこちらが扱いやすくなります。
別解2: REDUCE + VSTACK(動くが遅く、未マッチ処理に注意)
古くから紹介されている回避策にREDUCE + VSTACK + LAMBDAの組み合わせがあります。各検索値ごとにXLOOKUPを呼び、VSTACKで縦に積み上げる方式です。
=DROP(REDUCE("", G2:G6, LAMBDA(a,b,
VSTACK(a, XLOOKUP(b, A2:A16, B2:E16, "該当なし"))
)), 1)
=IFNA(...)で外包するか、HSTACK分解を選んでください。パフォーマンスは10,000行クラスでHSTACK分解の約25倍遅くなります(H2-9の計測表)。動くは動くものの、HSTACK分解の方がシンプルで速いため、新規に書くなら本命解(H2-4)を推奨します。
やってはいけない 5パターン
XLOOKUPの戻りが1列しか出ないとき、ネット上で見かける「これで直りそう」な書き方の多くは実機では動きません。代表的な5つのNGパターンを示します。
NG1: BYROW + LAMBDA で多列戻り → #CALC!
=BYROW(G2:G6, LAMBDA(x, XLOOKUP(x, A2:A16, B2:E16)))
→ #CALC!
BYROWのLAMBDAは各行ごとに1つのスカラ値を返すことを要求しています。XLOOKUPが1×4の行ベクトルを返すと、BYROWは結果配列を組み立てられず#CALC!になります。多列戻りにはMAKEARRAY(H2-5)を使ってください。BYROWは戻りが単列のときだけ使える関数です。
NG2: 戻り範囲をHSTACKで包む → 1列のまま
=XLOOKUP(G2:G6, A2:A16, HSTACK(B2:B16, C2:C16))
→ 5行 × 1列のみ(改善しない)
戻り範囲をHSTACKで束ねただけでは、Excelから見れば「2列ある戻り範囲」というだけで、配列検索値 × 多列戻りの1列制約は変わりません。HSTACKは戻り範囲を包むのではなく、外側で個別XLOOKUPの結果を結合する位置で使ってください(H2-4)。
NG3: CHOOSECOLSで後置きラップ → #VALUE!
=CHOOSECOLS(XLOOKUP(G2:G6, A2:A16, B2:E16), 1, 2)
→ #VALUE!
内側のXLOOKUPはそもそも1列しか返さないため、CHOOSECOLSが2列目を選ぼうとして#VALUE!になります。後置きの整形では救出できません。
NG4: CHOOSECOLSで前置きラップ → 1列のまま
=XLOOKUP(G2:G6, A2:A16, CHOOSECOLS(B2:E16, 1, 2))
→ 5行 × 1列のみ戻り範囲側をCHOOSECOLSで2列に整えても、配列検索値の1列制約はそのままです。
NG5: TOROW / TRANSPOSEで後置きラップ → 失われたデータは戻らない
=TOROW(XLOOKUP(G2:G6, A2:A16, B2:C16))
→ 5セル横並び(C列データは元から無い)
失われた列のデータは「セルの中に隠れている」のではなく、そもそも返されていません。TOROWやTRANSPOSEで配置を変えても新しいデータは現れません。
=@XLOOKUP(G2:G6, A2:A16, B2:E16)のように@(暗黙的交差)を付けると、配列検索値が1セル分(G2)に絞られて結果も1セルになります。多列救出にはなりません。補足: スピル妨害は通常通り #SPILL!、隣列の障害物は干渉しない
本記事の問題のケース(配列検索値 × 多列戻り)でも、スピル先のセル(1列スピル範囲内)に別の値があれば、通常通り#SPILL!エラーが出ます。一方、戻り範囲の2列目以降が本来スピルしてくる位置に障害物があっても2列目はそもそもスピルしないため干渉しません。
対処は通常スピルと同じで、スピル先のセルをクリアして再計算するだけです。スピル系エラーの全パターンはエラー値「#スピル!」の発生原因と解決方法を参照してください。
パフォーマンス比較(参考、体感差はほぼない)
3つの正解パターン(HSTACK / MAKEARRAY / REDUCE+VSTACK)で計算時間を測りました。3試行の中央値です。
| 検索範囲 | 検索キー | HSTACK | MAKEARRAY | REDUCE + VSTACK |
|---|---|---|---|---|
| 1,000行 | 100 | 0.006秒 | 0.003秒 | 0.004秒 |
| 10,000行 | 1,000 | 0.011秒 | 0.017秒 | 0.270秒 |
| 50,000行 | 1,000 | 0.048秒 | 0.052秒 | 0.223秒 |
10,000行クラスでHSTACKが最速、REDUCE+VSTACKは約25倍遅いという差はあるものの、絶対時間はどれも0.3秒未満で体感差はほぼありません。可読性で選ぶならHSTACK分解が最も素直です。
IFNA / 第4引数 / IFERROR の使い分け
HSTACK分解した結果に未マッチ#N/Aが混ざるときは、外側にIFNAを1個付けるのが最短です。
=IFNA(HSTACK(XLOOKUP(...), XLOOKUP(...)), "該当なし")行・列ごとに違う代替値を出したいときは、IFNAの第2引数に同じ大きさの配列を渡せます。
XLOOKUPの第4引数("該当なし")に配列を渡すと、未マッチ行の値が#VALUE!になります(マッチ行は通常通りスカラが返ります)。配列代替値が必要な場合はIFNA外包に切り替えてください。
IFERRORは#N/A以外の#DIV/0!や#REF!まで握り潰してしまうため、本来気付きたい別のエラーが見えなくなります。意図が明確なIFNAを優先してください。
参考: VLOOKUPもExcel 2024では配列でスピルする
Excel 2024やMicrosoft 365のExcelでは、VLOOKUPに配列の検索値を渡しても自然にスピルします。XLOOKUPに乗り換えなくても、IFNA外包で同じことができます。
=IFNA(VLOOKUP(G2:G6, A2:C16, 2, FALSE), "該当なし")ただし、Excel 2019やExcel 2016など動的配列非対応の環境で開かれるファイルでは旧形式に戻してください。
まとめ
- XLOOKUPで配列検索値 × 多列戻りを指定しても、スピルは先頭1列分しか展開されない
- 本命解はHSTACKで個別XLOOKUPを水平結合すること
- 列数を変動させたい場合はMAKEARRAY + LAMBDAが便利
- REDUCE + VSTACKは動くが10,000行規模でHSTACKの約25倍遅い
- BYROW + LAMBDAは多列戻りで#CALC!になるので使えない(BYROWは単列戻り専用)
- 戻り範囲をHSTACK・CHOOSECOLSで包む / TOROW / TRANSPOSE / @ などはどれも回避策にならない
- 未マッチ#N/AはHSTACK全体に対してIFNAを1個外側に付けるのが最短
配布サンプル
本記事の主な式と失敗例・回避策を全8シートに整理した配布サンプルです。本命HSTACK・MAKEARRAY・REDUCE+VSTACKの3つの正解と、BYROW・戻り範囲HSTACK・CHOOSECOLS・TOROW/TRANSPOSEの4つのNGパターンが入っています。
関連記事
- XLOOKUP関数で複数列のスピル配列検索結果を作成する方法 – 単一検索値で多列を取る基本
- XLOOKUP関数の戻り範囲へ特殊な順番の列を登録する方法 – HSTACKで戻り順を変える応用
- HSTACK関数で配列を水平結合する方法 – HSTACKの基本
- ExcelでXLOOKUP関数の複数条件検索を行う方法 – 複数条件で配列検索したい場合
- スピル#N/Aを消す4選 – IFNA / 第4引数 / IFERRORの使い分け
- エラー値「#スピル!」の発生原因と解決方法 – スピル系エラー総合
- XLOOKUP関数の基本的な使い方
コメント