← 記事一覧へ戻る

VLOOKUPができない原因5選｜#N/Aの直し方とXLOOKUPとの違い

VLOOKUPでデータはあるはずなのに#N/Aになる原因を5つに整理。スペース・全角半角・数値と文字列の型違いなど、僕が実際にハマった直し方を解説します。

「データはあるはずなのに、#N/A になる」

VLOOKUP関数を使い始めた人が、ほぼ確実に一度はぶつかる壁です。目では確かに「P001」が見えてるのに、Excelは「見つかりません」と言ってくる。何が間違ってるのか分からないのが一番つらいんですよね。

僕も先日、本業の集計作業でこれに数時間ハマりました。結論から言うと、原因は「見た目は同じでも、Excelの中では別物」だったから。

この記事では、VLOOKUP基本編の続きとして、#N/Aになる原因を5つに整理して、僕が実際にやった直し方まで解説します🌱

この記事でわかること

  • #N/Aエラーの原因がどれに当てはまるか切り分けられる
  • 余分なスペース・全角半角・「数値と文字列」の直し方
  • エラーをきれいに表示するIFNA関数
  • XLOOKUPとの違いと使い分け

まず前提：#N/Aは「故障」ではない

#N/Aは「Not Available（見つかりませんでした）」という正常な報告です。Excelが壊れたわけではなく、「検索値が、検索範囲の左端の列に存在しなかった」と言っているだけ。

なので、やることはシンプル。「あるはずなのに見つからない」原因を、上から順に潰していくことです。よくある原因はこの5つ👇

#N/Aになる5つの原因マップ

原因①：検索値が本当に存在しない

まずは基本から。検索値のタイプミス（P001 のつもりが P01）や、マスタ側がまだ未登録のケースです。

確認方法：検索範囲の列で Ctrl + F（検索）して、検索値を探してみる。ヒットしなければ、本当に存在しません。

これで見つかるのに#N/Aになるなら、次の②〜④のどれかです。ここからが本番。

原因②：余分なスペースが入っている

見た目は「P001」でも、実際は「P001 」（末尾に空白）だと別物として扱われます。他のシステムからコピペしたデータに多いパターンです。

直し方：TRIM関数で前後の空白を取り除きます。

=TRIM(A2)

作業列（隣の空いた列）に入れて下までコピーし、その列を検索に使えばOKです。

原因③：全角と半角が混ざっている

「Ｐ００１」（全角）と「P001」（半角）も別物です。見た目が似ているので、これが一番気づきにくい。

直し方：ASC関数で全角→半角に揃えます。

=ASC(A2)

これも作業列方式で。逆に全角に揃えたいときは JIS関数です。

②と③をまとめると、こんなイメージです👇

作業列でTRIMとASCを使って表記を揃える例

原因④：「数値」と「文字列」の型が違う（僕がハマったやつ）

ここが今日の山場です。見た目はまったく同じ「1001」なのに一致しないケース。

Excelのセルには「数値」と「文字列」という**データの種類（型）**があります。同じ「1001」でも、

  • 数値の1001（セルの右寄せ）
  • 文字列の”1001”（セルの左寄せ・左上に緑の三角が出ることも）

は、VLOOKUPにとって完全に別物です。

数値の1001と文字列の1001は見た目が同じでも別物

僕が実際にハマった状況

商品コードに「1001」「1002」のような数字だけのものと、「A101」のような英字入りのものが混在している表でした。この場合、Excelは数字だけのコードを「数値」、英字入りを「文字列」として読み込むことがあり、列の中で型がバラバラになります。すると、同じ列なのに「一致する行と一致しない行が出る」という、わけのわからない状態に……。

ちなみに最初は「データ」タブの「区切り位置」で型を変換しようとしたんですが、僕のケースでは直りませんでした。

直し方：&"" で文字列に揃える

最終的に効いたのがこれ。作業列で、キー列の値に空文字をくっつけるだけ。

=A2&""

&"" は「空っぽの文字をつなげる」という意味で、これをやると数値も強制的に文字列に変わります。検索値側と範囲側の両方を文字列に揃えれば、型の不一致が解消されて一致するようになります。

逆に文字列→数値に揃えたいときは =A2*1（1を掛ける）が定番です。

原因⑤：検索範囲がズレている（$忘れ）

数式をオートフィルでコピーしたときに、検索範囲も一緒にずれてしまうパターンです。

