エクセル時短・トラブル解決

ExcelのVLOOKUPで#N/Aが出る原因と直し方をすぐ解説

VLOOKUPで#N/Aが出ると、「式は合っているはずなのに、なぜ？」と止まってしまいますよね。仕事中だと特に焦ります。そんなときは、やみくもに式を打ち直すより、よくある原因を順番に確認するのがいちばん早いです。この記事では、私が実務でよく見る原因と直し方を、すぐ試せる形でまとめました。まずは結論から確認しましょう。

まず結論：#N/Aの直し方はこの5点を先に確認しましょう

VLOOKUPの#N/Aは、検索値が見つからないときに出ます。まずは「検索値の余分なスペース」「全角・半角の違い」「検索列が範囲の一番左にあるか」「完全一致FALSEになっているか」「数値と文字列のズレ」を確認しましょう。

VLOOKUPの基本形は次のとおりです。

=VLOOKUP(検索値,範囲,列番号,FALSE)

#N/Aを減らしたいなら、最後の引数はまずFALSEにして完全一致で使うのがおすすめです。TRUEや省略のままだと、意図しない結果になることがあります。

VLOOKUPで#N/Aが出る主な原因

1. 検索値が表の中に存在しない

いちばん多い原因です。たとえば社員コードや商品コードが、参照先の表にまだ登録されていないと#N/Aになります。

2. 前後にスペースが入っている

見た目では同じでも、「A123」と「A123 」は別物です。コピー貼り付けしたデータでよく起こります。

3. 全角と半角が混ざっている

「123」と「１２３」、「ABC」と「ＡＢＣ」は一致しません。コードや番号の照合で要注意です。

4. 数値と文字列がズレている

片方は数値、片方は文字列になっていると一致しないことがあります。セルの左上に緑の三角が出ている場合は特に疑いましょう。

5. 範囲の左端に検索列がない

VLOOKUPは、指定した範囲の一番左の列から検索します。検索したい列が真ん中や右側にあると、正しく探せません。

6. FALSEではなくTRUEになっている

最後の引数をTRUEまたは省略すると近似一致になります。マスタ表が並び替えられていないと、#N/Aや誤った結果の原因になります。

最短で直す手順

まずは次の順番で確認すると、無駄なく原因を絞れます。

  1. 数式バーでVLOOKUPの式を確認する
  2. 最後の引数がFALSEか確認する
  3. 検索値をコピーして、参照先の左端列に貼り付けて一致するか見る
  4. 検索値と参照先データのスペースを確認する
  5. 数値か文字列かを確認する
  6. 参照範囲の左端が検索列になっているか見る

クリックしながら確認する手順

手順1：数式を確認する

  1. #N/Aが出ているセルをクリックします。
  2. 画面上部の数式バーを確認します。
  3. =VLOOKUP(検索値,範囲,列番号,FALSE)の形になっているか見ます。
  4. 最後がTRUEまたは省略なら、まずFALSEに修正してEnterを押します。

手順2：検索値が本当にあるか確認する

  1. VLOOKUPの検索値になっているセルをクリックします。
  2. セルの内容を数式バーで確認し、値をコピーします。
  3. 参照先の表の左端列を開きます。
  4. Ctrl + Fで検索ボックスを開きます。
  5. コピーした値を貼り付けて検索します。
  6. 見つからなければ、その値は表に存在していない可能性が高いです。

手順3：スペースを取り除く

前後の空白が怪しいときは、別セルでTRIM関数を使うと早いです。

  1. 空いているセルをクリックします。
  2. =TRIM(A2)のように入力します。
  3. Enterを押して、空白が削除された値を作ります。
  4. そのセルを検索値としてVLOOKUPに使ってみます。

参照先データの空白も同じようにTRIMで整えると効果的です。

手順4：数値と文字列のズレを直す

見た目は同じでも、データの型が違うと一致しません。

  1. 検索値のセルをクリックします。
  2. 数式バーで先頭にアポストロフィがないか確認します。
  3. 左上の緑の三角があれば、警告マークをクリックします。
  4. 数値に変換が出たらクリックします。
  5. 逆に文字列にそろえたい場合は、別セルで=TEXT(A2,"0")のように変換します。

手順5：参照範囲を見直す

  1. 数式バーでVLOOKUPの範囲を確認します。
  2. その範囲の一番左の列が検索に使う列か見ます。
  3. 違っていたら、検索列が左端になるように範囲を指定し直します。
  4. 列番号も合わせて修正します。

#N/Aを表示させたくないときの対処法

エラー自体を隠したいだけなら、IFERRORを組み合わせると見た目がすっきりします。

=IFERROR(VLOOKUP(A2,$F$2:$H$100,2,FALSE),"")

これで見つからないときは空欄表示になります。未登録と表示したいなら、空文字の代わりに「未登録」と入れればOKです。

上手くいかない場合のチェックポイント

列番号が範囲外になっていないか

たとえば範囲が2列しかないのに、列番号を3にすると別のエラーになります。範囲と列番号の組み合わせを見直しましょう。

参照範囲がずれていないか

数式を下にコピーしたとき、参照範囲がずれて検索できなくなることがあります。範囲は$F$2:$H$100のように絶対参照にしておくと安心です。

フィルターや表示形式に惑わされていないか

フィルターで見えないだけでデータが存在する場合もあります。表示形式だけで判断せず、数式バーの中身まで確認しましょう。

改行や見えない文字が入っていないか

Webやシステムから貼り付けたデータには、スペース以外の見えない文字が入ることがあります。CLEAN関数やTRIM関数を組み合わせると整えやすいです。

=TRIM(CLEAN(A2))

VLOOKUPの代わりに使える時短ワザ

XLOOKUPが使えるならこちらが便利です

Excelの新しいバージョンならXLOOKUPのほうが柔軟です。左方向も検索でき、見つからないときの表示も指定できます。

=XLOOKUP(A2,F:F,G:G,"未登録")

VLOOKUPより範囲の指定ミスが起きにくいので、今後の時短にもつながります。

検索確認はCtrl + Fが速いです

「その値が本当にあるか」を見るだけなら、まずCtrl + Fで十分です。式をいじる前に実データを探すクセをつけると、原因特定がかなり早くなります。

まとめ

VLOOKUPの#N/Aは、ほとんどの場合「見つからない」ことが原因です。特に確認したいのは、完全一致FALSE、スペース、全角半角、数値と文字列、検索列が左端かの5つです。順番に見れば、かなりの確率で解決できます。急いでいるときほど、式を何度も打ち直すより、まずデータの中身をチェックしていきましょう。

おすすめの記事