Excel約5分で読めます

ExcelのXLOOKUPで「見つかりません」が出る|検索値と範囲のずれを元データで確かめる

ExcelのXLOOKUPで「見つかりません」が出るを表したイメージ
記事の内容を表したイメージ(AI生成)

XLOOKUPの数式を入れたのに、確かに存在するはずの値が「見つかりません」と返ってくる場合、数式の書き方より先に疑うべきことがあります。それは「検索値と検索範囲のデータが、見た目は同じでも中身が違う」という状態です。

この記事では、XLOOKUPが一致を見つけられない原因のうち、元データのずれに起因するものに絞って説明します。スペースや文字コードの違い、数値と文字列の混在、余分な改行など、目視ではわかりにくいずれを確かめる手順と、修正の判断基準を示します。


なぜ「見つかりません」が出るのか

XLOOKUPは、検索値と検索範囲のセルを一文字ずつ比較します。見た目が同じ「A001」でも、一方に全角スペースが混じっていたり、片方が数値型で片方が文字列型だったりすると、一致と判断されません。

既定の一致モード(完全一致)では、わずかな差でも「見つからない」として第4引数の値(省略時は #N/A)を返します。エラーではなく設計どおりの動作なので、数式を直しても解決しません。元データを直すか、データのばらつきを吸収する数式に変える必要があります。


確認の前にファイルをコピーする

元データを変更する作業が伴うため、作業前に必ずファイルを別名でコピーして保存してください。「ファイル名_作業前_日付.xlsx」のように名前をつけると、戻りたいときに迷いません。個人情報を含む名簿などを外部サービスに貼り付けて確認する場合は、所属する組織のルールを先に確認してください。


原因を絞り込む4つの確認手順

手順1:データ型(数値か文字列か)を確かめる

セルを選択したとき、数値は右揃え、文字列は左揃えで表示されるのが既定の動作です(書式設定によって変わることがあるため、あくまで目安です)。

確実に確認するには TYPE 関数を使います。

=TYPE(A2)

返り値が 1 なら数値、2 なら文字列です。検索値と検索範囲の先頭セルの両方で確認し、結果が一致しているかを見てください。

架空の例(架空の例です):

表は横にスクロールできます

セル内容(見た目)TYPE の結果判断
検索値 B210011(数値)
検索範囲 A210012(文字列)← 型が違う

この場合、どちらかに型を合わせます。検索範囲が文字列なら検索値も文字列に変換するか、検索範囲を数値に修正します。

  • 検索値を文字列に変換する:=XLOOKUP(TEXT(B2,"0"), A:A, C:C)
  • 検索範囲の文字列を数値に変換する:列全体を選択→「テキストを列に分割」などで再変換(画面の表示は版や設定により異なります)

例外的に迷うケース:商品コードが「0001」のようにゼロ始まりの場合、数値に変換するとゼロが消えて「1」になります。この場合は両方を文字列に統一するのが安全です。


手順2:余分なスペースや改行を調べる

LEN 関数でセルの文字数を比較します。

=LEN(A2)

見た目が同じでも文字数が違えば、どちらかに余分な文字が入っています。

架空の例(架空の例です):

  • 検索値「田中 花子」→ LEN の結果:5
  • 検索範囲「田中 花子」→ LEN の結果:5(全角スペースも1文字)

この例では文字数は同じです。LENだけでは半角・全角の違いを判別できません。

スペースの種類(半角・全角)は目視で判別しにくいです。TRIM 関数は先頭・末尾の半角スペースと単語間の重複スペースを除去しますが、全角スペースは除去しません。全角スペースも除去したい場合は SUBSTITUTE を組み合わせます。

=TRIM(SUBSTITUTE(A2," "," "))

上の数式の第2引数 " " は全角スペース(U+3000)、第3引数 " " は半角スペース(U+0020)です。数式をコピーする際に文字が変わることがあるため、実際に全角スペースと半角スペースを直接入力して確認してください。

改行(セル内改行)が混入している場合は LEN の結果が1増えています。CLEAN 関数で改行など印刷されない制御文字を除去できます。

=CLEAN(A2)

手順3:文字コードレベルで一致しているか確かめる

見た目も文字数も同じなのに一致しないケースは、文字コードが異なる場合があります。たとえば、記号のハイフン(-)と長音符(ー)、全角ダッシュと半角ハイフンなどは、フォントによって見た目が近くなります。

CODE 関数で先頭1文字のコード番号を取得して比較します。

=CODE(LEFT(A2,1))

検索値と検索範囲の先頭文字のコード番号が一致しているかを確認します。違う場合は、どちらかの文字を SUBSTITUTE で置き換えて統一します。


手順4:検索範囲の列がずれていないか確かめる

数式自体のミスですが、意外に見落としやすいです。縦方向のXLOOKUPでは、検索範囲と戻り範囲の行数が合わないとエラーになります。行数が同じでも開始行がずれていると別の行の値を返すため、行数と開始行の両方を確認します。

=XLOOKUP(検索値, 検索範囲, 戻り範囲)

検索範囲と戻り範囲の行数を ROWS 関数で確認します。

=ROWS(A2:A100)
=ROWS(C2:C100)

両方の結果が一致していることを確認してください。


修正後に動作を確かめる

データを修正したら、まず1行だけで動作を確認します。修正前のデータと修正後のデータを隣の列に並べて LEN や TYPE で比較し、すべて一致してから全行に適用します。

修正方法として「数式で変換した結果を値貼り付けする」方法はよく使われますが、元のデータが上書きされるため、コピーをとってから行ってください。値貼り付けは「コピー→形式を選択して貼り付け→値」で実行します(画面の表示は版や設定により異なります)。


迷いやすい例外:一部の行だけ一致しない場合

全行が「見つかりません」になるのではなく、一部の行だけ一致しない場合は、その行のデータだけに特定の問題が混入していることが多いです。

架空の例(架空の例です):

申込一覧の氏名列に100件のデータがあり、98件は正常に返るが2件だけ #N/A になる。その2行の LEN を確認すると、他の行より1多い。該当行のデータの末尾に半角スペースが入っていた。

このような場合、問題のある行を特定するには LEN の結果をオートフィルタで並べ替えて、値が多い行を探すと効率的です。


次の行動

まずファイルを別名でコピーし、TYPE と LEN の2つの関数で検索値と検索範囲の先頭セルを確認してください。型と文字数が一致すれば、次に CODE で文字コードを比較します。一部の行だけ一致しない場合は、その行だけを取り出して同じ確認を行うと原因を絞り込めます。

参考資料

この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