【Excel】VLOOKUPが一致しない原因と直し方 — 空白・全角半角・型の違いを見分ける

この記事は約11分で読めます。

型番や商品コードをキーにして VLOOKUP や XLOOKUP でマスターを引いたのに、画面上はどう見ても同じ文字列なのに #N/A が返る。原因が分からないまま手作業で埋めている——という状態は珍しくありません。

この記事で分かること

  • 見た目が同じなのに VLOOKUP・XLOOKUP が一致しない主な原因4つ
  • どの原因に当たっているかを LEN ・ ISNUMBER ・ EXACT で切り分ける方法
  • 作業列に1本の数式を入れて、複数の原因をまとめて片づける手順
  • 直したあとに表記ゆれを再発させないための入力規則の考え方

対象は Microsoft 365・Excel 2021 以降です。XLOOKUP が使えない環境（Excel 2019 以前）でも、記事内の VLOOKUP の書き方でそのまま置き換えられます。

どんな場面で使うか

キーになるコード列が「人の手」と「システムの出力」の両方から入ってくる表では、ほぼ必ず起こります。

  • 基幹システムから出力した受注データに、Excel の商品マスターを突き合わせる
  • 複数の担当者が入力してきた台帳を、あとから1つに集約する
  • Web ページや PDF からコピーした値を、そのまま貼り付けて使っている

いずれも「コードは一意だから照合できる」前提で作られていて、その前提が崩れていることに気づきにくいのが厄介です。

基本説明

サンプルは「受注データ」シートと「商品マスター」シートの2枚構成です。

「受注データ」シートは、システムから出力したものをそのまま貼り付けた想定です。

行 A列（型番） B列（数量）
2 AB-1001 10
3 AB-1002 5
4 ＡＢ－１００３ 8
5 1005 12
6 AB-1006 3
7 AB-1007 6

「商品マスター」シートは型番・商品名・単価の3列で、手で整備したものです。

行 型番 商品名 単価
2 AB-1001 ボールペン 黒 120
3 AB-1002 ボールペン 赤 120
4 AB-1003 クリアファイル A4 80
5 1005 付箋 大 250
6 AB-1006 ノート A5 180
7 AB-1007 ホチキス針 10号 60

ただし、5行目の型番 1005 だけは表示形式を「文字列」にして入力してあります。受注データ側の 1005 は数値なので、この1組は見た目が同じで型だけが違う状態です。

並べて見るかぎり6行とも一致しそうなので、D列に商品名を引く数式を入れてみます。「受注データ」シートの列は次のように使います。

  • A列: 型番
  • B列: 数量
  • C列: 修正後の型番
  • D列: 商品名
  • E列: 単価
  • F列: 金額

まずは元の A列 のままで引いてみます。XLOOKUP が使えない環境のために、VLOOKUP 版も並べておきます。

=XLOOKUP(A2,商品マスター!$A$2:$A$7,商品マスター!$B$2:$B$7,"該当なし")
=VLOOKUP(A2,商品マスター!$A$2:$C$7,2,FALSE)

結果は、6行のうち一致するのは2行目の「ボールペン 黒」だけで、残りの5行はすべて「該当なし」（VLOOKUP では #N/A ）になります。どちらで書いても一致しないという事実は変わりません。

見た目が同じでも一致しない4つの原因

Excel の照合は「表示されている文字」ではなく「セルに入っている値」で行われます。次の4つのうち空白・制御文字・型の違いは画面上まったく見分けがつかず、全角と半角も並べて比べないと気づきにくいものです。

原因 何が起きているか 発生源としてよくあるもの
余分な空白 前後にスペースが入っている システム出力の桁揃え、コピー＆ペースト
全角と半角の違い AB-1003 と ＡＢ－１００３ は別の文字列 日本語入力のまま手入力した、旧システムからの移行
制御文字 末尾に改行やタブが紛れている CSV の取り込み、Web ページからの貼り付け
数値と文字列の違い サンプルでは受注側が数値の 1005 、マスター側が文字列の 1005 数字だけのコード、先頭ゼロの扱い

サンプルの3〜7行目に、この4つを1つずつ（7行目だけは全角スペースという組み合わせで）仕込んであります。

手順

ステップ1: 一致しない行を洗い出す

