VLOOKUPが合わない原因を200件で数えた。#N/Aの72件のうち64件は直せる

VLOOKUPが#N/Aを返すとき、値は確かにマスタにある。ないように見えているだけだ。200件で試したら、72件が#N/Aになって、そのうち64件は式を直すだけで見つかった。本当にマスタに無かったのは8件だけだった。

もっと困るのは、#N/Aが出ないほうだ。第4引数のFALSEを省くと、この8件が「社員200」という実在する名前を返してきた。エラーが出ないので気づけない。

以下の計算は、2026年9月22日にMicrosoft 365のExcel（バージョン16）で行った。200件の検索値は式を確かめるために作ったもので、原因ごとの割合は実際の現場とは違う。

72件の内訳を数えた

社員番号1001から1200のマスタを作り、検索値200件のうちいくつかを細工した。前に半角の空白を付ける、数値を文字列で入れる、全角の数字にする、CHAR(160)の空白を付ける、マスタに無い番号を入れる、の5つだ。

FALSEを付けた式で#N/Aになったのは72件。内訳は、前に半角の空白が21件、数値が文字列で入っているのが19件、全角の数字が13件、改行なしの空白が11件、マスタに無い番号が8件だった。

細工した64件は、直した式では全部見つかった。マスタに無い8件だけが残る。72件の#N/Aのうち、本当に手が出ないのは11%しかない。

#N/Aが並んでいると全部が駄目に見えるが、実際は表記のそろえ方の問題がほとんどだ。マスタの作りを疑う前に、検索値のほうを直す。

第4引数を省くと、無い番号が実在する名前に化ける

同じ200件を、第4引数を省いた式でも計算した。=VLOOKUP($A2,マスタ!$A:$B,2)と書くだけのものだ。

#N/Aは64件に減った。8件減っている。減った8件はマスタに無い番号で、6013番も6018番も6090番も6120番も、全部「社員200」を返した。

近似一致は、見つからないときに検索値より小さい値のうちいちばん大きいものを返す。マスタの最後が1200番なので、6013番を探すと1200番の行に当たる。エラーではないので、そのまま台帳に載る。

#N/Aが減って喜ぶところではない。うちはFALSEを書き忘れないように、VLOOKUPを打つときは先に,FALSE)まで打ってから中身を埋めることにしている。

TRIMで取れない空白がある

前に空白が付いているだけならTRIMで取れる、と思っていた。取れないものがあった。

4種類の空白で長さを測った。半角の空白を前に付けた「 1005」は5文字で、TRIMのあと4文字。全角の空白を付けたものも、TRIMで4文字になった。日本語のExcelでは全角の空白も取れる。

CHAR(160)の空白を付けたものは、TRIMを通しても5文字のままだった。改行を末尾に付けたものも5文字のまま。この2つはTRIMでは落ちない。

=SUBSTITUTE(SUBSTITUTE($A2,CHAR(160),""),CHAR(10),"")

CHAR(160)は改行なしの空白と呼ばれるもので、ウェブページや一部のシステムから貼り付けると混ざる。見た目は空白と変わらない。LENで数えて桁が合わないのにTRIMで直らないときは、これを疑う。

直す式は4つを重ねる

原因が複数あるので、直す式も重ねる。

=VLOOKUP(VALUE(TRIM(SUBSTITUTE(SUBSTITUTE(ASC($A3),CHAR(160),""),
  CHAR(10),""))),マスタ!$A:$B,2,FALSE)

内側から順に、ASCで全角を半角にそろえ、SUBSTITUTEで改行なしの空白と改行を落とし、TRIMで前後の空白を取り、VALUEで文字列を数値に直す。

順番に意味がある。ASCを先に通さないと、全角の数字はVALUEで数値にならない。TRIMを先に通すとCHAR(160)が残ったままになるので、SUBSTITUTEが先だ。

マスタの1列目が文字列ならVALUEは外す。数値に直してしまうと、今度は型が合わなくなる。マスタが数値ならVALUEを付ける。どちらかに寄せるのであって、両方を試すわけではない。

