VLOOKUPが#N/Aで見つからない原因5つ｜スプレッドシート

スプレッドシートのVLOOKUPが #N/A を返し、セルにカーソルを合わせると「値が見つかりませんでした」という趣旨の説明が出る（英語表示では "Did not find value ... in VLOOKUP evaluation"）。目で見ると表の中にちゃんとあるのに、なぜか見つからない——この記事はその状態を直すためのものです。

先に結論です。#N/A は「検索キーと完全に一致する値が、範囲の最初の列に無い」という意味です。Googleのヘルプでも、#N/A は値が見つからないことを意味すると説明されています。見た目は同じでも一致しない理由は、実務ではほぼ次の5つに収まります。①前後のスペース、②数値と文字列の違い、③全角と半角の違い、④検索キーが範囲の最初の列に無い、⑤コピーで範囲がずれた。上から順に潰せば、ほとんどは数分で直ります。

表示と状況から原因を逆引きする

最後の行は #N/A とは逆の症状ですが、同じ根から来ています。VLOOKUPの4つ目の引数（並べ替え済み）を省略すると、既定は TRUE＝近似照合です。GoogleはVLOOKUPのヘルプで、FALSE（完全一致）の使用を強く勧めています。業務の表引きでは、4つ目には必ず FALSE を書くと決めてしまうのが安全です。

1分でできる「本当に同じ値か」の確かめ方

原因を当てずっぽうで探す前に、空いているセルで次の式を打ってください。A2 が検索キー、D5 が「同じはず」の表のセルです。

=A2=D5

FALSE が出れば、見た目は同じでも中身が違うことが確定します。続けて、文字数と種類を確かめます。

=LEN(A2)　=LEN(D5)　=ISNUMBER(A2)　=ISNUMBER(D5)

LEN の数が違えば、スペースなど見えない文字が混ざっています（原因1）。ISNUMBER が片方だけ TRUE なら、数値と文字列の違いです（原因2）。この2つで、5つの原因のうち2つを1分で判定できます。

原因1｜前後に見えないスペースがある

CSVの取り込みや、他のシステムからのコピーで最も多いのがこれです。Googleのヘルプでも、" Apple"、"Apple "、"Apple" はVLOOKUPではそれぞれ別の値として扱われると明記されています。

• 一括で消す：列を選び、［データ］→［データ クリーンアップ］→［空白文字を削除］
• 式の中で揃える：=VLOOKUP(TRIM(A2), $D$2:$F$500, 3, FALSE)

TRIM は先頭と末尾のスペースを削除する関数です。印刷できない制御文字が混ざっている場合は CLEAN も併用します。式で揃える方法は、表の側が毎回外部から上書きされる運用に向いています。

原因2｜数値と文字列が混ざっている

「1001」という商品コードが、検索キー側では数値、表の側では文字列として入っている。見た目は同じでも一致しません。表の側が左寄せ、キー側が右寄せになっていれば、ほぼこれです。

• 表示形式を揃える：検索キー列を選び、［表示形式］→［数字］から数値か日付を選ぶ（ヘルプの推奨手順）
• 式の中で数値に直す：=VLOOKUP(VALUE(A2), $D$2:$F$500, 3, FALSE)
• 逆に文字列に揃える：=VLOOKUP(A2&"", $D$2:$F$500, 3, FALSE)

ゼロ始まりのコード（「0012」など）は、数値にすると先頭の0が消えて別の値になります。この場合は数値に直すのではなく、両方を文字列に揃えてください。

原因3｜全角と半角が混ざっている

「ABC-01」と「ＡＢＣ－０１」、「ｶﾞｽ」と「ガス」。手入力とシステム出力が混ざる表で起きます。スプレッドシートには全角の英数字とカタカナを半角に変換する ASC 関数があります。

=VLOOKUP(ASC(TRIM(A2)), $D$2:$F$500, 3, FALSE)

ただし、この式で揃うのは検索キー側だけです。表の側にも全角が混ざっているなら、表の側にも ASC をかけた補助列を作り、そちらを検索します。

原因4｜検索キーの列が範囲の左端に無い

VLOOKUPは、指定した範囲のいちばん左の列だけを検索します。ヘルプにも「エラー以外の値を返すには、検索キーが範囲の最初の列にある必要があります」とあり、簡単な確認方法として「検索キーがB列にあるなら、範囲はB列から始まる必要がある」と書かれています。

よくあるのは、表全体を選ぼうとして A列から範囲を指定し、実際のキーは C列にある、という形です。その場合は範囲を C列から始め直し、3つ目の引数（何列目を返すか）も数え直します。数えるのは表全体ではなく、選んだ範囲の左端を1とした列番号です。

キーより左の列の値を取りたいときは、VLOOKUPでは取れません。スプレッドシートの XLOOKUP なら、検索する列と返す列を別々に指定できるので、この制約がありません。

=XLOOKUP(A2, $C$2:$C$500, $A$2:$A$500, "該当なし")

原因5｜コピーで範囲がずれた

1行目では正しく、下へコピーすると途中から #N/A が増える。範囲を B3:D7 のように相対参照で書いていると、コピーするたびに範囲が1行ずつ下へずれ、上のほうの行が検索対象から外れていきます。ヘルプのおすすめの方法にも、範囲は $B$3:$D$7 のように絶対参照で書くことが挙げられています。

範囲をF4キーで絶対参照にする、または列ごと $D:$F と指定すれば、行を追加しても検索範囲から漏れません。

#N/A を表示させたくないときの書き方

「見つからない行は空欄にしたい」という場合は IFNA で包みます。

=IFNA(VLOOKUP(A2, $D$2:$F$500, 3, FALSE), "")

ここで1つ注意があります。原因を直す前に IFNA で隠すと、本当は存在するデータが空欄として見えなくなります。請求や在庫の表で、スペース1つのせいで金額が空欄になり、合計が合わないまま気づかない——という事故は珍しくありません。IFNA は、上の5つを確認して「本当に無い」と分かってから使ってください。IFERROR で包むと #REF! など別のエラーまで消えるので、表引きの #N/A だけを処理するなら IFNA が向いています。

毎月の取り込みのたびに同じ #N/A を直しているなら、表の作りそのものが限界に来ている合図です。コードの表記ゆれを入力の時点で防ぐ、取り込み時に自動で揃える、といった仕組みは、関数の工夫より効果が大きいことがよくあります。私たちMihataは、こうした日々の手作業をスプレッドシートを土台にした社内アプリに置き換えるお手伝いをしています。記事の途中で恐縮ですが、よろしければ合わせてご覧いただけたら嬉しいです。

関数のエラーが重なって表が重くなってきた場合はスプレッドシートが重い原因と軽くする対処法、配列で返す式が赤いエラーになる場合はARRAYFORMULAが効かない原因、数式が勝手に変わってしまう場合は数式が勝手に書き換わる原因と戻し方を参照してください。Excelで同じ表を扱っていてスピルのエラーが出る場合はエクセルのスピルエラーの直し方にまとめています。

どうしても一致しない行が残る場合は、該当の2セルの値と式をそのままお送りください。どこが違うのかの切り分けからご一緒します。