VLOOKUPで#N/Aが出る原因は6つ!見た目は同じなのに見つからないときの直し方

VLOOKUPの#N/A、原因は6つ。原因の見分け方と、まとめて防ぐ式 トラブル解決

商品マスタから VLOOKUP で商品名を引いてきたら、#N/A がずらっと…。マスタを見ると、ちゃんと同じコードがあるのに!ということ、ありませんか?(´・ω・`)

#N/A は「探した値が、範囲のいちばん左の列に見つからなかった」という意味です。本当に登録がないとき以外は、ほとんどが「数値と文字の違い」「空白」「全角と半角」「範囲のずれ」「探す列の位置」のどれかでした。

この記事では、注文表に商品マスタから商品名と単価を引く例で、実際に #N/A を出して、原因を1つずつ確かめてみました。直し方も、全部試して結果が出たものだけを載せています💡

🎬 実際の Excel 画面で、#N/A を1つずつ出して直している動画もあります(5分くらい)。文章より画面で見たいときは、こちらからどうぞ♪

動画で見る:VLOOKUP の #N/A の原因6つと直し方(音声あり・章ごとに見られます)

実際に出た #N/A:8件中4件!

商品マスタ(7件)を用意して、注文表の B2 にこの式を入れ、下にコピーしました。

商品マスタ。コード・商品名・単価の7行。101は文字として、102は数値として登録されている
商品マスタ。101 は「文字」、102 は「数値」で登録されている
=VLOOKUP(A2,商品マスタ!$A$2:$C$8,2,FALSE)
注文表でVLOOKUPを使った結果。8件中4件が#N/Aになり、原因を書き添えたもの
8件中4件が #N/A。右に原因を書き添えた

見た目ではマスタにあるはずのコードが、なんと4件も見つかりませんでした(>_<) ここから、原因ごとに見ていきますね。

まずは見分け方:3つの式で原因をしぼる

いきなり直す前に、空いているセルにこんな式を入れて調べると早いです。同じコードのはずのセルどうしを比べます。

見分け方の式。A3と直す前のマスタA2の比較はFALSE、ISTEXTで文字かどうか、LENで文字数6
101 どうしを比べると FALSE。マスタ側だけが文字で、C-401 は6文字あった
  • =A3=商品マスタ!A2:見た目が同じでも FALSE なら、Excel は「別の値」だと思っています
  • =ISTEXT(A3) と =ISTEXT(商品マスタ!A2):片方だけ TRUE なら「数値と文字の違い」。サンプルでは注文側が FALSE(数値)、マスタ側が TRUE(文字)でした
  • =LEN(商品マスタ!A8):「C-401」は5文字のはずなのに 6 と出たら、空白などの見えない文字が入っています

原因1:本当に登録がない

A-205 はマスタにありません。これは正しい #N/A です。表が #N/A だらけにならないよう、IFNA で文字に置き換えておきましょう。

=IFNA(VLOOKUP(A5,商品マスタ!$A$2:$C$8,2,FALSE),"未登録")

IFERROR でも同じように見えますが、IFERROR は式の書きまちがいまで隠してしまいます。列番号をうっかり 5(範囲は3列しかない)にした式で試したら、IFERROR は「未登録」と表示して、IFNA は #REF! のままでした。#N/A だけを置き換える IFNA のほうが、まちがいに気づけて安全です。

原因2:数値と文字の違い(101 と “101”)

マスタの 101 は「文字列」として登録されていて(セルに「数値が文字列として保存されています」という緑の三角が付いていました)、注文表の 101 は「数値」です。見た目は同じでも、VLOOKUP は別物として扱って #N/A になりました。逆の組み合わせ(マスタが数値、注文が文字)でも #N/A でした。

とりあえず直すなら、検索する値の形を相手に合わせます。

マスタが文字のとき: =VLOOKUP(A3&"",商品マスタ!$A$2:$C$8,2,FALSE)
マスタが数値のとき: =VLOOKUP(VALUE(A3),商品マスタ!$A$2:$C$8,2,FALSE)

ただ、ここに落とし穴がありました。このサンプルのようにマスタに文字と数値が混ざっていると、&"" を付けた式を全部の行にコピーしたとき、今度は数値で登録されている 102 が #N/A になったんです。根本的には、マスタのコード列をどちらか一方にそろえるのが確実です。下の「完成形」では、コード列を文字にそろえています。

原因3:後ろに空白が入っている

マスタの「C-401」の後ろに空白が1つ入っていて(LEN で6文字)、注文表の「C-401」と一致しませんでした。空白は画面ではまったく見えないので、いちばん気づきにくい原因です。

  • マスタ側に空白があるとき:マスタのセルを TRIM で掃除する(空いた列に =TRIM(A8) と入れて、値だけ貼り付けて戻す)。掃除したあとは「封筒」が正しく出ました
  • 注文側に空白があるとき:=VLOOKUP(TRIM(A2),…) のように検索値を TRIM で包む。「A-201 」(後ろに空白)で #N/A になったものが、これで「トナー黒」になりました