直せるのか、本当に無いのかを列で分ける

#N/Aが出た行を1件ずつ見ていくと時間がかかる。直せるものと直せないものを、式で分ける。

=IF($B3<>"#N/A","そのまま見つかる",
  IF($C3<>"#N/A",IF($E3>0,"余計な文字が付いている","型か全角半角のちがい"),
  "マスタに無い"))

B列がそのままの結果、C列が直した式の結果、E列が余計な文字の数だ。3つの組み合わせで見立てが決まる。

「余計な文字が付いている」と出たら、検索値を作っている元のほうを直す。貼り付けのたびに手で直していると、来月も同じことになる。取り込みの段階でSUBSTITUTEを挟んでおく。

「型か全角半角のちがい」なら、入力の段階でそろえる。入力規則で選ばせる話のように一覧から選ばせれば、そもそも起きない。

「マスタに無い」が出たら、マスタを更新するか、その行を別扱いにする。名簿の突き合わせが一致しない話で書いたとおり、無い値を無理に当てにいくと、別の人の情報が入る。

IFERRORで隠すと、直せる64件も見えなくなる

#N/Aが並ぶのが嫌で、=IFERROR(VLOOKUP(...),"")と書いて空にすることがある。印刷したときにきれいになるので、そうしたくなる。

今回の200件でこれをやると、72件が空欄になる。そのうち64件は、直せば名前が入る行だ。空欄になった時点で、直せるものと直せないものの区別が消える。

隠すなら、空文字ではなく"要確認"のような文字にする。あとで数えられるし、印刷しても目に入る。

=IFERROR(VLOOKUP($A3,マスタ!$A:$B,2,FALSE),"要確認")

#N/Aのまま残すのがいちばん安全だが、そのセルを合計したり別の式で参照したりしていると、そちらまでエラーになる。合計に入れる列なら0、人が見る列なら"要確認"と使い分けている。

XLOOKUPなら既定が完全一致になる

Microsoft 365とExcel 2021以降にはXLOOKUPがある。手元で動かして確かめた。

=XLOOKUP(1002,$A$1:$A$3,$B$1:$B$3)と書くと、第4引数を書かなくても完全一致で探す。VLOOKUPのように近似一致に落ちない。

見つからないときの値も指定できる。=XLOOKUP(9999,$A$1:$A$3,$B$1:$B$3,"なし")で「なし」が返った。IFERRORで包まなくていい。

同じ3行の表で=VLOOKUP(9999,$A$1:$B$3,2)と書くと、最後の行の値が返る。3行でも200行でも同じことが起きる。

ただしExcel 2019以前では使えない。社内に古いExcelが残っているなら、XLOOKUPで作ったブックは開いた先で#NAME?になる。配るブックはVLOOKUPのままにしている。

マスタの側を直すほうが早いこともある

検索値を直す式を書くのは、検索値の側を触れないときだ。毎月同じシステムから同じ形で落ちてくるなら、そちらは直せない。

マスタが自社で作っているものなら、マスタの側を検索値に合わせるほうが早いこともある。社員番号を数値で持っているのに、システムからは文字列で落ちてくるなら、マスタに文字列の列を1つ足して、そこを探しにいく。

=TEXT($A3,"0000")

どちらに寄せるかは、変えられないほうに合わせて決める。両方が変えられるなら数値に寄せる。文字列の番号は、並べ替えたときに1000の次に10000が来るので、あとで困る。

確かめていないこと

測ったのは、検索値の表記が原因で合わないときの分け方だけだ。マスタの側に同じ番号が2つあるときや、範囲を絶対参照にしていなくて下にコピーしたときにずれる場合は、この診断表では見ていない。

範囲のずれは、式をコピーしてから#N/Aが下のほうだけ出る、という形になる。1行目は合っているのに途中から合わなくなったら、範囲に$が付いているかを先に見る。

マスタの重複は、配ったブックとは別に数える。配った控えと突き合わせる話で使っている重複の式がそのまま使える。VLOOKUPは最初の1件しか見ないので、2件目があることに気づけない。