VLOOKUPで一致しているのに #N/A が出る原因と直し方

最初に数式の検索範囲を確認する

表に値が見えていても、そのセルが数式で指定した範囲の左端列に入っているかを先に確認します。 VLOOKUPは指定した範囲の最初の列から検索値を探し、見つかった行から指定した列の値を返します。ワークシート全体を検索するわけではありません。

たとえば、E2に商品コードを入れ、A2からB10の商品一覧から商品名を返す式は次のようになります。

=VLOOKUP(E2,$A$2:$B$10,2,FALSE)

E2は探す値、A2:B10は探す範囲、2は範囲の左から数えて何列目を返すかです。FALSEは完全一致を指定します。コードや会員番号のように、同じ値が見つかった行だけを使う場合は、最後の引数にFALSEを明示すると意図を確認しやすくなります。

行が増えて、範囲から外れていないか

商品一覧に新しい商品が追加され、探したいコードがA11にあるとします。数式がA2:B10を見ているままなら、画面上にコードが見えていても、A11は対象外です。列の型を変える前に、数式バーを開き、検索範囲の最初と最後を見てください。A11を含める必要がある場合は、A11の隣に商品名があることも確かめてから範囲を広げます。

範囲を広げるときは、同じ列の下に別の表や集計行がないかも確認します。対象を広げすぎると、たまたま同じコードが別の表にあり、別の商品名を返すことがあります。新しい行を継続的に追加する一覧なら、一覧の境界を分かるように整え、追加するたびに検索範囲がどこまで届くかを点検します。

検索する列が範囲の左端にあるか

表がA列に商品名、B列に商品コードという順の場合、A2:B10を指定したVLOOKUPが探すのはA列です。B列にコードがあっても、検索の対象にはなりません。コードが左、商品名が右に来る補助表を用意するか、検索列と戻り値の列を別々に指定できる方法を使います。元の並びを変えられない共有台帳なら、元表を直接並べ替える前に、別シートで確認してください。

また、コピーした式で一部の行だけが失敗するなら、動く行と失敗する行を一つずつ選び、参照範囲を見比べます。検索範囲に付くドル記号は、式をコピーしたときに参照が動かないようにする印です。検索値のセルは行ごとに動かし、一覧の範囲は固定するという設計なら、どちらにドル記号が付くかを分けて考えます。

数値と文字列の違いを調べる

同じ「1001」と表示されても、一方が数値、もう一方が文字列なら、完全一致の検索で見つからないことがあります。 見た目や右寄せ・左寄せだけで判断せず、調べたいセルに対して次の式を使います。検索値をE2、表側の候補をA2とした例です。

=ISNUMBER(E2)
=ISNUMBER(A2)
=ISTEXT(E2)
=ISTEXT(A2)

それぞれを別の空きセルに入れます。ISNUMBERがTRUEなら数値、ISTEXTがTRUEなら文字列です。たとえば、E2が数値でA2が文字列なら、まず「このコードは数字として扱うか、文字列として扱うか」を決めます。表側を変えるか検索側を変えるかは、その決め方に合わせます。

先頭のゼロに意味があるコードは文字列としてそろえる

商品コード「00123」と「123」が別物なら、数値に変換してしまうと、見分けるためのゼロが失われます。この場合は、正しい桁数や元の台帳を確認して、文字列としてそろえます。すでにゼロを落として取り込んだデータについては、見た目だけから元のコードを決めつけず、元の一覧と照合してください。

一方、コードの定義上、数値の123と文字列の「123」が同じものなら、文字列の数値を数値に変える方法があります。元の値を残すため、まず空き列で次の式を試します。

=VALUE(A2)

変換後にISNUMBERでTRUEを確認し、その補助列を検索範囲の左端に置いて試します。値の中に文字を含むコードが混ざっている場合、VALUEがエラーになることがあります。数値に変えられない値を空欄に置き換える前に、それが異常値か、正規の英数字コードかを確認します。

セルの表示形式と保存されている値を分けて考える

セルの表示形式を変えた直後に、見た目がそろったからといって、型まで変わったとは判断できません。たとえば、数値123を「00123」と表示しているセルと、文字列「00123」を保存しているセルは、画面では同じでも中身が異なります。表示を変えたあとはISNUMBER・ISTEXTで再確認します。

原因が分かりにくいときは、検索側と一覧側の両方を、先頭ゼロの有無が分かる見本で比べましょう。業務システムから出した一覧を毎月使うなら、「取り込み時にコードを文字列として保持する」など、次回の取り込み方法も合わせて記録しておくと、同じ原因を追いやすくなります。

空白や見えない文字を調べる

型がそろっているのに見つからない場合は、検索値と一覧の候補の文字数を比べます。 コード「AB12」の後ろに半角スペースが一つ付くと、画面上では区別しにくくても、文字数は4と5になります。文字列として扱うコードの例です。

=LEN(E2)
=LEN(A2)

同じに見えるのに文字数が違う場合は、どちらかに余分な文字がないかを確認します。数式バーで末尾を見たり、元データを出したシステムの出力形式を確かめたりします。ただし、文字数が同じ場合でも、全角・半角、別種類のハイフン、似た文字などが違う可能性は残ります。

半角スペースなら、補助列でTRIMを試す

半角スペースが前後に入っていると分かったら、元の列の隣に補助列を作り、次の式を使います。A2が元のコードで、B2に商品名がある想定なら、D2に式を入れて下へコピーし、E列に商品名を並べます。検索値をG2とする場合、検索結果をH2に置く例です。

=TRIM(A2)
=VLOOKUP(TRIM(G2),$D$2:$E$10,2,FALSE)

