ExcelのVLOOKUPがうまくいかない原因7つ｜#N/Aや誤表示の直し方
はじめに
VLOOKUPは便利ですが、実務ではうまく動かない理由が分かりにくい関数でもあります。#N/A が出る、別の商品名が返る、列を追加したら結果がずれた。このようなトラブルは非常によくあります。
この記事では、VLOOKUPで起きやすい問題を原因別に切り分けて直す手順をまとめます。
この記事でわかること
- VLOOKUPで起きやすい7つの原因
- エラーと誤表示の見分け方
- どこから確認すればよいかの順番
対象となる人
- 既存ファイルにVLOOKUPが多く入っている方
- エラーの原因が特定できず止まっている方
- XLOOKUPではなくVLOOKUPの保守が必要な方
結論
VLOOKUPの不具合は、ほとんどが検索値・検索範囲・列番号・一致条件のどれかに集約されます。
闇雲に式を書き換えるのではなく、1つずつ切り分けると短時間で直せます。
原因1 検索値が左端列にない
VLOOKUPは、指定した範囲の左端列 で検索します。
検索したいコードが範囲の2列目以降にあると、正しく動きません。
=VLOOKUP(A2, F2:H100, 2, FALSE)この場合、検索値A2は F列 で探されます。
検索対象がG列にあるなら、範囲指定自体を見直す必要があります。
原因2 列番号がずれている
VLOOKUPの第3引数は、範囲内で何列目を返すか です。
シート全体の列番号ではない点で混乱しやすいです。
たとえば F2:H100 の範囲なら、
- F列 = 1
- G列 = 2
- H列 = 3
です。
途中で列を挿入したときも、この列番号ズレが起きやすくなります。
原因3 完全一致になっていない
第4引数を省略すると、意図しない近似一致になることがあります。
実務では、コード検索のほとんどが完全一致なので、基本は FALSE を入れてください。
=VLOOKUP(A2, F2:H100, 2, FALSE)原因4 検索値の前後に空白がある
コードや名称に見えない空白があると、一致しません。
特にCSV貼り付けやシステム出力ではよく起きます。
検索値側、マスタ側の両方で、次のように整形して確認すると原因を見つけやすいです。
=TRIM(CLEAN(A2))原因5 数値と文字列が混在している
見た目は同じ 1001 でも、片方は数値、片方は文字列ということがあります。
この場合、見た目では一致していてもVLOOKUPではヒットしません。
確認方法の一例は次のとおりです。
- 左寄せか右寄せかを確認する
ISTEXTやISNUMBERで判定する- 必要に応じて文字列化、数値化する
原因6 参照範囲が固定されていない
数式を下にコピーしたときに、参照範囲がずれてしまうケースです。
この場合は絶対参照にします。
=VLOOKUP(A2, $F$2:$H$100, 2, FALSE)テーブルを使う場合は、このズレが起きにくくなります。
原因7 そもそもマスタにデータがない
一番基本ですが、実際によくあります。
検索対象のコード自体がマスタに未登録なら、式を直しても返りません。
まずは検索対象の値が存在するかを、フィルターや検索で確認してください。
まず確認する順番
迷ったら、次の順で見てください。
- 第4引数が
FALSEか - 検索値が左端列にあるか
- 列番号が正しいか
- 空白や見えない文字がないか
- 数値と文字列が混在していないか
- 参照範囲が固定されているか
- マスタに値が存在するか
コピペ用関数
=VLOOKUP(A2, $F$2:$H$100, 2, FALSE)
=TRIM(CLEAN(A2))
=ISTEXT(A2)
=ISNUMBER(A2)つまずきポイント
- シート全体の列番号で考えてしまう
- 第4引数を省略してしまう
- 値は同じに見えるのに型違いに気づけない
注意点
- 既存ファイルの保守ではVLOOKUPが残っていることが多いです。
- 新規作成ならXLOOKUPの方が読みやすい場面があります。
- エラーが出ているときほど、式を一気に変えず原因を分解して確認してください。
まとめ
VLOOKUPのトラブルは、原因を順番に切り分ければかなりの確率で直せます。
特に 完全一致・左端列・列番号・空白・型違い の5つは、最初に確認する習慣を付けると実務で強いです。