オートフィル＝セルを選択したときに右下に出る小さな四角（■）をつかんで下にドラッグすると、数式をまとめてコピーできる機能のこと。便利な反面、数式の中のセル参照も1行ずつ自動でずれていきます。

=VLOOKUP(E2, A2:C9, 2, FALSE)   ← コピーすると範囲がA3:C10、A4:C11…とずれる
=VLOOKUP(E2, $A$2:$C$9, 2, FALSE) ← $で固定すればずれない

範囲に $（絶対参照）を付けるか、A:C のように列ごと指定すれば防げます。詳しくは基本編で解説しています。

補足：エラー表示をきれいにする（IFNA関数）

原因を潰しても、「マスタに未登録（原因①）」はどうしても発生します。そんなとき、#N/Aがそのまま表に並ぶと見栄えが悪い。

IFNA関数で包むと、#N/Aのときだけ別の表示にできます。

=IFNA(VLOOKUP(E2, $A$2:$C$9, 2, FALSE), "未登録")

これで、見つからないときは「未登録」と表示されます。空欄にしたければ "" を指定します。

IFNAで#N/Aを「未登録」と表示した例

ひとつ注意点。僕は最初、別セルでの判定に =IF(OR(ISNA(Z1), R1=Z1), …) のようにOR関数でまとめようとしたんですが、ORの中に#N/Aがあるとエラーがそのまま伝染してうまくいきませんでした。=IF(ISNA(Z1), "", IF(R1=Z1, "", 1)) のようにIFを入れ子にして、#N/A判定を先に済ませるのが確実です。

XLOOKUPとの違い（使える環境なら検討）

Excel 2021 / Microsoft 365 では、VLOOKUPの後継 XLOOKUP が使えます。主な違いはこれ👇

VLOOKUPXLOOKUP
検索列の位置左端限定どの列でもOK
返す列の指定列番号（数えるのが面倒）範囲で指定（ずれない）
見つからない時#N/A（IFNAで包む）引数で指定できる
使える環境ほぼ全バージョンExcel 2021 / 365 のみ

XLOOKUPなら「見つからなかったら”未登録”」を関数だけで書けます。

=XLOOKUP(E2, A:A, B:B, "未登録")

ただし、**古いExcelの人にファイルを渡すと開けない（#NAME?になる）**ので、職場のバージョンが揃っていない場合はVLOOKUPが無難です。僕も本業ではVLOOKUPを使っています。

よくある質問（FAQ）

Q1：FALSE指定なのに「違う値」が返ってくるのはなぜ？

範囲ズレ（原因⑤）が多いです。コピーした数式の範囲が $ で固定されているか確認してください。それでも変なら、範囲の左端列に重複した検索値がないかチェック。VLOOKUPは最初に見つかった行を返すので、重複があると意図しない行を拾います。

Q2：作業列を増やしたくないんだけど…

=VLOOKUP(E2&"", …) のように、検索値側に直接 &"" を付ける方法もあります。ただし範囲側の型が混在している場合は、範囲側も揃える必要があるので、最初は作業列方式が分かりやすくておすすめです。

Q3：TRIMでもASCでも&""でも直らない

改行コードや見えない特殊文字が混ざっているケースがあります。CLEAN関数（=CLEAN(A2)）を試してみてください。それでもダメなら、その列を一度メモ帳に貼り付けて戻すと、見えない文字が消えることがあります。

まとめ

#N/Aの原因は、ほぼこの5つに当てはまります。

  1. 本当に存在しない → Ctrl+F で確認
  2. 余分なスペース → TRIM
  3. 全角/半角 → ASC
  4. 数値と文字列の型違い → &"" で文字列に揃える（僕がハマったやつ）
  5. 範囲ズレ → $ で固定

今日のアクション：#N/Aに出会ったら、上から順に1つずつ試す。順番に潰せば必ずどれかに当たります。

もし解決できないエラーがあったら教えてください

5つ試してもダメだったケース、ぜひ教えてください。僕の経験値になりますし、次の記事のネタにさせてもらいます🌱

連絡手段：

  • 📝 記事下の コメント欄 にどうぞ
  • 🐦 X（旧Twitter）の @miyamon_ai にリプライ・DMでもOK

会社員の僕が業務効率化するためのノウハウを定期的に発信しています。

関連記事：