TRIMは前後の半角スペースを取り除き、文字列の途中に連続した半角スペースがあれば一つに整えます。コード内のスペースが区別に使われている一覧では、TRIMを適用した結果、別のコードが同じになってしまわないか確認してください。ここで示した式は、空白を整理してよい文字列コードを想定しています。数値コードの型をそろえる手順とは分けて使います。

TRIMで直らなければ、取り除く文字を特定する

全角スペースなど、TRIMだけでは処理できない文字もあります。例えば、コードの前後に全角スペースが入っており、そのスペースを半角に置き換えてよいと確認できた場合は、補助列で以下を試せます。

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

この式は全角スペースを半角スペースに置き換えてから整理します。前後だけを対象にする式ではなく、途中の全角スペースも変わります。そのため、途中のスペースに意味がある場合には、そのまま使わず、入力元のルールを確認してください。

例えば、ウェブページから貼り付けた一覧と、基幹システムから取り込んだ一覧で、見えない空白の種類が異なることがあります。複数の除去関数を一度に重ねると、直った理由や元のコードとの差が分かりづらくなります。原因が分からない間は、補助列に元の値と処理後の値を並べ、変更したのがどの文字か説明できる状態にしてから、一覧全体に適用します。

直したあとに3つの値で確かめる

修正した式は、最初に困った値、別の正常な値、一覧に存在しない値の3つで確かめます。 #N/A が消えても、期待する商品名や担当者名が返っているとは限りません。結果は元一覧と照合します。

・最初にエラーが出た値。どの行が正解かを元一覧で確認してから、修正後の式を試します。

・別の正常な値。範囲の末尾や、先頭にゼロを持つ別コードなど、壊したくないケースも選びます。

・一覧にない値。意図したとおり見つからない扱いになるか確認します。

同じコードが複数行にあったらどうするか

前後の空白を削除した結果、異なる行が同じコードにそろう場合があります。VLOOKUPで何か値が返ってきても、同じコードのどの行を採用すべきかは、表の使い方に依存します。たとえば同じ商品に現行価格と過去価格の行があるなら、必要なのは単に一致するコードではなく、適用日や状態を含む条件かもしれません。

この場合、補助列でコードをそろえただけで完了とせず、対象を現行の行に絞るのか、適用日を別条件として扱うのかを決めます。正しい行が判断できなければ、元台帳の担当者に「この2行のどちらが報告対象ですか」と確認できるよう、候補を並べておきます。

エラー表示を変えるのは、原因を確認してから

#N/A が出る場所を空欄にすると、一覧にない値と、あるはずなのに参照できない値を見分けにくくなります。集計漏れの原因を調べている間は、検索結果を確認できる状態にします。業務上、一覧にない値を「未登録」と表示したい場合は、正常に検索できることを確認したあと、必要に応じて表示を整えます。

=IFNA(VLOOKUP(E2,$A$2:$B$10,2,FALSE),"未登録")

この式は、内側の検索が #N/A を返したときに「未登録」と表示します。原因が未解決の段階でも同じ表示になるため、この式を加えるだけでは原因調査になりません。報告の相手が「未登録」の意味をどう受け取るかも考え、例えば「照合できず」と表記するなど、運用に合わせます。

空きシートで #N/A を再現してみる

自分の表を直す前に、数値と文字列の違いだけを作った練習用の表で試すと、結果を比較しやすくなります。 新しいシートを作り、A2に数値の1001、B2に「ノート」、A3に数値の1002、B3に「ペン」を入れます。E2には、半角のアポストロフィを先頭に付けて ‘1001 と入力します。この入力では、E2に見える1001を文字列として用意できます。F2には次の数式を入れます。

=VLOOKUP(E2,$A$2:$B$3,2,FALSE)

この状態では、E2の1001とA2の1001は見た目が同じでも型が異なるため、#N/A が想定されます。次にE2を数値の1001へ入れ直し、F2に「ノート」が返るか確かめます。続けて数値の1002で「ペン」、一覧にない9999で #N/A になるかを見ます。もし1001が戻らなければ、E2とA2の両方をISNUMBERで確認し、F2がA2:B3を参照しているか見直してください。

この練習では対象の2行と正しい商品名が分かっています。実際の台帳でも同じように、数式を変更する前に「このコードならこの値が返る」と確認できる行を選んでおくと、表示だけが直った状態を見分けられます。

よくある質問

Q. 列全体を数値に変えれば早いですか？ 先頭ゼロや英字が識別に使われていないかを確認します。コードが文字列として定義されているなら、正規の桁や記号を保って文字列にそろえます。最初は元の列を残し、補助列で変換した結果を見比べると戻せます。

Q. 表に値が見えるのに、数式をコピーした一部の行だけ失敗します。 動く行と失敗する行の数式を開き、検索値のセルと一覧の範囲を比べます。範囲がコピーに伴って移動しているなら、一覧の参照だけを固定する方針で式を組み直します。元の式を控え、正常だった行も再度確認してください。

Q. 検索できたのに、違う商品の名前が返るのはなぜですか？ まず返す列番号と、検索の最後の引数を確認します。さらに同じコードが複数行ないか、空白を整理したことで別コードが重なっていないかを見ます。どの行が返るべきかを決めてから式を見直すと、見た目だけで直すのを避けられます。

まとめ

#N/A が出たら、まず検索範囲の左端と末尾を確認し、次に数値・文字列の違い、最後に余分な空白を調べます。元の値を残し、どこを変えたか説明できる状態で、元のエラー行・別の正常行・該当なしを確かめてから報告に使いましょう。数式を直して終わりにせず、返った値が元一覧のどの行に対応するかまで確認するのがポイントです。

参照：Microsoft公式のVLOOKUP解説／ #N/Aの修正方法／関連記事：Excel関数をAIで作る方法