XLOOKUPの使い方｜VLOOKUPとの違い・複数条件・#N/A対処

XLOOKUP は、検索する範囲と結果を返す範囲を別々に指定して値を取り出す Excel の関数で、基本形は =XLOOKUP(検索値, 検索範囲, 戻り範囲) です。 VLOOKUP と違い、列番号を数える必要がなく、検索列より左の列も返せます。既定で完全一致になり、見つからないときに表示する値も引数で指定できます。

この記事では、同じサンプル表に対する XLOOKUP・VLOOKUP・INDEX+MATCH の数式を並べた比較表、6つの引数の早見表（一致モード・検索モードの全値）、複数条件の2方式、#N/A・#VALUE!・#SPILL!・#NAME? を再現する数式と直し方、対応バージョン表を、計算結果つきでまとめます。

確認環境：XLOOKUP の数式は表計算エンジン HyperFormula 3.4.0（Node.js 22）で計算し、同じ答えになる VLOOKUP・INDEX+MATCH の数式を LibreOffice Calc 24.2.7（Ubuntu 24.04、ヘッドレス実行）で検算しました。Calc 24.2 には XLOOKUP 関数がないため、この組み合わせにしています。引数の仕様・対応バージョン・スピルの条件は Microsoft サポートのページの記載です。

XLOOKUPの早見表

構文と6つの引数

=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])

一致モードの全値

検索モードの全値

二分探索（2 と -2）は、検索範囲が並べ替えられていることが前提です。Microsoft サポートのページには、並べ替えられていない場合は無効な結果が返ると書かれています。

この記事で使うサンプル表

セル A1 から次の表が入っているとします。H1:I4 は、単価から区分を決めるための表です。

A1 に貼り付けるためのタブ区切りです（G列は空けてあります）。

商品コード   商品名 分類  色   単価  在庫      下限  区分
A001    ノートPC   本体  黒   98000   12      0   C
A002    モニター    周辺  黒   32000   8       10000   B
A003    キーボード   周辺  白   6800    30      50000   A
A004    マウス 周辺  黒   2400    45
A005    キーボード   周辺  黒   7200    0
A006    ドック 周辺  銀   15800   5

XLOOKUP・VLOOKUP・INDEX+MATCH の数式比較表

同じ結果を得るための数式を、3つの書き方で並べました。

「最後に一致した行」の3列目は、INDEX+MATCH では書きにくいため、同じ結果になる LOOKUP 関数の定番の書き方を載せています。

VLOOKUP との違い

INDEX+MATCH との違い

以前の版の Excel で開く可能性があるブックは INDEX+MATCH、そうでなければ XLOOKUP という使い分けがわかりやすい基準です。

基本の使い方

完全一致で検索する

=XLOOKUP("A003",A2:A7,B2:B7)

結果は「キーボード」です。検索値をセル（たとえば K2）に入れて =XLOOKUP(K2,A2:A7,B2:B7) と書くのが通常の形です。数式を下へコピーするときは、範囲を絶対参照にします。

=XLOOKUP(K2,$A$2:$A$7,$B$2:$B$7)

大文字と小文字は区別されません。=XLOOKUP("a003",A2:A7,B2:B7) も「キーボード」を返します。

見つからないときの表示を決める

=XLOOKUP("Z999",A2:A7,B2:B7)            → #N/A
=XLOOKUP("Z999",A2:A7,B2:B7,"該当なし")  → 該当なし

第4引数に "" を指定すると空欄に見せられます。

「以上・以下」の区分を引く（一致モード -1 と 1）

H2:I4 の表から、金額 20000 の区分を引きます。

=XLOOKUP(20000,H2:H4,I2:I4,"",-1)       → B（20000 以下で最大の下限は 10000）
=XLOOKUP(20000,H2:H4,I2:I4,"",1)        → A（20000 以上で最小の下限は 50000）
=XLOOKUP(20000,H2:H4,I2:I4,"該当なし",0) → 該当なし（完全一致する行がない）

ワイルドカードで検索する（一致モード 2）

=XLOOKUP("ノート*",B2:B7,E2:E7,"",2)      → 98000
=XLOOKUP("?ウス",B2:B7,A2:A7,"",2)        → A004
=XLOOKUP("ノート*",B2:B7,E2:E7,"該当なし") → 該当なし

