VLOOKUP で #N/A が出る原因と対処|空白・型違い・範囲ずれの確認手順と IFNA の使い方

結論

VLOOKUP の #N/A は、「検索値を、検索範囲の先頭列の中で見つけられなかった」という意味のエラーです。数式が壊れているのではなく、一致するものが無い(無いように見える)状態を示しています。

「目で見ると同じ値があるのに #N/A になる」ケースの大半は、次のどれかです。

  • 検索値や表側に、前後の空白などの見えない違いがある
  • 片方が数値、もう片方が文字列で、型が違っている
  • 第4引数が TRUE(近似一致)になっていて、意図と違う探し方をしている
  • 検索したい列が範囲の先頭列になっておらず、探す場所がずれている
  • 数式をコピーしたときに検索範囲がずれ、対象が範囲の外に出た

この記事では、原因ごとに「確認のしかた」と「直し方」を対応させて解説します。最後に、見つからない場合の表示を IFNA/IFERROR で整える方法も紹介します。ただし、表示を整えるのは原因を直した後の仕上げであり、最初からエラーだけを隠す使い方はおすすめしません。

意味・原因

#N/A の意味と VLOOKUP の探し方

#N/A は「Not Available」、つまり該当する値が無いことを表すエラー値です。Microsoft サポートでも、#N/A は「数式が探すよう指示されたものを見つけられない」場合に表示されると説明されています。

前提として、VLOOKUP の構文は次のとおりです。

=VLOOKUP(検索値, 範囲, 列番号, [検索方法])
引数意味
検索値探したい値(セル参照でも直接入力でも可)
範囲探す表全体。検索はこの範囲の先頭列の中で行われる
列番号見つけた行から、範囲の中で何列目の値を返すか
検索方法FALSE:完全一致、TRUE:近似一致。省略すると TRUE(近似一致)になる

ポイントは2つです。1つ目は、探すのは常に範囲の先頭列だけであること。2つ目は、第4引数を省略すると近似一致になり、完全一致で探してほしい場面でも違う探し方になることです。ID やコードの照合では、基本的に FALSE を明示します。

原因の代表例

原因起きる状態
本当に値が無い検索値が表に登録されていない。正しい意味での #N/A
表記ゆれ・前後の空白「A001 」のように末尾へ空白が入る、全角・半角が違う、など見た目は同じでも別物として扱われる
数値と文字列の型違い見た目はどちらも 1001 だが、片方は数値、片方は文字列で一致しない
先頭列でない検索キーの列が範囲の2列目以降にあり、先頭列を探しても見つからない
近似一致 TRUE の誤検出FALSE のつもりが TRUE や省略になっており、並び順によっては見つからず #N/A、または別の行の値を返す
範囲がずれている相対参照のまま数式をコピーし、下の行ほど検索範囲が下へずれていく

対処手順

原因は1件ずつ切り分けます。まず「本当に表にある値なのか」を確定させてから、見えない違いを疑う順序が効率的です。

手順1:完全一致(FALSE)になっているか確認する

照合用途では、第4引数に FALSE を必ず指定します。

=VLOOKUP(A2,$E$2:$G$100,2,FALSE)

TRUE や省略のままだと、近似一致として「検索値以下で最大の値」を探す動作になり、先頭列が昇順に並んでいないと #N/A や誤った値を返すことがあります。Microsoft サポートの VLOOKUP の解説でも、誤った値が返る原因としてこの点が挙げられています。

手順2:本当に存在するか、表の中で検索する

表の範囲を選択し、Ctrl+F の検索で検索値を探します。ここで見つからなければ「本当に無い」が濃厚です。見つかるのに VLOOKUP は #N/A という場合は、手順3以降の「見えない違い」を疑います。

手順3:前後の空白など、見えない違いを潰す

検索値側に TRIM を使うと、前後の空白と、単語間の余分な連続空白を取り除けます。

=VLOOKUP(TRIM(A2),$E$2:$G$100,2,FALSE)

表側に空白が混ざっている場合は、表側を直すのが本筋です。補助列を作って TRIM で整えたキーを用意し、その列を範囲の先頭列にして検索する方法もあります。数式の中で表全体を無理に加工するより、補助列の方が後の確認がしやすくなります。

