VLOOKUPで検索できず#N/Aエラーになる原因と解決法

VLOOKUPで#N/Aが出るのはなぜ？

#N/AはNot Available（未定・不明）の略で、Excelのエラーでは「参照する値が無い・データが見つからない」という意味になります。

検索関数であるVLOOKUPで#N/Aが出るのは、検索する値や抽出するデータが範囲の中に見つからないからです。

#N/Aエラーは以下のVLOOKUP関数の４つの引数「検索値」「範囲」「列番号」「検索方法」に絡んで発生します。

VLOOKUPで#N/Aになる原因【１】「検索値」と「範囲」にある値が完全一致になっていない

「検索値」に入力した［商品コード］の文字列と「範囲」の中にある文字列が完全に一致していないからです。
VLOOKUPは半角と全角を区別して検索します（大文字と小文字は同じ文字として検索されます）。全角「Ｙ」と半角「Y」は別の文字になるので「#N/A＝検索したデータが範囲の中に見つからない」という結果が返ります。その他、誤字や文字列に含まれるスペースの有無にも注意しましょう。

【検索値で#N/Aが出る入力例】「範囲」にある文字列　➔　Y-02
• Ｙ-02　➔　✕（半角Yが全角で#N/A）
• y-02　➔　◯（小文字のyは大文字のYと区別されないのでOK）
• Y -02　➔　✕（文字列に半角スペースがあるので#N/A）

解決法

「検索値」の文字列を入力し直します。
検索する「範囲」の中の該当する文字列をコピーし、「検索値」のセルに「値として貼り付け」するのが一番確実な方法です。

#N/Aエラーが返っている以上、同じ文字に見えても違う箇所が必ずどこかにあります。

VLOOKUPで#N/Aになる原因【２】「検索値」を探す列が「範囲」の一番左側に無い

数式のコピーで引数「検索値」の指定場所が１セルずれて、検索する列が「範囲」内の左端ではなくなっているからです。
VLOOKUPでは「検索値」を参照する列を「範囲」の一番左側にしておく必要があります。その列を「1」として、右へ数えた「列番号」から値を抽出します。

解決法

【解決法１】

1. コピーする前のVLOOKUP関数で引数「検索値」を絶対参照にします。
2. 「列番号」を修正します。

【解決法２】
コピーした後のVLOOKUP関数で引数「範囲」を修正します。

【解決法２】では、１つずれて検索値になった商品名の列が範囲の左端になるように指定し直しています。

VLOOKUPで#N/Aになる原因【３】固定された「範囲」にデータが追加・削除され、参照がずれる

絶対参照で指定した「範囲」にデータが追加され、最後のデータが範囲外に押し出されたためです。

解決法

VLOOKUPの引数「範囲」を固定しておかないと正しく結果が返されないことがあるので、「範囲」を絶対参照にするのは間違ってはいません。ただ、日常的にデータの追加や削除があるスプレッドシートから「範囲」を参照すると、上記のようなエラーにつながります。この場合は、以下のどちらかの方法で解決します。

• 【解決法１】VLOOKUPの「範囲」を列で指定します。（例）A:F
• 【解決法２】「範囲」に参照する表をテーブルにします。

どちらの方法でも、「範囲」は固定され、尚且つデータが追加・削除されても参照がずれることはありません。

VLOOKUPで#N/Aになる原因【４】「検索方法」をTRUEにして近似値を探す場合に「範囲」の数値データが不足している

この例で「検索値」を「25」に指定して、近似値の「30」でヒットさせたいのに#N/Aエラーが出ているのはなぜ？

「検索方法」をTRUEにしたのに、近似値でヒットしません。

「検索方法」がTRUEの場合、「範囲」の数値データは「～以上」の数値を小さい方から順番に並べる必要があります。一番上にある数値データ以下の数値は検索の対象になりません。

解決法

「範囲」に検索値にしたい数値以下の数値データ行を追加します。この例では「30」の上に行を追加し、「1」～「25」の数値を入力します。

ここでは左端列の一番上のセルに「1」を入力し、「1以上30未満」の近似値検索ができるようにデータを追加しました。

VLOOKUPで#N/Aエラーを非表示にする関数

VLOOKUP関数の検索値が未入力でセルが空白になっている場合にも#N/Aエラーは出ます。空白の場合は#N/Aエラー値を非表示にできる便利な関数があります。