* は0文字以上、? は1文字を表します。3つ目のように一致モードを省くと完全一致になり、ワイルドカードの記号は文字そのものとして扱われます。 VLOOKUP は完全一致の指定のままワイルドカードが使えるため、置き換えるときは第5引数に 2 を足します。

最後に一致した行を返す（検索モード -1）

「キーボード」は4行目と6行目にあります。

=XLOOKUP("キーボード",B2:B7,E2:E7,"",0,1)   → 6800（先頭から）
=XLOOKUP("キーボード",B2:B7,E2:E7,"",0,-1)  → 7200（末尾から）

日付順に追記していく記録から、最新の1件を取り出すときに使えます。

横方向の検索と、行・列の交点

検索範囲を1行にすると、横方向に検索します。

=XLOOKUP("単価",A1:F1,A4:F4)                         → 6800
=XLOOKUP("A003",A2:A7,XLOOKUP("在庫",A1:F1,A2:F7))  → 30

2つ目は、内側の XLOOKUP が「在庫」の列（F2:F7）を返し、外側の XLOOKUP がその列から A003 の行を取り出します。

1行分をまとめて返す（スピル）

戻り範囲を複数列にすると、結果が右のセルへ広がります。この動きをスピルと呼びます。A10 に次の数式を入れた結果です。

A10: =XLOOKUP("A003",A2:A7,B2:F7)

数式は A10 の1つだけで、B10〜E10 には自動で値が入ります。

複数条件で検索する2つの方式

「商品名がキーボード、かつ色が黒」のコード（A005）を取り出します。

方式1：検索値と検索範囲を & で連結する

=XLOOKUP("キーボード"&"黒",B2:B7&D2:D7,A2:A7)   → A005

検索範囲側の B2:B7&D2:D7 は、行ごとに「ノートPC黒」「モニター黒」「キーボード白」…という文字列を作ります。検索値も同じ順で連結して探します。

連結した結果が偶然同じになる組み合わせ（「AB」と「C」、「A」と「BC」）があるデータでは、区切り文字を挟みます。

=XLOOKUP("キーボード"&"|"&"黒",B2:B7&"|"&D2:D7,A2:A7)   → A005

方式2：条件式を掛け合わせて 1 を探す

=XLOOKUP(1,(B2:B7="キーボード")*(D2:D7="黒"),A2:A7)   → A005

(B2:B7="キーボード") は行ごとに TRUE／FALSE を返し、掛け算すると TRUE は 1、FALSE は 0 として計算されます。すべての条件を満たす行だけが 1 になるので、検索値 1 で探します。

条件を3つ以上にするときは、かっこを掛け足します。不等号も使えます。

=XLOOKUP(1,(C2:C7="周辺")*(D2:D7="黒")*(F2:F7>=10),B2:B7,"該当なし")  → マウス
=XLOOKUP(1,(B2:B7="マウス")*(D2:D7="白"),A2:A7,"該当なし")            → 該当なし

1つ目は「分類が周辺、色が黒、在庫が10以上」で、モニター（在庫8）は外れ、マウス（在庫45）が返ります。

2つの方式の選び方

INDEX+MATCH でも同じ考え方で書けます。次の3つは、LibreOffice Calc で配列数式として入力し、XLOOKUP と同じ結果になることを確かめました。

=INDEX(A2:A7,MATCH("キーボード"&"黒",B2:B7&D2:D7,0))                   → A005
=INDEX(A2:A7,MATCH(1,(B2:B7="キーボード")*(D2:D7="黒"),0))             → A005
=INDEX(B2:B7,MATCH(1,(C2:C7="周辺")*(D2:D7="黒")*(F2:F7>=10),0))       → マウス

エラーの原因別：再現する数式と直し方

#N/A：検索値が見つからない

=XLOOKUP("A003 ",A2:A7,B2:B7)             → #N/A
=XLOOKUP(TRIM("A003 "),A2:A7,B2:B7)       → キーボード

=XLOOKUP("32000",E2:E7,B2:B7)             → #N/A
=XLOOKUP(VALUE("32000"),E2:E7,B2:B7)      → モニター

