Excel 2019以前のVLOOKUPは「検索値は1セル1つ」が原則で、複数の値を一気に調べるには関数をドラッグするか、CSE配列数式を使う必要がありました。Excel 2024 / Microsoft 365では、=VLOOKUP(E2:E6, A2:C6, 2, FALSE) のように検索値に範囲(配列)を渡すと、結果が自然に5行スピルします。
ただし便利になった反面、配列スピル特有の罠が3つあります。「多列戻りで列が落ちる」「近似検索が無言で誤値を返す」「スピル先衝突で #SPILL!」の3つです。本記事では実機検証の結果を示しながら、回避策まで一気に押さえます。
対象バージョン: Excel 2024(Build 19929)/ Microsoft 365。Excel 2021でも同様にスピルしますが、Excel 2019以前は本記事の挙動になりません。
Excel 2024でVLOOKUPが配列検索でスピルすることの確認
まず核心の挙動を確認します。検索表 A2:C6 に対して、検索値を縦に5つ並べた E2:E6 を VLOOKUP の第1引数(検索値)に渡します。
=VLOOKUP(E2:E6, A2:C6, 2, FALSE)
G2のセル1つに数式を入れただけで、G2:G6 の5行に結果が広がります。スピル範囲のセルを選択すると、5行全体が青い枠で囲まれて表示されます。これがExcel 2024の動的配列によるスピル動作です。
検索値に存在しない A999 や A888 は #N/A で返ります。この #N/A を一括で文字列に置き換えたい場合は IFNA で外包します。
=IFNA(VLOOKUP(E2:E6, A2:C6, 2, FALSE), "該当なし")
スピル全体に IFNA が効くため、行ごとに個別に書く必要はありません。IFERROR ではなく IFNA を推奨するのは、#REF! や #NAME? のような他のエラーまで握り潰さないためです。
なお Microsoft サポートの VLOOKUP 関数のドキュメントを見ると、第1引数の検索値は「セルへの値または参照を指定できます」とだけ記載されています。配列を渡せるとは明記されていません。実機の Excel 2024 / 365 では確かにスピルしますが、長期的な仕様保証としては慎重に扱うのが安全です。
「VLOOKUPは配列に対応していない」と書かれた記事がある理由
2018年の動的配列導入以前のExcel(Excel 2019以前)では、配列数式(Ctrl+Shift+Enter で確定する古い書き方)以外で1セル1値が原則でした。VLOOKUPに範囲を渡しても、暗黙的交差(英: implicit intersection)で先頭1セル分しか返らない仕様だったため、「VLOOKUPは配列を扱えない」という説明が広く流通しています。
この説明はExcel 2019以前としては正確ですが、動的配列が導入されたMicrosoft 365 / Excel 2021 / Excel 2024 では現状と一致しません。ご利用のExcelが新しい場合は、本記事の挙動を確認してから記事の手順を選んでください。
VLOOKUP配列スピルの罠①:列番号を{2,3}にしても1列しか出ない
「複数の値を一気に取れるなら、複数の列もまとめて取れるのでは」と、第3引数の列番号(英: col_index_num)に {2,3} を渡すと、見た目は5行スピルしますが実際は先頭の1列(B列)しか返りません。残りの列(C列)は欠落します。
=VLOOKUP(E2:E6, A2:C6, {2,3}, FALSE)Microsoft サポートの VLOOKUP 関数ドキュメントには、第3引数の列番号に複数列を渡せるとは書かれていません。エラーを表示せず無言で1列だけ返すため、気づかないまま下流の計算を壊す可能性があります。
複数列を取りたい場合は、列ごとに VLOOKUP を書いて HSTACK で結合する書き方が安全です。
=HSTACK(VLOOKUP(E2:E6, A2:C6, 2, FALSE), VLOOKUP(E2:E6, A2:C6, 3, FALSE))
これで5行2列のスピルになり、名前と売上の両方が並びます。なお、XLOOKUPで配列検索値と複数列戻り(=XLOOKUP(配列, 1列, 複数列))を組み合わせたときも、結果が想定どおりに広がらないケースがあり、回避策はやはり列ごとに分けて HSTACK で結合する書き方になります。XLOOKUPで複数の検索条件を扱う基本はExcelでXLOOKUP関数の複数条件検索を行う方法を参照してください。
VLOOKUP配列スピルの罠②:検索の型をTRUEにすると無言で誤値を返す
VLOOKUPの第4引数(検索の型、英: range_lookup)を TRUE にすると近似検索になります。配列検索値でもスピル自体は成立しますが、未一致キーで意図しない値が返ったり、検索値の位置によって動作が分かれたりするため、配列スピル文脈では危険です。誤値の場合はエラー表示が出ないため、目で気づきにくい罠になります。
=VLOOKUP(E2:E6, A2:C6, 2, TRUE)
Microsoft サポートの VLOOKUP 関数ドキュメントによれば、検索の型に TRUE を指定すると「テーブルの最初の列が数値順またはアルファベット順に並べ替えられているものと仮定し、最も近い値を検索」する動作になります。完全一致しない場合は 検索値以下の最大値に対応する行が返り、検索値が範囲の最小値未満なら #N/A、検索値が範囲の最大値を超える場合は最終行の値が返ります。#N/A は気づける一方、最大値を超える検索値で最終行の値が静かに返るのが本当の罠です。
整列済の検索表でも、検索値が最大値を超える場合などは、誤値が静かに混入します。配列スピル文脈では FALSE(完全一致)を固定で使い、近似検索が必要な場合は範囲を必ず昇順にしたうえで IFNA で #N/A も明示してください。
VLOOKUP配列スピルの罠③:スピル先に既存値があると#SPILL!エラー
従来の1セルVLOOKUPでは起きなかったエラーが、配列スピルでは起きます。スピル展開先のセルにすでに値が入っていると、数式を入力したセル(スピルの基点)が #SPILL! になります。
回避策はシンプルで、スピル域を空けるのが基本です。意図的に出力件数を絞りたい場合は、TAKE 関数で明示的に行数を指定する書き方が安全です。#SPILL! 全般の原因と対策はエラー値「#スピル!」の発生原因と解決方法でまとめています。テーブル内に配列スピルを書いて起きる別パターンの #スピル! は「スピル範囲がテーブル内です」の原因と3つの対処法を参照してください。
補足:セル番地に見えるテーブル名は使わない
VLOOKUPの罠ではなくテーブル命名側の問題ですが、配列スピルと組み合わせるとハマる人がいるため補足します。
Microsoft サポートの Excel テーブルの名前を変更するによれば、テーブル名は Z$100 や R1C1 のようなセル参照と同じ名前にできないと明記されています。Tbl1 のようなセル番地に見える名前(=TBL1セルとして解釈される)も同様に、通常UIでは命名時に拒否されます。
ただしCOM経由や外部生成ブックでは、Tbl1 のような名前が入った状態で受け取ってしまう場合があります。そのとき =VLOOKUP(E2:E6, Tbl1, 2, FALSE) は、Tbl1がTBL1セル参照として解釈されて全行 #N/Aを返します。テーブル名は DataTbl や 売上テーブル のように、セル番地と区別できる名前にするのが安全です。
横方向の配列検索値とスピル参照への展開
本記事では検索値を縦に5つ並べて検証していますが、横方向(H1:L1 など)に並べた検索値を渡すと、結果も横方向に5列スピルします。検索値の向きにスピル方向が追従するため、表のレイアウトに合わせて選べます。
また、別の数式のスピル結果を検索値にしたい場合は # 演算子(スピル参照)で連動させられます。たとえば別の場所(例: K2)に下のような FILTER や UNIQUE でスピル式を入れ、そのスピル元アドレスに # を付けて VLOOKUP に渡します。
=UNIQUE(注文履歴!A2:A1000)
このスピル元アドレスが K2 なら、検索値として K2# を渡せます。
=VLOOKUP(K2#, A2:C6, 2, FALSE)
K2 を起点とするスピル全体が検索値になり、スピル元の行数が変わると VLOOKUP 側のスピル行数も自動で追従します。検索値リストを UNIQUE や FILTER で動的に生成しておき、その結果を本記事の VLOOKUP に流す使い方が便利です。なお、検索値を直接 E2:E6 のように静的な範囲で並べているだけのときは、E2 はスピル元ではないため E2# という書き方にはなりません。
Excel 2019以前のバージョンとの互換性
本記事で扱った配列VLOOKUPは、動的配列に対応していないExcel 2019 / 2016 では同じ挙動になりません。Excel 2024 / 365 で作成したスピル数式入りの .xlsx を Excel 2019 で開くと、暗黙的交差で先頭1セルだけが表示される可能性があります。
古いExcelで開くと、関数名の前に _xlfn. という接頭辞が付いて #NAME? エラーになる場合があります。ただし本記事のVLOOKUP配列スピル式自体は、保存後も _xlfn.SINGLE のような互換マーカーは付かず、ファイル内部では「配列数式」として保存されます。_xlfn. 接頭辞の仕組み全般は今後公開予定の関連記事を参照してください。
送信先がExcel 2019以前の可能性がある場合は、配列スピル式ではなく従来の「セルに1つずつVLOOKUP」「IFERROR外包」の書き方を選んでください。具体的にはVLOOKUP関数がエラーを返す可能性がある場合の回避処理:IFERRORを使うに従来手法をまとめています。VLOOKUP / HLOOKUP / INDEX+MATCH の選び分けはエクセルでの関数の使い方:VLOOKUP, HLOOKUP, INDEX, MATCH の基本と応用を参照してください。
サンプルファイルのダウンロード
本記事で使用した検証用ブックです。「基本のスピル」「罠①多列戻り」「罠②近似検索」「罠③スピル衝突」「補足:テーブル名命名」の5シートが入っています。Excel 2024 / Microsoft 365 で開いてください。
VLOOKUP配列スピル + 3つの罠 サンプルブック(.xlsx)まとめ:VLOOKUP配列スピルの3原則
Excel 2024 / Microsoft 365 のVLOOKUPは配列検索値を渡せばスピルしますが、3つの罠を避ける必要があります。
- IFNA外包:
#N/Aを一括で文字列に置換する - FALSE固定:配列スピル文脈では完全一致を使う(
TRUEは無言で誤値) - HSTACK分解:複数列を取りたいときは列ごとにVLOOKUPを書いて結合する
加えてスピル先の障害物に注意し、外部生成ブックを受け取ったらテーブル名にセル番地のような名前が入っていないか確認してください。
同じ検索値で複数結果を取りたい場合は別アプローチで、VLOOKUP関数で複数ヒットをした際に全てをリストアップしたい場合の代替手段を参照してください。スピルで #N/A を消す書き分けの全体像はスピル#N/Aを消す4選に整理しています。
コメント