VLOOKUPで「#N/A」が出る5つの原因と直し方

「さっきまで動いていたVLOOKUPが、急に #N/A になった」
「同じ値があるはずなのに、なぜか見つからない」

VLOOKUPの #N/A は、Excelやスプレッドシートで最もよく見かけるエラーの一つです。#N/A は「探している値が見つからなかった」という意味ですが、実際には値があるのに見つからないケースがほとんどです。
この記事では、よくある原因を5つと、それぞれの直し方を紹介します。

■ 原因1：探している値が本当に存在しない
まずは基本です。検索する表に、その値が本当に入っているかを確認しましょう。商品コードの打ち間違いや、まだ一覧に追加されていない新商品が原因のことがあります。
【直し方】存在しない場合に「該当なし」と表示させたいときは、IFERRORで囲みます。
=IFERROR(VLOOKUP(A2,商品一覧!A:C,3,FALSE),"該当なし")
ただし、IFERRORはすべてのエラーを隠してしまうので、原因2〜5を確認してから使うのがおすすめです。

■ 原因2：見えないスペースが入っている
「A001」と「A001 」（最後に空白）は、Excelでは別の文字として扱われます。他のシステムからコピーしたデータや、CSVから取り込んだデータでよく起こります。
【直し方】TRIM関数で余分なスペースを取り除いてから検索します。
=VLOOKUP(TRIM(A2),商品一覧!A:C,3,FALSE)
検索される側の表にスペースが入っている場合は、別の列にTRIMをかけた値を作っておくと確実です。

■ 原因3：数値と文字列が混ざっている
見た目は同じ「1001」でも、片方が数値、もう片方が文字列だと一致しません。セルの左上に緑の三角が出ている場合は、数値が文字列として入っているサインです。
【直し方】検索する値の型を、表の側に合わせます。
文字列に合わせる：=VLOOKUP(A2&"",商品一覧!A:C,3,FALSE)
数値に合わせる：=VLOOKUP(VALUE(A2),商品一覧!A:C,3,FALSE)

■ 原因4：数式をコピーしたら範囲がずれた
1行目では正しく動くのに、下にコピーした行だけ #N/A になる場合は、検索範囲が一緒にずれている可能性があります。
【直し方】検索範囲を「$」で固定（絶対参照）します。範囲を「E:G」のように列ごと指定する方法もあります。
=VLOOKUP(A2,$E$2:$G$100,3,FALSE)

■ 原因5：検索したい列が、範囲のいちばん左にない
VLOOKUPは、指定した範囲のいちばん左の列しか検索できません。たとえば「商品名から商品コードを探したい」のに、商品コードが左側にあると使えません。
【直し方】XLOOKUP（Excel 2021以降・Microsoft 365・Googleスプレッドシート）を使うと、左右どちらの列でも検索できます。
=XLOOKUP(A2,商品一覧!B:B,商品一覧!A:A,"該当なし")
古いExcelの場合は、INDEXとMATCHの組み合わせで同じことができます。
=INDEX(商品一覧!A:A,MATCH(A2,商品一覧!B:B,0))

■ おまけ：最後の引数「FALSE」を忘れていませんか？
VLOOKUPの4つ目の引数を省略すると「近似一致」になり、表が並べ替えられていないと #N/A や間違った値が返ることがあります。完全に一致するものを探すときは、FALSE（または 0）を指定しましょう。

■ それでも直らないときは
ここまでの方法で直らない場合は、複数の原因が重なっていたり、数式が入れ子になって原因が見えにくくなっていたりすることがあります。
Excel・スプレッドシートのエラーの原因調査と修正を、3,000円から承っています。直した理由のメモもお付けするので、次に同じエラーが出たときはご自分で直せます。

▶ Excel・スプレッドシートのエラーを直します
https://coconala.com/services/4503644

「こんな集計をしたい」という関数づくりのご相談もお気軽にどうぞ。

▶ Excel・スプレッドシートの関数を作ります
https://coconala.com/services/4502972