Excel XLOOKUPが#N/Aになる原因を、AIと一緒に切り分ける手順

先に確認する3点

見た目の差を探す前に、設定の問題を除外します。

• Excelの版:XLOOKUPはExcel 2016・2019では使えません。使えるのはExcel 2021以降とMicrosoft 365です(上記Microsoftの説明)。共有相手の版が古いと、自分の画面では動いても相手側で動かないことがあります。
• 一致モード:省略時は完全一致(0)です。-1・1は「完全一致、なければ次に小さい/大きい項目」、2はワイルドカード一致で、*・?・~が特別な意味を持ちます。意図しない値を指定していないか見ます。
• 範囲のずれ:数式をコピーして検索範囲が動いていないか、行を追加して範囲が足りなくなっていないかを確認します。

見た目で分からない差を、補助式で調べる

Microsoftの#N/A解説でも、よくある原因として「データ型の不一致」「余分なスペース」「完全一致と近似一致の混在」「範囲の不一致」が挙げられています(エラー値 #N/A を修正する方法、VLOOKUP関数の#N/Aエラーを修正する方法)。以下は、これらを切り分けるために編集部で組んだ補助式です。

前提(説明用の例)

• 検索値:E2
• 検索範囲:A2:A100(商品コードの列)
• 戻り配列:B2:B100
• 元の数式:=XLOOKUP(E2, A2:A100, B2:B100)が#N/Aになる

補助式は、作業用の空きセルに1つずつ入力します。XMATCHと配列の計算を使うため、Excel 2021以降かMicrosoft 365を想定しています。

補助式(説明用・未実行)

(1) 型の確認
=ISNUMBER(E2)
=COUNT(A2:A100)                  ← 数値のセルの数
=COUNTA(A2:A100)-COUNT(A2:A100)  ← 数値以外のセルの数

(2) スペースの確認
=LEN(E2)
=LEN(TRIM(E2))                   ← LEN(E2)と違えば、端や連続したスペースあり

(3) 端の文字の種類
=UNICODE(LEFT(E2,1))
=UNICODE(RIGHT(E2,1))            ← 32は半角スペース、160は改行なしスペース、
                                    12288は全角スペース

(4) 型と半角スペースをならして比較し、一致する行数を数える
=SUM(--(TRIM(A2:A100&"")=TRIM(E2&"")))

(5) 改行なしスペースもならした上で、位置が返るか
=XMATCH(TRIM(SUBSTITUTE(E2&"",UNICHAR(160)," ")),
        TRIM(SUBSTITUTE(A2:A100&"",UNICHAR(160)," ")))

(6) 行数がそろっているか
=ROWS(A2:A100)=ROWS(B2:B100)

TRIMが消すのは半角スペース(文字コード32)だけで、改行なしスペース(160)は単独では消えません。そのため(5)で先に置き換えています。全角スペースが混じる場合は、(5)にUNICHAR(12288)の置換も足してください。XMATCHは位置を返す関数です(XMATCH関数)。見つからなかったときのエラーの種類は、公式ページでは確認できていません。(3)〜(6)の組み立ては公式ページの記述ではなく編集上の整理です。

結果の読み方

• (1)で検索値は文字列、範囲は数値(またはその逆)、疑う原因：データ型の不一致、次にやること：どちらかにそろえる
• (2)でLENとLEN(TRIM)が違う、疑う原因：先頭・末尾などの半角スペース、次にやること：元データから除く
• (3)で160や12288、疑う原因：特殊なスペース(TRIMでは消えない)、次にやること：置換して除く
• (4)が1以上なのに元の式は#N/A、疑う原因：型または半角スペースの差、次にやること：(1)(2)で種類を特定
• (4)が0で、(5)は位置が返る、疑う原因：改行なしスペースが原因、次にやること：元データを直す
• (4)が0で、(5)も位置が返らない、疑う原因：文字そのものが違う、または本当にデータがない、次にやること：表記ゆれ・入力漏れを確認
• (6)がFALSE、疑う原因：検索範囲と戻り配列の行数差、次にやること：同じ行数にそろえる

