Excel約6分で読めます

ExcelのVLOOKUPが#N/Aになる|全角半角・末尾スペース・数値と文字列を順に確認

ExcelのVLOOKUPが#N/Aになるを表したイメージ
記事の内容を表したイメージ(AI生成)

VLOOKUPの数式を入れたのに、セルが#N/Aになって困っている場合、数式の書き方より先に「検索値と表のデータが本当に一致しているか」を疑う必要があります。見た目は同じなのにエラーになるケースのほとんどは、全角・半角の違い、末尾に紛れ込んだスペース、数値と文字列の型の不一致、この三つのどれかが原因です。

この記事では、#N/Aが出たときに何から確認するか、どの関数で原因を切り分けるか、そして直し方の手順を順番に説明します。範囲の指定ミスや絶対参照の有無など数式の構造的な間違いはいったん脇に置き、「データが揃っているはずなのに一致しない」という場面に絞って扱います。


なぜ見た目が同じでも一致しないのか

Excelは文字や数値の比較をするとき、見た目ではなく内部のデータをそのまま照合します。「A001」という文字列があったとして、片方が全角の「A001」であれば、画面上では似て見えても別の文字としてあつかわれます。末尾に半角スペースが一つ入っていても同様で、「A001」と「A001 」(スペースあり)は一致しません。数値の1と、文字列として入力された"1"も異なるデータです。

これらの違いはセルを目で見ただけでは気づきにくく、特にシステムからエクスポートしたデータや、複数人が入力を分担したファイルで起きやすいです。「自分で打った表には出ないのに、もらったデータを使うとエラーになる」という状況は、多くの場合この三つのどれかです。


原因を切り分ける順番

エラーの原因を探すときは、次の順番で確認すると効率よく絞り込めます。

  1. 数値と文字列の型の不一致を確認する
  2. 末尾スペースを確認する
  3. 全角・半角の違いを確認する

型の不一致が最も気づきにくく、修正の方向が逆になると直らないため、最初に確認します。スペースは型が合っていても残ることがあるため次に、全角・半角は型とスペースを直しても残るケースがあるため最後に見ます。


確認1:数値と文字列の型を見分ける

セルの左寄せ・右寄せがざっくりした目安になります。Excelは数値を既定で右寄せ、文字列を左寄せで表示します。ただし書式を手動で変えていると当てにならないため、ISNUMBER関数で確認する方が確実です。

=ISNUMBER(A2)

このようにセルを指定してTRUEが返れば数値、FALSEなら文字列(または空白)として格納されています。

架空の例です。商品コードが入った表で、参照元のA2が1001(数値)、参照先のコード列が'1001(先頭にアポストロフィを付けて文字列として入力)だとします。見た目は両方とも「1001」ですが、ISNUMBERで確認すると片方がFALSEになります。

数値を文字列に合わせる場合:

=VLOOKUP(TEXT(A2,"0"),参照範囲,列番号,FALSE)

TEXT(A2,"0")は数値を文字列に変換します。参照先が文字列で格納されているときに使います。

文字列を数値に合わせる場合:

=VLOOKUP(VALUE(A2),参照範囲,列番号,FALSE)

VALUEは文字列の数字を数値に変換します。参照先が数値で格納されているときに使います。

どちらに合わせるかは、参照先の表(第2引数で指定している範囲)の型に合わせるのが基本です。元のファイルを変更する前に、必ずコピーをバックアップとして残してから作業してください。


確認2:末尾(と先頭)のスペースを取り除く

スペースが紛れていないかを調べるにはLEN関数が使えます。文字数を数えて、見た目の文字数と合っているかを確認します。

架空の例です。コード「A001」は4文字のはずなのに、あるセルで=LEN(B2)が5を返したとします。この場合、想定より1文字多いことが分かります。スペースとは限らないため、余分な文字の種類と位置を確認します。

スペースを取り除くにはTRIM関数を使います。TRIMは先頭・末尾のスペースをすべて除去し、文字間の連続スペースは1文字に詰めます。

=VLOOKUP(TRIM(A2),参照範囲,列番号,FALSE)

検索値側にTRIMをかけるだけでなく、参照先の列にもスペースが混入している場合があります。その場合は参照先のデータ自体を修正するか、VLOOKUP以外の方法(後述)を検討します。

参照先のデータを一括修正するには、別の列に=TRIM(元の列のセル)を入力して値を作り、それをコピーして「値のみ貼り付け」で元の列に上書きする方法があります。個人情報が含まれるファイルの場合、外部のサービスや生成AIへの貼り付けは行わず、Excelの操作だけで完結させてください。

全角スペースは`TRIM`で取れない点に注意してください。 全角スペース( )が入っている場合はSUBSTITUTEと組み合わせます。

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

SUBSTITUTE(A2," ","")の「 」の部分には全角スペースを入力します。これで全角スペースを除去してからTRIMをかけます。


確認3:全角・半角の違いを統一する

全角・半角の混在を調べるには、EXACT関数で二つのセルが完全一致しているかを確認する方法があります。

=EXACT(A2,B2)

TRUEなら完全一致(全角・半角も含めてすべて同じ)、FALSEなら何か違いがあります。どの文字が違うかまでは教えてくれませんが、「見た目は合っているのにFALSEになる」なら全角・半角の差が残っている可能性が高いです。

半角に統一するにはASC関数を使います。全角の英数字・記号・カタカナを半角に変換します。

=VLOOKUP(ASC(A2),参照範囲,列番号,FALSE)

逆に全角に統一するにはJIS関数を使います。

=VLOOKUP(JIS(A2),参照範囲,列番号,FALSE)

どちらに統一するかは、参照先の表に合わせます。参照先が半角で格納されていれば検索値にASCをかけ、全角であればJISをかけます。

カタカナの全角・半角変換には注意点があります。 ASCはカタカナも半角カタカナに変換します。顧客名や商品名に全角カタカナを使っているデータでは、変換後に意図しない形になることがあります。変換後のデータは必ず目視で確認してください。


三つをまとめて数式に入れる

原因が複合している場合(型は合っているが、スペースと全角半角が両方ある、など)は、関数を組み合わせます。

検索値を文字列として扱い、スペース除去と半角統一を同時に行う例です。

=VLOOKUP(ASC(TRIM(A2)),参照範囲,列番号,FALSE)

数値として格納されている検索値に対しては、先に型を合わせてからスペースや全角の処理を行います。参照先が文字列の場合の例です。

=VLOOKUP(ASC(TRIM(TEXT(A2,"0"))),参照範囲,列番号,FALSE)

ただし数式が長くなるほど、どこで何をしているかが追いにくくなります。作業列を一つ設けて、まず変換後の値を別のセルに出力し、それをVLOOKUPの検索値に使う方が、後で確認・修正しやすくなります。

架空の例です。E列を作業列とし、=ASC(TRIM(TEXT(A2,"0")))で変換後のコードを出力、VLOOKUPでは=VLOOKUP(E2,参照範囲,列番号,FALSE)と書く、という分け方です。数式を短く保てるうえ、E列を見るだけで変換結果の確認ができます。


次の行動

今日#N/Aが出ているセルについて、まず=ISNUMBER(検索値のセル)と=ISNUMBER(参照先の先頭セル)の結果を並べて確認してください。型が揃ったらLENでスペースを確かめ、それでも一致しなければEXACTで全角・半角を疑います。原因が特定できたら、この記事の該当箇所の数式を作業列で試してから、元のVLOOKUPに組み込んでください。作業前にファイルのコピーを必ず取っておくことをおすすめします。

参考資料

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

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

記事一覧へ