公開日：
ExcelのVLOOKUPで#N/Aになる原因と直し方｜完全一致・文字列・空白を確認
Excelで顧客コードから担当部署を表示しようとしたら、数式の結果が #N/A になった。マスターには同じコードがあるように見えるのに、なぜか見つからない――。月次の請求データや顧客台帳でよく起こる困りごとです。
#N/A は通常、指定した検索値が検索先で見つからないことを示します。数式を書き直す前に「完全一致の指定」「文字列と数値の違い」「余分な空白」「検索範囲」を順番に確認しましょう。
この記事ではWindows版Excelを例に、少量の架空データで手順を紹介します。
図1：見た目が似たコードでも、値や型が違うと一致しません。架空の台帳を使った概念図です。
1. まず数式の末尾を「FALSE（完全一致）」にする
例えば、A列に顧客コード、B列に担当部署を置いたマスターがあるとします。
| セル | A列：顧客コード | B列：担当部署 |
|---|---|---|
| 2行目 | 00125 |
経理 |
| 3行目 | 00126 |
営業 |
| 4行目 | 00127 |
総務 |
E2に検索する顧客コードがあるとき、部署を返す式の例は次のとおりです。
=VLOOKUP(E2,$A$2:$B$4,2,FALSE)
引数の意味は次のとおりです。
| 部分 | 意味 | 確認点 |
|---|---|---|
E2 |
探したい顧客コード | 表示だけでなく中身が一致しているか |
$A$2:$B$4 |
検索先の範囲 | 左端のA列に顧客コードがあるか |
2 |
範囲の左から2列目の結果 | 返したい部署がB列か |
FALSE |
完全一致で探す | 省略や TRUE にしていないか |
VLOOKUPで第4引数を省略すると、近似一致として処理される場合があります。コードやIDの照合には原則 FALSE を明示してください。下へコピーする場合は、検索範囲に $ を付けて固定します。
2. 「00125」と「125」になっていないか確認する
顧客コード 00125 の先頭ゼロには意味があります。しかし、CSVの読み込み時に数値へ変換されると 125 になり、見た目や内部の型が違う状態になります。
検索値がE2、マスターのコードがA2なら、空きセルでそれぞれ次を確認します。
=ISTEXT(E2)
=ISTEXT(A2)
=ISNUMBER(E2)
=ISNUMBER(A2)
=LEN(E2)
=LEN(A2)
ISTEXT がTRUEなら文字列、ISNUMBER がTRUEなら数値です。LEN は文字数の確認に使えます。先頭ゼロを含む管理コードは、通常両方を文字列としてそろえる方が安全です。
注意：125 になったセルの表示形式だけを「文字列」に変えても、失われた先頭ゼロが復活するわけではありません。元CSVや元マスターから、正しいコードを確認して取り込み直してください。
3. 前後の空白・見えない文字を調べる
見た目では同じ「東京営業所」でも、末尾に半角スペースがあると完全一致で見つからないことがあります。
まずは =LEN(E2) と =LEN(A2) で長さを比較します。検査用の列を追加するなら、次の式で一般的な半角スペースの前後を整理できます。
=TRIM(A2)
改行などの一部の制御文字は CLEAN 関数も候補です。ただし、全角スペースや特定の不可視文字はTRIMだけでは消えない場合があります。また、名称の途中にあるスペースが業務上必要なときは一括削除しないでください。
元データを上書きせず、検査用の別列で整形結果を比較し、必要な行だけ修正するのが安全です。
4. 検索範囲と「左端列」を確認する
VLOOKUPは、指定範囲の一番左の列で検索値を探します。範囲を $B$2:$C$100 にしているのに、顧客コードがA列にある場合は、目的のコードを探せません。
確認したいことは4つです。
- 顧客コードが指定範囲の一番左にあるか。
- 検索対象の最終行まで範囲が広がっているか。
- コピー時に参照範囲がずれていないか。
- 同じコードが複数行ないか。複数あると先に見つかった行の結果を返すため、台帳側の重複も確認します。
図2：上から順に確認すると、原因を絞り込めます。
5. そもそもマスターにコードが存在しない場合
検索値が正しく、型も範囲も問題ないなら、マスターにそのコードが登録されていない可能性があります。月次処理なら、次の点を担当者に確認します。
- 新規顧客のコードがマスターへ追加されているか。
- 利用停止・統合でコードが変更されていないか。
- 今月のデータと先月のマスターを混ぜていないか。
マスターに存在しないコードを「エラーだから」と別のコードへ書き換えるのは避けましょう。取引先・経理など業務上の確認が必要です。
6. IFERRORで隠すのは、原因を確認したあと
=IFERROR(VLOOKUP(E2,$A$2:$B$4,2,FALSE),"要確認")
この式なら #N/A などのエラー表示を「要確認」に置き換えられます。ただし、エラーがなくなったのではなく、表示を変えただけです。売上や請求処理で 0 や空欄に置き換えると、未照合の行を見落とすことがあります。
#N/A だけを分けたい場合は IFNA も使えます。どちらにしても、確認対象の件数を記録し、マスター整備へつなげてください。
よくある質問
Q. 数式を入れたセルに #N/A ではなく式がそのまま表示されます
計算式が文字列として入力されている可能性があります。Excelの数式が反映・更新されないときの手順で表示形式や計算設定を確認してください。
Q. #REF! と #N/A は同じですか？
違います。#REF! は参照が無効になった場合などのエラーです。検索値が見つからない #N/A と対処が異なります。
Q. 数値の「125」と文字列の「125」は同じですか？
見た目が同じでも、検索式では型の違いが問題になることがあります。ISTEXT と ISNUMBER で確認し、マスターと検索値の型を統一しましょう。
まとめ
VLOOKUPの #N/A は、①完全一致 → ②文字列・数値 → ③空白 → ④検索範囲 → ⑤マスターの登録有無の順で確認します。原因が分かる前にIFERRORで空欄にせず、元データを残して対処することが、月次業務では重要です。