(4)(5)がともに0なのに目で見ると同じ値がある場合は、ハイフンの種類(-と‐)、Oと0、全角英数字と半角英数字といった別の文字を疑います。AIに文字コードを並べて比べさせると見つけやすくなります。

原因別の直し方チェックリスト

作業前に、元のブックをコピーしてください。

□ 型の不一致

• 検索値が文字列で範囲が数値なら、VALUE(E2)で数値にします。逆ならE2&""で文字列にします。先にスペースを除かないとVALUEは失敗することがあります。
• 列全体をそろえたいときは、Microsoftが紹介する[データ]→[テキストを列]を使う方法があります。先に目的の書式を適用してから使います。表示形式を変えただけでは中身が変わらないことがあるため、直した後に(1)で再確認します。

□ 余分なスペース

• 数式側でTRIMを使う方法(Microsoftの解説にもあります)か、元データ側で除去します。同じ表を何度も使うなら、元データを直す方が数式が単純で済みます。

□ 特殊なスペース(改行なしスペース、全角スペース)

• SUBSTITUTEで半角スペースに置き換えてからTRIMをかけます((5)の形)。

□ 範囲・引数のずれ

• 行の追加に備えて余裕のある範囲にするか、表(テーブル)にします。
• 検索範囲と戻り配列の行数をそろえます。
• 完全一致が目的なら、一致モードは省略または0にします。

□ データが本当にない

• 表記ゆれや新規追加の漏れなら、数式ではなくデータ側を直します。

□ 古いExcel

• 版を更新するか、別の関数に置き換えます。

AIに渡す依頼文と、役割分担

AIはブックの中身を見られません。補助式の結果を渡すのが近道です。実データの名前・金額・個人情報は、構造が同じ架空の値に置き換えてから渡してください。AIサービスの学習設定は秘密保持と同じではないので、置き換えは設定にかかわらず行う方が安全です。

依頼文の例

次の値は、全部が説明用の仮のものです(実際の出力結果ではありません)。検索値が「A-001+末尾に改行なしスペース」の場合を想定しています。

ExcelのXLOOKUPが#N/Aになる原因を切り分けたいです。
環境:Microsoft 365のExcel
数式:=XLOOKUP(E2, A2:A100, B2:B100)
検索値E2の例:「A-001」(実際の値を置き換えたもの)
検索範囲の先頭3行の例:A-001 / A-002 / A-003

補助式の結果:
(1) ISNUMBER(E2)=FALSE、COUNT(A2:A100)=0
(2) LEN(E2)=6、LEN(TRIM(E2))=6
(3) UNICODE(RIGHT(E2,1))=160
(4) 結果=0
(5) 結果=1
(6) 結果=TRUE

お願い:
・この結果から考えられる原因を可能性の高い順に挙げてください
・それぞれ、確認する追加の式を1つずつ示してください
・元データを直す方法と、数式で吸収する方法を分けて書いてください

任せる範囲と、人が確認する範囲

• AIに任せる:結果の読み取り、原因の候補出し、追加確認の式の下書き。
• 人が確認する:セル参照が自分の表に合っているか、直した後に正しい行が返っているか。AIの示す式は、必ずコピーしたブックで試します。
• 注意:AIが原因を断定しても、補助式の結果と食い違うなら採用しません。手元の結果が優先です。

直した後の確認

1. #N/Aだったセルで、期待した戻り値が出ているか。
2. 別の2〜3行でも、正しい行の値が返っているか。
3. [見つからない場合]の引数やIFERRORで、エラーを隠していないか。

特に3が重要です。切り分けの途中でエラーを隠すと、原因が残ったまま空欄や0が表示されて気づけなくなります。原因を直してから、必要なら最後に追加してください。