原因4:全角と半角の違い(Ｂ-301 と B-301)

注文表に全角の「Ｂ-301」と入っていたせいで、マスタの半角「B-301」が見つかりませんでした。一方で、小文字の「a-202」は「A-202」と同じものとしてちゃんと見つかったんです。

つまり、VLOOKUP は大文字・小文字は区別しないけれど、全角・半角は区別するということですね。

実は関数によって扱いが違っていて、XLOOKUP・XMATCH・UNIQUE は全角と半角を同じものとして扱いました。関数ごとの違いは、2つの名簿を照らし合わせる記事に一覧表でまとめています。

VLOOKUP のまま直すなら、検索値を ASC 関数で半角にしてから探します。下の完成形の式に入れてあります。

原因5:範囲に $ を付け忘れて、コピーでずれた

範囲を 商品マスタ!A2:C8($ なし)のまま下へコピーすると、1行下がるごとに範囲も1行ずつ下にずれていきます。7行目の式は 商品マスタ!A7:C13 を探していて、上のほうにある商品が見つからなくなっていました。

$なしで式をコピーすると下の行で#N/Aになり、$ありの式では正しく表示される
$ なしの式は、下の行ほど上の商品を見つけられない

範囲は $A$2:$C$8 のように $ を付けて固定します。範囲を選んだあとに F4 キーを1回押すと付きますよ。

これがやっかいなのは、最初の数行は正しく出ることです。上のほうだけ見て「できた!」と思って、そのまま使い続けてしまいがちなので気をつけてください。

原因6:探したい列が範囲のいちばん左にない

VLOOKUP は、範囲のいちばん左の列しか探しません。商品名「トナー黒」からコードを引こうとして、範囲を A〜C 列のままにしたら #N/A になりました。

Microsoft 365 や Excel 2021 以降なら、XLOOKUP が便利です。探す列と返す列を別々に指定できるので、左側の列も返せます♪

=XLOOKUP(A12,商品マスタ!$B$2:$B$8,商品マスタ!$A$2:$A$8,"未登録")

これで「A-201」が返ってきました。ちなみに、XLOOKUP に替えると直る原因と直らない原因があったので、同じマスタで試した結果も書いておきますね。

  • 原因4(全角の「Ｂ-301」):XLOOKUP では見つかりました(「クリアファイル」が返った)
  • 原因2(数値の 101 と文字の “101”)と原因3(後ろの空白):XLOOKUP でも #N/A のままでした

数値と文字の違いや空白は、関数を替えても直らないので、値のほうをそろえる必要があります。

#N/A より危ない:4つ目の引数の省略

最後に、#N/A は出ないけれど、もっと危ないパターンです⚠️

4つ目の引数(FALSE)を省くと「近似一致」になります。試してみたら、マスタにない「A-205」も「B-300」も、エラーにならずに「トナーカラー」と返ってきました。

4つ目の引数を省くと、A-205もB-300もトナーカラーと間違った値が返る
FALSE を省くと、ないはずのコードにも値が返る

#N/A なら気づけますが、それらしい間違った値には気づけません。コードや名前で探すときは、必ず FALSE(または 0)を付ける、これだけは覚えておいてください!

完成形:原因2〜4をまとめて防ぐ式

マスタのコード列を「文字列」にそろえて空白を消しておいたうえで、注文表にはこの式を入れました。

商品名: =IFNA(VLOOKUP(TRIM(ASC(A2&"")),商品マスタ!$A$2:$C$8,2,FALSE),"未登録")
単価:   =IFNA(VLOOKUP(TRIM(ASC(A2&"")),商品マスタ!$A$2:$C$8,3,FALSE),"")
直した式を使った注文表。未登録のA-205以外の7件に商品名と単価が表示された
本当に未登録の A-205 以外、7件すべてが表示された
  • A2&"":数値で入力されたコードも文字にする(マスタが文字なので合わせる)
  • ASC(…):全角の英数字を半角にする
  • TRIM(…):前後の空白を消す

本当に未登録の A-205 以外、ぜんぶ表示されました✅

マスタのコード列を文字列にそろえるには、列を選んで表示形式を「文字列」にしてから、コードを入れ直します。数値のまま表示形式だけ変えても、中身は数値のままでした。

サンプルファイルはこちら

「注文」シートは完成形の式、「商品マスタ」は直したあとのマスタです。「直す前のマスタ」「範囲ずれ」シートで、#N/A が出る状態を試せます。データはすべて架空です。

vlookup-na-sample.xlsxダウンロード

マクロは入っていません。ダウンロードしたファイルが「保護ビュー」で開いたら、「編集を有効にする」を押すと式が動きます。

#N/A が出たら、まずは「見分け方」の3つの式から試してみてください。原因がわかれば、直すのはすぐですよ😊

この記事の式は、Microsoft 365 の Excel(ビルド 16.0.20527.20150、Windows 11)で、2026年11月8日にすべて試しました。

マスタの整理や、転記・照合の作業をまとめて自動化したい場合は、ココナラで Excel の自動化のご依頼も受けています。

タイトルとURLをコピーしました