見た目が同じでも、末尾の空白や、数値と文字列の違いがあると一致しません。ほかのシステムから取り込んだデータで起こりやすい原因です。検索値ではなく表の側に空白がある場合は、表の列を TRIM で整えた列を別に作ると直せます。

「見つからないこと」が正常な場合は、第4引数で表示を決めます。

=XLOOKUP("Z999",A2:A7,B2:B7,"該当なし")   → 該当なし

#VALUE!：検索範囲と戻り範囲の大きさが違う

=XLOOKUP("A003",A2:A7,B2:B6)   → #VALUE!
=XLOOKUP("A003",A2:A7,B2:B7)   → キーボード

検索範囲が6行、戻り範囲が5行になっています。行を追加したあとに片方の範囲だけを広げたときに起こります。表を Excel のテーブルにして列名で参照すると、行を足しても2つの範囲が同時に広がります。

この種類のエラーは、第4引数では置き換わりません。第4引数が働くのは「見つからなかった」ときだけです。

=XLOOKUP("A003",A2:A7,B2:B6,"該当なし")   → #VALUE!

#SPILL!：結果を広げる先がふさがっている

A10 に1行分を返す数式を入れ、C10 に「メモ」という文字が入っている状態です。

C10 を空にすると、A10 から E10 に「キーボード・周辺・白・6800・30」が表示されます。Microsoft サポートの日本語のページでは、このエラーは「#スピル!」と表記されています。

Microsoft サポートのページには、#スピル! の原因として次の内容が挙げられています。

#NAME?：関数名が認識されない

=XLOKUP("A003",A2:A7,B2:B7)   → #NAME?

つづりの誤りのほか、XLOOKUP に対応していない版で開いたときにも表示されます。今回の検算に使った LibreOffice Calc 24.2 でも、=XLOOKUP("A003",A2:A7,B2:B7) は #NAME? になりました。配布先の版がそろっていないブックでは、INDEX+MATCH で書いておくと同じ結果を表示できます。

XLOOKUPの対応バージョン

Microsoft サポートの XLOOKUP 関数のページに記載されている内容です。

Excel 2019・2016 で同じ結果を得るには、比較表の INDEX+MATCH の列の数式を使います。

よくある質問

Q. VLOOKUP を XLOOKUP に書き換えるとき、気をつけることは？ A. 3点あります。VLOOKUP で第4引数を省略または TRUE にしていた数式は近似一致なので、XLOOKUP では一致モードに -1 を指定します。ワイルドカードを使っていた数式には一致モード 2 を足します。IFERROR で包んでいた部分は、第4引数に移せます。

Q. 見つからないときに 0 や空欄を表示するには？ A. 第4引数に 0 や "" を指定します。=XLOOKUP("Z999",A2:A7,E2:E7,0) は 0 を、=XLOOKUP("Z999",A2:A7,B2:B7,"") は空の文字列を返します。合計などの計算に使う列は 0、表示だけの列は "" が向いています。

Q. 行数が多いと遅くなりますか？ A. 既定の検索モードは先頭の項目から順に探します。検索範囲が昇順または降順に並べ替え済みの表では、検索モード 2 または -2 の二分探索を指定できます。並べ替えられていない範囲に指定すると無効な結果になるため、並び順を保てる表に限って使います。

まとめ

• 基本形は =XLOOKUP(検索値, 検索範囲, 戻り範囲)。既定で完全一致になり、左の列も返せる
• 第4引数で見つからないときの表示を指定でき、IFERROR で包む必要がない
• 一致モードは 0・-1・1・2、検索モードは 1・-1・2・-2。ワイルドカードを使うときは一致モード 2 を指定する
• 複数条件は、& で連結する方式と、条件式を掛けて 1 を探す方式の2つ。不等号を使うなら掛け算の方式
• #N/A は検索値の空白や型の違い、#VALUE! は範囲の行数、#SPILL! はスピル先のセル、#NAME? はつづりと対応版を確認する
• Excel 2019・2016 では XLOOKUP が使えないため、配布先の版が混ざるブックは INDEX+MATCH で書く

参考・出典

• Microsoft サポート「XLOOKUP 関数」（構文、一致モード・検索モードの値、適用対象の製品）
• Microsoft サポート「#スピル! エラーを修正する方法」（スピルエラーの原因）

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。