DIME MAGAZINE
VLOOKUPで「値はあるはずなのに#N/Aになる」原因を図解で徹底解説。文字列・空白・型の違いなど見落としやすい原因と、すぐ使える対処法をまとめました。
目次
この記事でわかること
- 「値はあるのにヒットしない」が起きる”認知のズレ”の正体
- 数値・文字列の型違い／全角半角混在／不可視スペース／日付型の不一致の見分け方と修正方法
- EXACT関数・LEN関数を使って一発で原因を特定するフロー
VLOOKUPで「あるのにヒットしない」は”認知のズレ”が原因
マスタに確かにデータが存在するのに#N/Aになる。これが発生したとき、多くの人が「何かがおかしい」と感じながらも原因を特定できず途方に暮れます。
人間の目には「同じ」に見えても、Excelは以下のようなわずかな違いを別物として扱うのです。
- 123（数値）と “123“（文字列）
- A001（半角）と Ａ００１（全角）
- 田中 一郎（スペースあり）と 田中一郎（スペースなし）
- 2024/1/1（日付型）と “2024/1/1”（文字列型）
「人間には同じに見えるが、コンピューターには別物」この前提さえ理解できれば、あとは原因を1つずつ潰すだけです。この記事ではそんな「見た目は同じなのに一致しない」という認知のズレに絞って解説していくので、ぜひ参考にして見てください。
VLOOKUPで値があるのにヒットしない原因①：数値と文字列の型が違う
最も発生しやすいパターンです。IDや番号をシステムからCSVで取り込んだとき、数値のはずのコードが文字列として保存されていることがよくあります。
見分け方
- セル内で右揃え → 数値型
- セル内で左揃え → 文字列型（左上に緑の三角マークが出ることもある）
修正方法
VALUE関数で文字列を数値に変換して検索します。
=VLOOKUP(VALUE(C3), F:I, 2, FALSE)
逆にマスタが文字列でこちらが数値の場合
=VLOOKUP(TEXT(C3, "0"), F:I, 2, FALSE)
根本的な解決はマスタと入力表の型を統一することです。数式側での変換はあくまで対症療法なので、データ管理のルールを見直す機会にもなります。
VLOOKUPで値があるのにヒットしない原因②：全角・半角の混在
英数字・記号の全角・半角の違いです。「A001」（半角A）と「Ａ001」（全角A）は別文字として扱われます。
修正方法
ASC関数で全角を半角に統一。
=VLOOKUP(ASC(C3), F:I, 2, FALSE)
マスタ側が全角で統一されている場合はJIS関数で半角を全角に変換します。
=VLOOKUP(JIS(C3), F:I, 2, FALSE)
どちらに統一するかはルール次第ですが、一般的には半角英数で統一するほうがシステムとの親和性が高く、長期的にトラブルが少ないです。
VLOOKUPで値があるのにヒットしない原因③：不可視文字・スペースが混入している
コピペやシステム出力データに多いトラブルです。スペース・タブ・改行コードなど、目では見えない文字が混入していると一致しません。これは「目視確認しても原因が見つからない」という最も厄介なパターンです。
修正方法
TRIM関数で前後のスペースを除去。
=VLOOKUP(TRIM(C3), F:I, 2, FALSE)
ただしTRIMは全角スペースを除去しないため、SUBSTITUTE関数を組み合わせます。
=VLOOKUP(TRIM(SUBSTITUTE(C3, " ", "")), F:I, 2, FALSE)
システム出力データには改行コード（CHAR(10)）が含まれることもあります。
=VLOOKUP(SUBSTITUTE(TRIM(C3), CHAR(10), ""), F:I, 2, FALSE)
VLOOKUPで値があるのにヒットしない原因④：日付データの扱い方が違う
日付の場合、セル上の表示は同じ 2024/1/1 でも、片方が日付型（内部的には数値）、もう片方が文字列型（”2024/1/1″）として保存されていることがあります。
確認方法
そのセルで =ISNUMBER(C3) を実行し、TRUEなら数値（日付型）、FALSEなら文字列型です。
修正方法
DATEVALUE関数で文字列の日付を日付型に変換します。
=VLOOKUP(DATEVALUE(C3), F:I, 2, FALSE)
VLOOKUPで値があるのにヒットしない原因⑤：第4引数がTRUEになっている
第4引数（検索方法）が TRUE または省略されている場合、「近似一致」モードで動作します。このモードではデータが昇順に並んでいないと正しく動作しません。
=VLOOKUP(C3, F:I, 2)
=VLOOKUP(C3, F:I, 2, TRUE)
=VLOOKUP(C3, F:I, 2, FALSE)
完全一致で検索したい場合は必ず FALSE を指定してください。
VLOOKUPで値があるのにヒットしない原因を一発で特定する確認フロー
原因の候補が多くて迷ったときは、このフローで順番に確認してください。
【ステップ1】マスタに値が存在するか？
→ Ctrl+Fで検索して確認
→ なければ「マスタにデータがない」が原因
【ステップ2】型を確認する
→ =ISNUMBER(検索値セル) と =ISNUMBER(マスタの該当セル) を比較
→ 結果が異なれば「型の不一致」→ VALUE/TEXT関数で修正
【ステップ3】文字列を確認する
→ =EXACT(検索値セル, マスタの該当セル) を実行
→ FALSEなら「全角・半角・スペースの違い」→ ASC/TRIM関数で修正
【ステップ4】第4引数を確認する
→ 数式バーで第4引数がFALSEになっているか確認
→ TRUEや省略ならFALSEに変更
EXACT関数は「どこが違うか」まではわかりませんが、「一致しているかどうか」をTRUE/FALSEで返してくれます。ステップ3でFALSEが出たら、全角・半角・スペースの順番で原因を絞り込んでください。
よくある質問（FAQ）
Q. EXACT関数を使うと原因がわかりますか？
A. =EXACT(A1, B1) は大文字・小文字・全角・半角の違いまで含めて完全に一致しているかをTRUE/FALSEで返します。VLOOKUPでヒットしない場合の原因特定に非常に有効なので、まずこれで確認するのがおすすめです。
Q. マスタのデータを統一するのが大変な場合はどうすればいいですか？
A. VLOOKUP数式側でASC・TRIM・VALUEなどを組み合わせて変換してから検索するのが現実的な対処です。ただしマスタ自体を変更できない環境が続くと数式がどんどん複雑になるため、データ管理ルールの整備を並行して進めることをおすすめします。
Q. 何度確認しても原因がわかりません。
A. =EXACT(C3, F2) のように検索値とマスタの値を1対1で比較してみてください。FALSEが返ったら不一致の証拠です。さらに =LEN(C3) と =LEN(F2) で文字数を比較すると、スペースや不可視文字の混入も判別できます。
まとめ
「あるのにヒットしない」原因は、ほぼ次の5つです。
- 数値と文字列の型の違い → VALUE/TEXT関数で統一
- 全角・半角の混在 → ASC/JIS関数で統一
- 不可視スペース・特殊文字 → TRIM＋SUBSTITUTE関数で除去
- 日付型と文字列型の混在 → DATEVALUE関数で変換
- 第4引数がTRUE → FALSEに変更
まず =EXACT(検索値, マスタの値) で一致しているかを確認し、FALSEなら =LEN() で文字数を比較する。この2ステップが最速の診断方法です。
📖 あわせて読みたい
VLOOKUPで#N/Aエラーが出る原因と直し方【図解】｜消す方法・表示を変える方法まで
この記事でわかること #N/Aエラーが出る4つの原因（全角・半角／スペース／型の不一致／データなし） 原因別の修正方法（ASC・TRIM・VALUE関数など） …
VLOOKUPがエラーになる原因と対処法まとめ【図解】｜#N/A・#REF・#VALUEを一挙解決
この記事でわかること VLOOKUPで起きるエラー5種類と、それぞれの主な原因 #N/A・#REF!・#VALUE!・0表示・間違った値が返るときの対処法 IF…
VLOOKUPの使い方とは？基本構文・よくあるエラーまで完全ガイド
この記事でわかること VLOOKUPの基本構文（コピペOK） 別シート参照・複数条件の使い方 #N/A・#REF!などエラーの原因と直し方 VLOOKUPでは限…
文／Habiny
最新号