最初にやるのは犯人探しではなく、範囲の確定です。全行だめなら参照範囲そのものの間違いを疑ったほうが早く、一部だけなら値の表記ゆれである可能性が高くなります。

ステップ2: 3つの数式で原因を切り分ける

データ範囲の外（ここでは H・I・J列）に、次の3本を並べて入れます。

=LEN(A2)
=ISNUMBER(A2)
=EXACT(A2,ASC(A2))

LEN は文字数、 ISNUMBER はセルの中身が数値かどうか、 EXACT は半角化する前後で文字列が変わらないかを見ています。サンプルで出る値は次のとおりで、正しい型番 AB-1001 は7文字です。

行 LEN ISNUMBER EXACT 読み取れること
2 7 FALSE TRUE 問題なし
3 8 FALSE TRUE 余分な文字が1つある
4 7 FALSE FALSE 文字数は正しいが全角が混ざっている
5 4 TRUE TRUE 数値として入っている
6 8 FALSE TRUE 余分な文字が1つある
7 8 FALSE FALSE 余分な文字があり、しかも全角

4行目のように、文字数が正しくても一致しないことがあります。 LEN だけでは見逃すので、 EXACT と組み合わせて判断してください。

3行目と6行目はどちらも8文字で、この表だけでは区別がつきません。余分な1文字の正体は UNICODE で確認します。

=UNICODE(RIGHT(A3,1))
=UNICODE(RIGHT(A6,1))

3行目は 32 （半角スペース）、6行目は 10 （改行）が返ります。まず RIGHT で末尾を見て、正常な文字が返ったら余分な1文字は先頭側にあるので LEFT でも見ます。7行目は =UNICODE(LEFT(A7,1)) で 12288 （全角スペース）が返ります。

似た CODE ではなく UNICODE を使うのは、日本語版の CODE がシフトJISのコードを返すためです。全角スペースは CODE だと 33088 になり、文字コード表と合いません。

ステップ3: 作業列でキーをそろえる

原因が分かったら、C列（修正後の型番）に次の1本を入れて7行目までコピーします。

=TRIM(CLEAN(ASC(A2)))

内側から順に、 ASC が全角を半角へ、 CLEAN が改行などの制御文字を除去し、 TRIM が前後の空白を落とします。この順番には意味があります。 TRIM が消せるのは半角スペースだけで全角スペースは残るため、先に ASC で半角化しておく必要があります。

数値として入っている5行目も、 ASC が文字列に変換するので同じ数式で片づきます。結果は上から AB-1001 ・ AB-1002 ・ AB-1003 ・ 1005 ・ AB-1006 ・ AB-1007 の6件です。

ただし、この数式の結果は必ず文字列になります。サンプルはマスター側が文字列なので合いますが、逆にマスター側が数値なら、キーが文字列に固定されてかえって一致しなくなります。その向きのときは =VALUE(TRIM(CLEAN(ASC(A2)))) で数値へ寄せます。作業列を書く前に、どちらの型に揃えるのかを先に決めてください。

ステップ4: そろえたキーで参照し直す

A列ではなく C列 を引数にして、商品名・単価・金額を出します。

=XLOOKUP(C2,商品マスター!$A$2:$A$7,商品マスター!$B$2:$B$7,"該当なし")
=XLOOKUP(C2,商品マスター!$A$2:$A$7,商品マスター!$C$2:$C$7,0)
=B2*E2

XLOOKUP が使えない環境では VLOOKUP に置き換えます。第4引数の FALSE は必須です。ただし "該当なし" にあたる引数が無いので、一致しない行は #N/A のまま出ます（同じ見え方にするなら IFERROR で包みます）。単価を引くときは列番号が 3 です。

=VLOOKUP(C2,商品マスター!$A$2:$C$7,2,FALSE)
=IFERROR(VLOOKUP(C2,商品マスター!$A$2:$C$7,2,FALSE),"該当なし")
=VLOOKUP(C2,商品マスター!$A$2:$C$7,3,FALSE)

ステップ5: 元データそのものを直す

作業列を残すと、次にこのファイルを触る人が A列 と C列 のどちらが正なのか分からなくなります。照合が通ったら、次の順で片づけます。

  • C列 をコピーして、A列 に「値貼り付け」で戻す
  • D列・E列・F列 も、それぞれ同じ位置に「値貼り付け」して数式を値に変える
  • C列 を削除する

