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

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

※ Microsoft365(旧Office365)Excelの画像で解説しています。

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

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

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

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

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

この例で「検索値」に入力した商品コードが検索する「範囲」の中にあるのに#N/Aエラーが出ているのはなぜ?

VLOOKUPで#N/Aエラーになる2

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

    【検索値で#N/Aが出る入力例】「範囲」にある文字列 ➔ Y-02

  • Y-02 ➔ (半角Yが全角で#N/A)
  • y-02 ➔ (小文字のyは大文字のYと区別されないのでOK)
  • Y -02 ➔ (文字列に半角スペースがあるので#N/A)
解決法

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

VLOOKUPで#N/Aエラーになる3

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

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

この例で[単価]セルにも同じ数式をコピーしているのに#N/Aエラーが出ているのはなぜ?

VLOOKUPで#N/Aエラーになる4

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

解決法

【解決法1】

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

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

VLOOKUPで#N/Aエラーになる5

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

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

この例で下まで同じ数式をコピーしているのに最終行のセルにだけ#N/Aエラーが出ているのはなぜ?

VLOOKUPで#N/Aエラーになる6

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

解決法

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

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

VLOOKUPで#N/Aエラーになる7

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

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

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

VLOOKUPで#N/Aエラーになる8

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

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

解決法

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

VLOOKUPで#N/Aエラーになる9

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

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

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

#N/Aエラーだけに特化して処理する関数です。#N/Aエラーを非表示にできるとともに、エラーの原因も判定しやすくなります。

IFNA関数の使い方

#N/Aを含め、すべてのエラーを処理する関数です。エラーをまとめて非表示にできます。

IFERROR関数の使い方

VLOOKUPはビジネスの現場で必須の関数と言えるでしょう。便利な関数ですが、引数が4つあり、それぞれの指定方法にミスがあるとすぐ#N/Aエラーが返る難しさがあります。本記事でご紹介した#N/Aエラーの原因と解決法を参考に#N/Aエラーの解消に取り組んで下さい。

Excelのエラー一覧

エラー値を知って、Excelを使いこなそう!
エラー表示 読み方 エラーの意味と原因
#DIV/0! ディバイド‐パイ‐ゼロ 0除算エラー。ある数値を0で割り算した時に表示されるエラー。Excelでは0、空白が除数になると#DIV/0!が返る
#VALUE! バリュー 数式に入力した(または参照した)値の形式が間違っている時に表示されるエラー
#N/A ノー・アサイン VLOOKUP・XLOOKUPなどの検索関数で検索値が見つからない時に表示されるエラー
#REF! リファレンス 数式での参照が正しくない、参照するセル範囲が無効である時に表示されるエラー
#NAME? ネーム 関数名や参照したセルやセル範囲に定義された名前が無効である時に表示されるエラー
#NUM! ナンバー 数式に入力(または参照)されている数値が無効である時に表示されるエラー
#NULL! ヌル 半角スペース(参照演算子)で指定した複数の範囲に共通部分がない時に表示されるエラー
#スピル!(#SPILL!) スピル 複数の結果を一括で表示できるスピル機能を妨げる不具合がある時に表示されるエラー ※Microsoft365(旧Office365)、Excel2019、2021対応
記号文字 読み方 詳細
#### ナンバーサイン 日付のセルにマイナスの数値を入力、または桁数の多い数値を入力した時に表示される。俗にシャープシャープとも呼ばれる
nE+n n掛ける10のn乗 Excelで大きすぎる・小さすぎる数値を入力、または12桁以上の数値を入力した時に表示される。指数表示
緑の三角マーク エラーインジケーター セルの右上に表示。セルにエラー値ではなく、入力値や数式の結果が表示されている場合はエラーの可能性を意味する
エラー処理の関数 読み方 詳細
IFNA関数 イフ・エヌ・エー VLOOKUP・XLOOKUPなどの検索関数で検索値が未入力の場合に#N/Aエラーを指定した値に置き換える
IFERROR関数 イフ・エラー 未入力から発生するようなエラーを指定した値に置き換える
ISERROR関数 イズ・エラー エラー値かどうかを判定し、論理値(TRUE・FALSE)で返す ※Excel2003以前のバージョンでIFERRORの代替で使われる事が多い
\ この記事をシェアする /
タイトルとURLをコピーしました