XLOOKUP関数を入力したのに、セルに「#N/A」「#VALUE!」「#NAME?」が表示されて先に進めない。あるいは、エラーは出ていないのに取り出した金額が明らかにおかしい——。XLOOKUPのトラブルは、この「表示されるエラー」と「黙って間違う」の2種類に分かれます。
この記事では、症状から原因を特定して数式を直すところまでを一気通貫で解説します。特に後半で扱う「エラーが出ないまま誤った値を返す」パターンは、検算しない限り気づけないため、金額や在庫を扱う表では最優先で確認すべき項目です。
症状別トリアージ表：まずここで原因を絞る
セルの表示から原因を特定してください。該当する行のリンクから解説へ進めます。
| セルの表示・症状 | 主な原因 | 解説 |
|---|---|---|
| #N/A | 検索値が検索範囲に存在しない（データ型・空白・全角半角の不一致を含む） | #N/Aの対処 |
| #VALUE! | 検索範囲と戻り配列のサイズまたは向きが違う | #VALUE!の対処 |
| #REF! | 参照先のセルが削除された／閉じた別ブックを参照している | #REF!の対処 |
| #SPILL! | 結果を書き出す先に既存データ・結合セル・テーブルがある | #SPILL!の対処 |
| #NAME? ／ 数式に「_xlfn.XLOOKUP」と表示 | 使用中のExcelがXLOOKUPに対応していない | #NAME?の対処 |
| エラーは出ないが値が違う | 検索モード2／-2と未並べ替え、一致モード1／-1、範囲の相対参照ずれ | 黙って間違う3パターン |
| 空欄のはずが「0」になる | 参照先が空白セル | 空白セルが0になる件 |
#N/A：検索値が見つからない
XLOOKUPは、既定では完全一致で検索します。Microsoft公式ドキュメントによれば、一致するものがなく、かつ第4引数の「見つからない場合」も指定していないときに #N/A が返ります。
ここで多くの人がつまずくのは、画面上は同じに見えるのに、Excelは別物として扱っているケースです。目視で「確かにある」と確認できても一致しないときは、次の3つを順に疑ってください。
原因1：数値と「文字列としての数値」が混在している
基幹システムやCSVから取り込んだコードは、見た目が数字でも文字列として保存されていることがあります。検索値が数値の1001、検索範囲が文字列の”1001″だと、XLOOKUPは一致と判定しません。
判定方法は次のとおりです。
- 空いたセルに
=ISNUMBER(A2)と入力し、TRUEなら数値、FALSEなら文字列と判定する - 検索値側と検索範囲側の両方で同じ判定を行い、結果が食い違っていないか確認する
片方だけ型を合わせて回避する場合の書き方です。
=XLOOKUP(D1&"", A2:A100, B2:B100, "該当なし")検索値に &"" を付けると文字列に変換され、文字列側の検索範囲と一致します。逆に検索範囲が数値で検索値が文字列なら、=XLOOKUP(VALUE(D1), ...) のように数値へ変換します。
ただし、これはあくまで応急処置です。恒久対策として、データ側の型を統一しておくことをおすすめします。
- 対象の列を選択する
- 「データ」タブの「区切り位置」をクリックする
- ウィザードで何も変更せず「完了」をクリックする
原因2：前後に空白が入っている
手入力や貼り付けの過程で、コードの末尾に半角スペースが混入することがあります。セル上では見えません。
=LEN(A2)で文字数を数え、想定より1文字以上多ければ空白混入を疑う=EXACT(A2,D1)がFALSEなら、見た目が同じでも内容が異なると判断する
数式側で回避するなら、検索範囲をTRIM関数で整えます。
=XLOOKUP(TRIM(D1), TRIM(A2:A100), B2:B100, "該当なし")
原因3：全角と半角が混ざっている
「ＡＢＣ」と「ABC」は別の文字列です。ASC関数で半角に揃えると一致するようになります。
=XLOOKUP(ASC(D1), ASC(A2:A100), B2:B100, "該当なし")
#N/Aを「該当なし」などの表示に変える
原因を潰したうえで、それでも該当データが存在しない行がある場合は、エラー表示のままにせず意味のある文言に置き換えます。方法は2つあります。
1つ目は、XLOOKUP第4引数の「見つからない場合」を使う方法です。
=XLOOKUP(105, A2:A4, C2:C4, "データなし")2つ目は、IFNA関数で包む方法です。
=IFNA(XLOOKUP(105, A2:A4, C2:C4), "データなし")XLOOKUP単体で完結するなら第4引数のほうが短く、数式も読みやすくなります。IFNAは、XLOOKUPの結果をさらに別の計算に渡すような入れ子構造のときに使うと管理しやすくなります。
なお、IFERRORではなくIFNAを選ぶ理由があります。IFERRORはすべてのエラーを握りつぶすため、後述する#VALUE!や#REF!といった設定ミス由来のエラーまで隠してしまいます。「該当データがない」という業務上の正常ケースだけを処理したいなら、IFNAが適切です。
#VALUE!：検索範囲と戻り配列のサイズ・向きが違う
XLOOKUPは、検索範囲と戻り配列の次元が対応していないときに#VALUE!を返します。行数が違う場合と、縦方向と横方向が混在している場合の2パターンがあります。
行数が違う例です。
=XLOOKUP(A2, B2:B10, C2:C5)検索範囲が9行、戻り配列が4行で対応が取れません。次のように揃えます。
=XLOOKUP(A2, B2:B10, C2:C10)向きが違う例は、検索範囲が横1行（B1:K1）なのに戻り配列が縦1列（A2:A11）といったケースです。この場合は戻り配列も横方向（B2:K2など）に揃えます。
この誤りは、後から表に行を追加したときに起こりがちです。検索範囲だけ広げて戻り配列を広げ忘れる、という手順漏れが典型です。表全体をテーブル（「挿入」タブの「テーブル」）に変換しておくと、行追加時に参照が自動で追従するためサイズ不一致そのものが起きにくくなります。
#REF!：参照が無効になっている
#REF!は、数式が無効なセルを参照しているときに表示されます。XLOOKUPで遭遇する原因は主に2つです。
原因1：参照していた行や列を削除した
数式が参照していた列を削除すると、参照先が失われて#REF!になります。削除直後に気づいたなら「Ctrl」＋「Z」で元に戻すのが最も確実です。
時間が経ってから発見した場合は、数式内の #REF! という文字列を正しいセル範囲に手で書き直します。
- エラーのセルを選択し、数式バーで
#REF!の位置を確認する - 該当箇所を削除し、正しい範囲をドラッグで選択し直す
- 「Enter」で確定する
原因2：閉じた別ブックを参照している
見落としやすいのがこちらです。Microsoftの公式解説では、動的配列関数で閉じたブックを参照すると#REF!が発生すると明記されています。XLOOKUPもこれに該当します。
VLOOKUPが閉じたブックを参照できるのに対し、XLOOKUPは参照先ブックが閉じていると計算できません。共有サーバー上のマスタファイルを参照している運用では、朝一番に開いた瞬間に集計欄が一斉に#REF!になる、という形で表面化します。
対処の選択肢は次のとおりです。
- 参照先のブックを開いた状態で再計算する
- マスタデータを同じブック内のシートにコピーし、外部参照そのものをなくす
- 外部参照が必須なら、閉じたブックでも動作するVLOOKUPまたはINDEX+MATCHに置き換える
#SPILL!：結果を書き出す先が空いていない
XLOOKUPは、戻り配列に複数列を指定すると結果を複数セルに書き出します（スピル）。この書き出し先が空いていないと#SPILL!になります。
=XLOOKUP(B2, B5:B14, C5:D14)この数式は2列分の結果を返すため、右隣のセルも空である必要があります。
Microsoftの解説によれば、#SPILL!の主な原因は次のとおりです。
- 書き出し先のセルに既存のデータが入っている
- 書き出し先に結合セルが含まれている
- 数式をテーブル内に入力している（テーブル内ではスピルがサポートされない）
該当セルを選択すると破線の枠線が表示され、どこへ書き出そうとしているかが分かります。エラーチェックのアラートから「妨げているセルの選択」を選ぶと、原因のセルへ直接ジャンプできます。
結合セルが原因の場合は、「ホーム」タブの「セルを結合して中央揃え」をクリックして結合を解除します。テーブル内が原因の場合は、「テーブルデザイン」タブの「範囲に変換」で通常の範囲に戻すか、数式をテーブル外へ移動します。
#NAME? と「_xlfn.XLOOKUP」：Excelのバージョンが対応していない
数式に間違いがないのに#NAME?が出る、数式バーを見ると _xlfn.XLOOKUP と表示されている——この場合、数式の問題ではありません。使用中のExcelがXLOOKUPを実装していないことを示しています。
Microsoftの公式解説では、_xlfn. という接頭辞は、実行中のExcelがサポートしていない関数がブックに含まれているときに表示されるものと説明されています。
XLOOKUPが使えるバージョン
| バージョン | XLOOKUP |
|---|---|
| Microsoft 365 | 使える |
| Excel 2024 | 使える |
| Excel 2021 | 使える |
| Excel 2019 | 使えない |
| Excel 2016 | 使えない |
XLOOKUP関数の公式ページには、Excel 2016およびExcel 2019では使用できないと明記されています。同ページは同時に、新しいバージョンで作成されたブックをExcel 2016／2019で開く状況が起こりうる、とも注意を促しています。社内でOfficeのバージョンが混在している環境では、まさにこれが#NAME?の正体です。
Office 2016とOffice 2019は2025年10月14日にサポートが終了しています。それでも永続ライセンス版を使い続けている端末は残っているため、この問題は現在も発生します。
自分のバージョンを確認する
- Excelで「ファイル」タブをクリックする
- 左メニューの「アカウント」をクリックする
- 「製品情報」欄に表示されている製品名とバージョンを確認する
対処：INDEX+MATCHに置き換える
相手の環境をアップグレードできないなら、XLOOKUPを使わない数式に書き換えるのが確実です。置き換え方は後述のINDEX+MATCHの章で解説します。
配布前にチェックしておきたい場合は、互換性チェックを実行します。
- 「ファイル」タブをクリックする
- 「情報」の「問題のチェック」をクリックする
- 「互換性チェック」をクリックし、表示された非互換項目を確認する
【最重要】エラーが出ないのに答えが間違う3パターン
ここまでのエラーは、少なくとも画面に表示されるだけまだ親切です。実務で本当に危険なのは、もっともらしい数字が返ってきてしまうケースです。請求書や在庫表でこれが起きると、検算するまで誰も気づきません。
パターン1：検索モード2／-2を並べ替えていないデータに使う
XLOOKUPの第6引数「検索モード」には、2（昇順前提のバイナリ検索）と-2（降順前提のバイナリ検索）を指定できます。高速化のテクニックとして紹介されることがある設定です。
しかしMicrosoftの公式ドキュメントは、検索モード2および-2について、並べ替えられていない場合は無効な結果が返されると明記しています。エラーではなく、無効な値がそのまま返る点が問題です。
=XLOOKUP(D1, A2:A1000, B2:B1000, "該当なし", 0, 2)この数式は、A2:A1000が昇順に並んでいる場合にのみ正しく動きます。並べ替えが崩れていると、静かに誤った行の値を返します。データが後から追記されて並び順が崩れるケースは珍しくないため、リスクは常に残ります。
安全策はシンプルです。
- 第6引数を省略する（既定の1＝先頭から順に検索が適用される）
- どうしても2／-2を使うなら、参照するデータ範囲を必ずテーブル化し、並べ替え状態を維持する運用を決めておく
パターン2：一致モード1／-1で「近い値」が返る
第5引数「一致モード」に1を指定すると完全一致がないとき次に大きい項目が、-1を指定すると次に小さい項目が返ります。税率表や送料表のように区分値を引く用途では正しい使い方ですが、商品コードや社員IDのように完全一致が前提の検索でこれを指定すると、別のレコードの値が返っても一切警告されません。
=XLOOKUP(D1, A2:A100, B2:B100, "該当なし", -1)コード検索で存在しないコードを渡すと、この数式は「該当なし」ではなく、直前のコードの値を返します。完全一致が前提なら、一致モードは省略するか0を明示してください。
=XLOOKUP(D1, A2:A100, B2:B100, "該当なし", 0)パターン3：範囲を固定せずに数式をコピーする
数式を下方向にコピーすると、相対参照の範囲も一緒にずれていきます。ずれた先に偶然一致するデータがあれば誤った値が返り、なければ#N/Aになります。前者は気づけません。
検索範囲と戻り配列は、必ず絶対参照で固定します。
=XLOOKUP(D2, $A$2:$A$100, $B$2:$B$100, "該当なし")範囲部分を選択して「F4」キーを押すと、$が自動で付きます。テーブル化して構造化参照（商品マスタ[コード] のような書き方）にしておけば、そもそもコピーでずれません。
補足：空欄のはずが「0」と表示される
参照先のセルが空白の場合、XLOOKUPは0を返します。備考欄や日付欄で「0」が並ぶのはこれが原因です。空白のまま表示したいときはIF関数で判定します。
=IF(XLOOKUP(D2,$A$2:$A$100,$B$2:$B$100,"")="","",XLOOKUP(D2,$A$2:$A$100,$B$2:$B$100,""))文字列として扱ってよいなら、末尾に &"" を付けるだけでも空白表示になります。ただし戻り値が数値の場合は文字列化されるため、合計計算に使う列では避けてください。
XLOOKUPの代替：INDEX+MATCH（速度ではなく互換性のために使う）
INDEX+MATCHは、Excel 2019以前を含むすべてのバージョンで動作します。XLOOKUPが使えない環境へブックを配布するときの、実質的な唯一の選択肢です。
基本の書き方
=INDEX(戻り範囲, MATCH(検索値, 検索範囲, 0))商品コードから価格を取り出す例です。
| A列（商品コード） | B列（商品名） | C列（価格） |
|---|---|---|
| A101 | りんご | 100 |
| A102 | みかん | 120 |
| A103 | バナナ | 150 |
| A104 | ぶどう | 200 |
=INDEX(C2:C5, MATCH("A103", A2:A5, 0))結果は150になります。
ここで絶対に省略してはいけないのが、MATCHの第3引数「0」です。MATCHは第3引数を省略すると1（昇順前提の近似一致）として扱われます。並べ替えられていないデータに対して近似一致を行うと、XLOOKUPの検索モード2と同じく、エラーを出さずに誤った位置を返します。
XLOOKUPからの書き換え対応表
| XLOOKUP | INDEX+MATCH |
|---|---|
| =XLOOKUP(D1,A2:A100,B2:B100) | =INDEX(B2:B100,MATCH(D1,A2:A100,0)) |
| =XLOOKUP(D1,A2:A100,B2:B100,”該当なし”) | =IFNA(INDEX(B2:B100,MATCH(D1,A2:A100,0)),”該当なし”) |
| =XLOOKUP(D1,A2:A100,B2:B100,,,-1)（末尾から検索） | =LOOKUP(2,1/(A2:A100=D1),B2:B100) |
IFNA関数はExcel 2013以降で使えます。それより古い環境も想定するなら、IFERRORで代用します。
INDEX+MATCHとVLOOKUPの比較
| 項目 | INDEX+MATCH | VLOOKUP |
|---|---|---|
| 検索方向 | 検索列より左の列も取得できる | 検索列より右の列のみ |
| 列の挿入・削除 | 参照が自動追従する | 列番号がずれて誤った値を返すことがある |
| 対応バージョン | すべて | すべて |
| 数式の読みやすさ | 関数が入れ子になり長くなる | 短くて分かりやすい |
VLOOKUPの列番号ずれも、エラーを出さずに隣の列の値を返す典型的な「黙って間違う」パターンです。列の増減が想定される表では、INDEX+MATCHのほうが安全に運用できます。
複数条件で検索する
商品コードと色の2条件で価格を引く例です。
| A列（商品コード） | B列（色） | C列（価格） |
|---|---|---|
| A101 | 赤 | 100 |
| A101 | 青 | 110 |
| A102 | 赤 | 120 |
| A102 | 青 | 130 |
=INDEX(C2:C5, MATCH(1, (A2:A5="A101")*(B2:B5="青"), 0))結果は110になります。条件式の掛け算で、両方を満たす行だけが1になる仕組みです。
動的配列に対応していないExcel 2019および2016では、この数式は「Ctrl」＋「Shift」＋「Enter」で確定して配列数式にする必要があります。Microsoft 365やExcel 2021以降では、通常の「Enter」だけで動作します。
XLOOKUPが使える環境なら、同じことを次のように書けます。
=XLOOKUP(1, (A2:A5="A101")*(B2:B5="青"), C2:C5, "該当なし")「XLOOKUPが遅い」ときに本当に効く対策
「XLOOKUPは遅いのでINDEX+MATCHに変えると速くなる」という説明を見かけますが、Microsoftはこの2つの速度差について公式な見解を示していません。INDEX+MATCHへの置き換えは、あくまで互換性を確保するための手段と考えてください。
計算が重いときに効果が期待できるのは、関数の選択ではなく参照範囲と再計算の設計です。Microsoftの計算パフォーマンスに関するドキュメントでは、完全一致の検索を行う際は走査する範囲を最小限に絞り、大量の行や列をそのまま参照するのではなく、テーブルの構造化参照や動的な範囲名を使うことが推奨されています。
実務で試す順序は次のとおりです。
- 検索範囲を
A:Aのような列全体指定からA2:A5000のような実データ範囲に絞る - データ範囲をテーブルに変換し、構造化参照で記述する
- 1セルずつXLOOKUPを入力するのをやめ、1つの数式でスピルさせて複数行を一括取得する
- 入力作業中だけ「数式」タブの「計算方法の設定」を「手動」に切り替え、確定後に「F9」で再計算する
それでも改善しない場合は、XLOOKUPの数式が数万個並んでいること自体がボトルネックです。Power QueryのマージやピボットテーブルなどExcelの別機能に処理を移すほうが、効果が大きくなります。
よくある質問
XMATCH関数とXLOOKUPはどう使い分けますか
XLOOKUPは「値そのもの」を返し、XMATCHは「何番目にあるか」という位置を返します。取り出したい列が複数あって、位置を一度だけ求めて使い回したい場合はXMATCHが有利です。単純に値が欲しいだけならXLOOKUPで十分です。XMATCHもXLOOKUPと同じくExcel 2019および2016では使えません。
XLOOKUPは大文字と小文字を区別しますか
区別しません。”abc”で検索すると”ABC”にも一致します。厳密に区別したい場合はEXACT関数と組み合わせます。
=XLOOKUP(TRUE, EXACT(A2:A100, D1), B2:B100, "該当なし")スピルした結果を別のセルから参照するには
スピル範囲演算子「#」を使います。C2にスピルする数式が入っている場合、=SUM(C2#) と書くと、スピル範囲全体が自動的に対象になります。行数が増減しても参照を修正する必要がありません。
同じブックでXLOOKUPとVLOOKUPを混在させても問題ありませんか
計算上の問題はありません。ただし、Excel 2019以前で開く可能性があるブックでは、XLOOKUPの部分だけが#NAME?になり、表の一部が壊れた状態になります。配布用のブックは、どちらか一方に統一しておくほうが安全です。
まとめ
XLOOKUPのトラブルは、症状から原因を絞り込めば確実に解決できます。
- #N/Aはデータ型・空白・全角半角の不一致を疑い、解決後にIFNAまたは第4引数で表示を整える
- #VALUE!は検索範囲と戻り配列のサイズと向きを揃える
- #REF!は削除された参照か、閉じた別ブックへの参照を確認する
- #SPILL!は書き出し先の既存データ・結合セル・テーブルを解消する
- #NAME?と「_xlfn.XLOOKUP」はバージョン非対応のサインで、INDEX+MATCHへの置き換えが必要になる
そして最も注意すべきは、エラーが表示されないケースです。検索モード2／-2を並べ替えていないデータに使う、完全一致が前提なのに一致モード1／-1を指定する、範囲を絶対参照で固定せずにコピーする——この3つは、いずれも警告なしに誤った値を返します。
迷ったら、一致モードと検索モードは指定せず、範囲は絶対参照かテーブルで固定する。この2点を守るだけで、XLOOKUPのトラブルの大半は起きなくなります。
コメント