なお、全角と半角の違いは TRIM では直りません。コード体系が混在している場合は、入力規則や元データ側の統一もあわせて検討してください。

手順4:数値と文字列の型をそろえる

見た目が同じ数字でも、型が違うと一致しません。確認には、対象セルに対して次を使うと手早く分かります。

=ISNUMBER(A2)
=ISTEXT(A2)

表側が数値で検索値が文字列(またはその逆)の場合は、どちらかに寄せます。検索値が文字列で表が数値なら、VALUE で数値化する方法があります。

=VLOOKUP(VALUE(A2),$E$2:$G$100,2,FALSE)

逆に数値の検索値を文字列に寄せる場合は TEXT を使いますが、桁数や書式の指定を誤ると別の不一致を生みます。場当たり的に変換を重ねるより、元データの型を統一する方が根本的です。特に外部システムから貼り付けた表は、数字が文字列として入っていることがよくあります。

手順5:範囲の先頭列と絶対参照を確認する

  • 検索キーの列が、指定した範囲のいちばん左(先頭列)になっているか確認する。キーより左に列がある範囲指定では、そのキーは探せません。範囲の開始列をキーの列に合わせます
  • 数式を下へコピーする場合は、範囲を絶対参照で固定する
=VLOOKUP(A2,$E$2:$G$100,2,FALSE)

$ を付けない相対参照のままだと、コピーした行数だけ範囲が下へずれ、上の方のデータが範囲から外れて #N/A になります。「最初の行は合うのに、下の行ほど #N/A が増える」場合はこのパターンが典型です。

手順6:表示を整える(IFNA/IFERROR)

原因を直しても、「未登録なら #N/A が正しい」という行は残ります。見た目を整えたい場合は、エラー処理の関数で包みます。

見つからない場合だけを拾うなら IFNA が向いています。#N/A のときだけ指定した表示に置き換わり、他のエラーはそのまま出るため、数式ミスに気づけます。

=IFNA(VLOOKUP(A2,$E$2:$G$100,2,FALSE),"該当なし")

IFERROR は #N/A 以外のエラー(#REF!、#VALUE! など)もすべて置き換えます。

=IFERROR(VLOOKUP(A2,$E$2:$G$100,2,FALSE),"該当なし")
関数置き換える対象向いている場面
IFNA#N/A のみ「見つからない」だけを整えたい通常の照合
IFERRORすべてのエラーエラーの種類を問わず表示をそろえたい場合

基本的には IFNA を優先し、IFERROR は「他のエラーを隠してしまわないか」を考えてから使ってください。

セナ
セナ#N/A は『見つからない』。まず型と空白を疑って

つまずき所・よくある失敗

  • 第4引数を省略する:省略=近似一致(TRUE)です。照合では FALSE を毎回書く習慣を付けるのが安全です
  • IFERROR で先に隠してしまう:範囲ずれや型違いがあっても表示上は「該当なし」に見え、原因に気づけなくなります。エラー処理は切り分けの後に付けます
  • TRIM で何でも直ると思う:TRIM が対象にするのは空白です。全角・半角の違いや型違いは別に対処が必要です
  • 列番号の数え違い:列番号は「シートの列番号」ではなく「指定した範囲の中での列位置」です。範囲を変えたら数え直してください。範囲の列数を超えると #N/A ではなく #REF! になります
  • 左側の列を取りにいく:VLOOKUP は先頭列より左の値を返せません。その場合は表の並びを変えるか、INDEX と MATCH の組み合わせや XLOOKUP を検討します

まとめ

  • #N/A は「検索値が範囲の先頭列で見つからなかった」という意味。数式の故障ではない
  • 確認順序は、FALSE の指定 → 実在確認 → 空白(TRIM)→ 型(数値/文字列)→ 先頭列と絶対参照での範囲固定
  • 数式をコピーする表では、範囲に $ を付けた絶対参照が #N/A の予防になる
  • 表示の仕上げには IFNA を基本にし、IFERROR は他のエラーを隠さないか考えてから使う

関連記事

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です