2番目を飛ばして C列 を消すと、D〜F列 が #REF! になります。 D・E列 が C列 を、F列 が E列 を参照しているためです。

C列 の数式をそのまま A列 に貼ると、A列 が自分自身を参照して循環参照になります。必ず値として貼り付けてください。

サンプルコードまたは例

サンプルデータで最後まで通したときの結果です。

行 修正後の型番 商品名 単価 数量 金額
2 AB-1001 ボールペン 黒 120 10 1200
3 AB-1002 ボールペン 赤 120 5 600
4 AB-1003 クリアファイル A4 80 8 640
5 1005 付箋 大 250 12 3000
6 AB-1006 ノート A5 180 3 540
7 AB-1007 ホチキス針 10号 60 6 360

金額の合計は =SUM(F2:F7) で 6,340 です。

=SUM(F2:F7)

修正前は2行目しか一致していなかったので、拾えていた金額は 1,200 だけでした。差は 5,140 で、6行しかないサンプルでもこれだけ動きます。件数の多い実務データなら、ずれはさらに大きくなります。

再発させないための入力規則

「受注データ」シートの型番列に、データの入力規則でマスターの型番リストを参照させておくと、手入力による表記ゆれは止められます。

  • 型番を入力する列を選択する
  • 「データ」タブ →「データの入力規則」→「入力値の種類」を「リスト」にする
  • 「元の値」に商品マスターの型番範囲を指定する

ただし、これは人が手入力する場合にしか効きません。システム出力を貼り付ける運用では入力規則は素通りするので、その場合は取り込み時に必ず作業列を1つ挟む、という手順のほうを固定してください。この作業列に相当する処理は、【Power Query】データクレンジング入門 のようにクエリ側へ寄せられます。

よくあるエラー

TRIM を入れたのに空白が消えない TRIM が対象にするのは半角スペースだけです。全角スペースは残るので、 ASC を先に通してください。また TRIM が落とすのは前後の空白と、単語間の連続空白を1つに詰めるところまでです。 AB- 1002 のように途中の1個は残るので、コードに空白を含まない前提のときだけ =SUBSTITUTE(A2," ","") を重ねて消します。

ASC も TRIM も効かない空白がある Web ページからコピーした値には、ノーブレークスペースが混ざることがあります。 CLEAN の対象外（制御文字ではない）なので、 =SUBSTITUTE(A2,UNICHAR(160),"") を追加で挟みます。日本語版では CHAR(160) が該当の文字を返さず、エラーにならず置換されないだけで終わるため、 UNICHAR （Excel 2013 以降）を使います。

大文字と小文字の違いが原因だと思っている VLOOKUP も XLOOKUP も、既定では大文字と小文字を区別しません。 ab-1001 と AB-1001 は一致するので、これは #N/A の原因になりません。

#N/A にならないのに値が間違っている VLOOKUP の第4引数を省略すると近似一致になり、一致しなくてもエラーを出さずに近い行を返します。表記ゆれの調査以前の問題として、完全一致で引くときは FALSE を必ず指定してください。

表示形式を変えたのに直らない 表示形式で見た目をそろえても、セルに入っている値そのものは変わりません。 0001 と表示されていても中身が数値の 1 なら、文字列の 0001 とは一致しません。

マスター側が汚れている この記事では受注データ側を直しましたが、原因がマスター側にあることもあります。どちらが汚れているかは、両方に LEN を並べて比べれば分かります。マスターは多くのファイルから参照される土台なので、汚れていたらマスター側を直すのを優先します。

まとめ

  • 見た目が同じなのに一致しない原因は、余分な空白・全角半角・制御文字・数値と文字列の違いの4つに集約される
  • LEN ・ ISNUMBER ・ EXACT の3本を並べれば、どれに当たっているかは特定できる
  • =TRIM(CLEAN(ASC(A2))) を作業列に入れると4つとも同時に片づく。 ASC を先に通す順番が重要
  • 照合が通ったら値貼り付けで元データを直し、作業列は残さない
  • 手入力が原因なら入力規則で予防できるが、システム出力には効かないので取り込み手順のほうを固定する

「たまに #N/A が出るので手で埋めている」という状態は、原因が特定されていないだけです。まずは LEN を1列足すところから始めてみてください。

関連記事

サンプルファイル

記事中で使用しているサンプルデータはこちらからダウンロードできます。

タイトルとURLをコピーしました