先週、取引先400社ぶんの請求データを突き合わせる作業を任されました。
社内システムから吐き出したCSVの請求一覧と、経理部が管理している取引先マスタ。取引先コードをキーにVLOOKUPで突き合わせて、支払期日と担当者名を一覧に流し込む。数式自体は既存のものをコピーしただけなので、作業としては単純なはずでした。
ところが実行してみると、400件のうち27件だけが「#N/A」。しかも上から順に並べても、どのコードがエラーになるのか見た目にはまったく法則が見えません。承認会議は17時から。時計を見ると残り40分。焦って数式を何度も見直しましたが、括弧の数も、範囲の指定も、間違っているようには見えない。
結論から言うと、VLOOKUPの#N/Aのほとんどは、数式そのものの間違いではありません。「データ側に潜んでいる、目に見えない小さなズレ」が正体であることが大半です。ただ、そのズレ方にはいくつかのパターンがあり、パターンを知っているかどうかで、原因にたどり着くまでの時間が大きく変わります。
VLOOKUPの基本的な使い方や近似一致の危険性を先に押さえておくと、この先の内容もつながりやすくなります。今回は、実務で本当によく出会う原因を、私が実際にハマった順番に沿って整理しました。
「たぶんデータが悪い」と疑えるかどうかが分かれ目
会議前に固まってしまう人と、5分で原因にたどり着く人の違いは、実はそこまで大きくありません。差がつくのは、数式を疑う前に「データそのものを疑えるかどうか」だけです。
一部のセルだけが#N/Aになって規則性が見えない、昨日まで動いていた数式が今日は急にエラーを返す、検索値も参照範囲も見た目には間違っていない――こういう状態に心当たりがある方は、この後に紹介する原因のどれかに当てはまっている可能性が高いです。順番に見ていきます。
犯人1：見えないスペース
一番多い原因は、目に見えないスペースです。
たとえば請求データ側の取引先コードが「A001」の後ろに半角スペースが1つ入っていて、マスタ側は「A001」だけ。画面上ではどちらも「A001」にしか見えません。ですがExcelにとっては別の文字列として扱われます。
実際に試すと分かりやすいので、次の数式を入力してみてください。
A1：A001（末尾に半角スペース）
A2：A001
A3：=A1=A2結果は「FALSE」。たった1文字のスペースで、一致しなくなります。私が経験した27件のエラーのうち、実に19件がこのパターンでした。
特に注意したいのは、他システムから出力したデータ、CSVで取り込んだ一覧、他部署からメールで送られてきたExcelファイルです。コピーの過程で改行コードや余計な空白が紛れ込みやすく、見た目だけでは絶対に気づけません。
コピー時に見えない文字が混入するケースについては、TRIM関数・CLEAN関数を使った具体的な対処法を別の記事で詳しく解説しているので、あわせて読んでおくと安心です。
対処法はシンプルです。
=TRIM(A2)前後の余計なスペースを取り除けます。「まずスペースを疑う」。これだけで解決するケースは、体感でも半分近くあります。
なお、スペース以外にも、全角の「Ａ００１」と半角の「A001」のように、見た目はそっくりでも文字コードが違うケースがあります。この場合はTRIM関数だけでは直せず、ASC関数（全角を半角に変換する関数）を組み合わせる必要があります。
文字コード違いで一致しないケースは思っている以上に発生頻度が高いので、「見た目は同じなのに一致しない」ときはスペースとあわせて疑ってみてください。
犯人2：TRUEが仕込んだ罠
次に多いのが、第4引数の設定ミスです。
=VLOOKUP(D2, A2:B4, 2, TRUE)このTRUEは「完全に一致する値がなければ、近い値で妥協する」という意味を持ちます。厄介なのは、エラーにならず、間違った値を平然と返してくることです。#N/Aで気づける分、まだ良心的とすら言えます。
具体例を挙げます。社員番号1000・1001・1003が入った対応表があるとして、検索値に「1002」を指定した場合。
=VLOOKUP(1002, A2:B4, 2, TRUE)このTRUEは、1002に一番近い、それ以下の値である1001の行を返します。存在しないはずの社員番号なのに、平然と別人のデータが返ってくるわけです。
取引先コードや社員番号のような「一意であるべき値」を検索する場面でTRUEを使うと、間違いに気づかないまま集計が進んでしまいます。実務では、ほぼ確実にFALSEを使うと考えて差し支えありません。
=VLOOKUP(A2, B2:C4, 2, FALSE)この設定を確認するだけで、原因不明のエラーはかなり減ります。ちなみに、TRUEにも正当な使いどころはあります。検索範囲が昇順に並んでいることが保証されていて、なおかつ「値の範囲で区切って判定したい」場合、たとえば点数から評価ランクを出すようなケースです。取引先コード・社員番号・商品コードのような一意の値を探す場面では、基本的にFALSE一択と考えておいてください。
犯人3：列を追加した瞬間に起きるズレ
地味ですが、実は一番厄介なのがこのケースです。数式自体はまったく間違っていないのに、表の構造が変わったことで参照先がズレてしまう、という原因です。
たとえば元の式が
=VLOOKUP(E2, A2:B4, 2, FALSE)だったとします。その後、集計用にC列・D列を挿入したのに、範囲を「A2:B4」のまま直し忘れていた。この状態では、正しい列を参照できません。
特に注意したいのは、途中で列を挿入した、並び替えをした、他のファイルからコピーして使い回している、というケースです。数式は一見正しく見えるため、原因究明に一番時間がかかるパターンでもあります。
重複列があるとVLOOKUPの参照ズレが起こりやすいという点も、以前の記事で詳しく扱っているので、範囲まわりのトラブルに心当たりがある方はあわせて確認してみてください。
「列を追加したら、範囲を必ず確認する」。これを習慣にするだけで、提出直前の事故はかなり防げます。
こうしたズレを根本から防ぐ方法として、参照範囲を「テーブル」として設定しておく手段もあります。テーブル化しておけば、データが追加されたときに範囲が自動的に拡張されるため、手動での修正漏れそのものを減らせます。すでに運用中のファイルでも、範囲変更の頻度が高い表であれば、テーブル化を検討する価値は十分にあります。誰が触っても壊れにくい表を目指すという意味では、引き継げる表の設計の考え方ともつながる部分です。
見落としがちな2パターン：型違いと左方向参照
3つの主要な原因に加えて、実務でもう二つ、見落としがちなパターンを紹介します。
一つ目は、「検索値は数値、参照先は文字列（またはその逆）」という型違いです。取引先コード「1001」を入力しているつもりでも、片方が数値として、もう片方が文字列として入力されている場合、Excelはこれを別物として扱います。見た目にはまったく同じ「1001」に見えるため、気づくまでに時間がかかりがちです。この場合は、検索値と参照先のどちらか一方をVALUE関数（文字列を数値に変換）やTEXT関数（数値を文字列に変換）で揃えると解決します。他システムから出力したデータやCSV取り込み直後のデータは、数値がすべて文字列として扱われていることが多いので要注意です。
二つ目は、検索したい列が参照範囲の一番左にない、というケースです。VLOOKUPは仕組み上、検索値のある列より右側しか参照できません。「名前で検索して、その左側にある取引先コードを取り出したい」というような使い方は、VLOOKUPの構造上そもそもできないのです。この場合は、範囲を作り直して検索列を一番左に持ってくるか、INDEX関数とMATCH関数を組み合わせる方法に切り替える必要があります。エラーの原因を探しても、実は数式そのものの設計に無理があった、ということも意外とよくあります。
Before / After：事故る式と直した式
ここまでの内容を、実際の数式で比較してみます。
| ケース | Before（事故る式） | After（直した式） |
|---|---|---|
| スペース混入 | =VLOOKUP(A2, D:E, 2, FALSE) | =VLOOKUP(TRIM(A2), D:E, 2, FALSE) |
| TRUE近似一致 | =VLOOKUP(A2, D:E, 2, TRUE) | =VLOOKUP(A2, D:E, 2, FALSE) |
| 範囲ズレ（列追加後） | =VLOOKUP(A2, D2:E10, 2, FALSE) | =VLOOKUP(A2, D2:F10, 3, FALSE) |
数式だけを見ると、修正箇所はほんの数文字です。ですが原因が分からないまま探していると、この数文字にたどり着くまでに30分以上かかることも珍しくありません。私が請求データの27件を潰し終えたのも、結局は会議開始の3分前でした。
エラーが出たら、この順番で確認する
あの日以来、私は確認する順番を固定しています。
- スペース確認（TRIM関数で余計な空白がないか）
- 全角・半角確認（ASC関数で表記ゆれがないか）
- FALSE設定確認（TRUEになっていないか）
- 範囲確認（列追加・並び替え後にズレていないか）
- 型確認（数値と文字列が揃っているか）
この5つを機械的にチェックするだけで、VLOOKUPの前で固まる時間はほぼなくなりました。原因を1件ずつ潰すより、この順番で疑う場所を絞り込んでいくほうが、結果的には早く終わります。
IFERRORの正しい使いどころ、間違った使いどころ
よくある応急処置が
=IFERROR(VLOOKUP(…), "")でエラーを見た目上消す方法です。資料の提出直前であれば、一時的にこの処理でしのぎたくなる気持ちもよく分かります。
ただし、これは原因を隠しているだけだという点は忘れないでください。エラーは敵ではなく、「どこかがズレていますよ」という合図です。IFERRORで隠したままにしておくと、次にそのファイルを触った人が、同じ原因に気づかないまま時間を使ってしまうことにもなりかねません。
IFERRORを使うなら、「根本原因を確認したあとの、最終的な見た目調整として使う」のがおすすめです。原因を潰さないままIFERRORだけを足す使い方は、応急処置というより問題の先送りに近いです。
よくある質問
Q. TRUEを使ってはいけないのでしょうか？
いいえ、TRUEにも正当な使いどころはあります。検索範囲が昇順に並んでいることが保証されていて、「値の範囲で区切って判定したい」場合、たとえば点数から評価ランクを出す場合などです。取引先コードや社員番号のような一意の値を検索する場面では、基本的にFALSEを使うと考えておいてください。
Q. IFERRORで空欄にしてしまえば、それで十分ではないですか？
見た目を整えるだけなら、それで問題ありません。ただし、なぜエラーが出ているのかを一度も確認しないままIFERRORだけを足すと、データ自体に潜んでいるズレに誰も気づかないまま、次の集計や別の数式にまで影響が広がることがあります。まずは原因を確認したうえで使うことをおすすめします。
Q. VLOOKUPとSUMIFS、どちらもエラーではないのに結果が合わないことがあります
VLOOKUPは「該当する行が見つからない」ときに#N/Aを返しますが、SUMIFSは条件に一致する行がなくても単純に「0」を返すため、エラーにすら気づきにくいという違いがあります。SUMIFSで数字が合わない原因と対処法で、範囲ズレや表記ゆれによる典型パターンをまとめているので、あわせて確認してみてください。
研究員から一言
あの日のエラーは、結局のところ、他部署のシステムから月次で自動出力されたCSVに、取引先コードの末尾に見えない改行コードが混入していたことが原因でした。原因が分かってしまえば、なんてことのない話です。
ただ、あの時の私は「自分の数式が間違っているに違いない」と思い込んでいました。実際には数式ではなく、データ側に原因があった。この思い込みに気づくまでに、余計な時間を使ってしまったのを覚えています。
Excelの数式そのものは、そこまで複雑なものではありません。怖いのは「きっとこうだろう」という思い込みです。エラーが出たら、まず自分の数式を疑う前に、データそのものを疑ってみる。この順番を変えるだけで、原因を見つけるまでの時間は大きく短縮できます。
複数条件が絡む検索や、シートをまたいだ複合的な参照など、もう一段階複雑なVLOOKUPトラブルの見抜き方については、noteの方によりリアルな実例を交えてまとめています。もう少し踏み込んだケースまで知っておきたい方は、あわせてご覧ください。
まとめ：#N/Aが出たら確認する5項目
- スペース（TRIM関数で除去）
- 全角・半角（ASC関数で統一）
- TRUE / FALSE設定（一意の値ならFALSE）
- 参照範囲（列追加・並び替え後のズレ）
- 型（数値と文字列が揃っているか）
この5つを順番に確認するだけで、VLOOKUPの前で固まることはなくなります。焦って数式を作り直す前に、一度立ち止まって確認する。それだけで、防げる事故は意外と多いものです。
コメント