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