VLOOKUPがうまく反映されない場合の原因と対処法！エラーと値のズレを6つに分けて解決

けど、そんな中で悩むことは、

ですよね。

今回はそんなお悩みを解決する

についてまとめます！

完成イメージ

VLOOKUPは検索値・範囲・列番号・検索方法の4つがそろって初めて正しく動きます。
今回は
・「反映されない」原因を6つに分けて特定し、その場で直せるようになること
をゴールにします。
#N/Aが出る原因も、値がズレる原因も、1つずつ見ていけばこわくありません。

上の画像が完成形です。検索コードを入れると、商品名と単価が正しく引けるようになります。

なお、入力の手間を減らすVBAと、つまずきやすいポイントのトラブルシュートもあわせてご紹介します。
早速実装して試してみましょう！

STEP1. マスタ表を用意する

まずは検索される側の商品マスタを用意します。
A列が商品コード、B列が商品名、C列が単価です。
VLOOKUPは範囲の左端の列をキーにして探すので、商品コードは必ず左端のA列に置きます。
商品コードは重複させず、1商品につき1行にしておきましょう。

下の表をコピーして、Excelのシートに貼り付けるとそのまま試せます。

※ 数式で求める列は空にしています。すでに値が入っている列はそのまま残して試せます。

STEP2. 値を引いてくる検索表を作る

次に、値を引いてくる検索表をA13セルから作ります。
ここでは調べたい商品コードだけを入力し、商品名と単価の列は空のままにしておきます。
今回は「うまく反映されない」例を見たいので、あえて文字列のコードや存在しないコードも混ぜてあります。

STEP3. VLOOKUP関数を入力する

商品名を出したい B14セル に、次の数式を入力します。

=VLOOKUP(A14, $A$2:$C$11, 2, FALSE)

A14が検索値、$A$2:$C$11が検索する範囲、2が範囲の左から2列目（商品名）、FALSEが完全一致の指定です。
実際の入力画面がこちらです。
入力中は引数のヒントが表示されるので、順番を確認しながら進められます。

STEP4. 下の行にコピーして結果を確認する

B14セルの数式をB19までコピーし、単価も C14セル に =VLOOKUP(A14,$A$2:$C$11,3,FALSE) と入力してコピーします。
すると、いくつかの行で #N/A が表示されました。
#N/Aは「該当する値が見つからなかった」という意味のエラーです。
「反映されない」と感じるときの多くは、この#N/Aが原因です。
ここからは、なぜ見つからないのかを1つずつ確認していきます。

STEP5. 原因① 検索値の型と余分なスペースをそろえる

A16セルとA17セルに注目してください。
A16は「104」、A17は「105 」で、見た目は数字ですが文字列として入力されています。
文字列はセルの左寄せ、数値は右寄せになるので、位置で見分けられます。
VLOOKUPは文字列の「104」とマスタの数値104を別の値として扱うため、見つけられません。
また、後ろに半角スペースが1つ入っているだけでも、別の値とみなされます。
対処は、検索値の型と余分なスペースをそろえることです。
数値は数値として入力し直し、スペースは =TRIM() や「検索と置換」で取り除きます。

STEP6. 原因② 列番号のズレと検索方法（近似一致）

VLOOKUPが返す値は、範囲の左端から数えた列番号で決まります。
単価を出したいのに列番号を 2 にすると、2列目の「商品名（文字列）」が返ってしまいます。
また、4つ目の引数（検索方法）を 省略 すると近似一致になり、存在しないコードでも「近い値」を返します。
下の表で、正しい式とズレた式の結果を比べてみましょう。

STEP7. 原因③ 範囲の絶対参照（$）忘れ

数式を下や横にコピーすると、範囲が相対的にずれていきます。
すると途中の行から検索範囲がデータの外にはみ出して、#N/Aになったり、違う値が返ったりします。
これを防ぐには、範囲を絶対参照にします。
$A$2:$C$11 のように、列と行の前に「$」を付けます。
数式を入力している途中に F4キー を押すと、自動で「$」が付くので便利です。

STEP8. 検索値を直して完成させる

原因がわかったので、検索値を直します。
文字列だったA16を数値の 104 に、A17を数値の 105 に直し、存在しない999を実在する 107 に変更します。
単価の列もそろえると、どの行も商品名と単価が正しく引けるようになりました。
これで「反映されない」状態が解消です。

「反映されない」6つの原因と対処（早見表）

ここまでで見てきた原因を一覧にまとめました。
つまずいたときは、この表に戻って確認してみてください。

VBAでVLOOKUPを一括設定する

件数が多くて数式を1つずつ入れるのが大変なときは、VBAで一括設定するとラクです。
検索表のB列とC列に、商品名と単価をまとめて入力します。

Sub SetVlookup()
    Dim r As Long
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For r = 14 To lastRow
        Cells(r, 2).Formula = "=VLOOKUP(A" & r & ",$A$2:$C$11,2,FALSE)"
        Cells(r, 3).Formula = "=VLOOKUP(A" & r & ",$A$2:$C$11,3,FALSE)"
    Next r
    Range("B14:C" & lastRow).NumberFormat = "#,##0"
End Sub

タカヒロ

数式の中の FALSE を忘れずに入れましょう。
ここを省くと近似一致になって、返ってきた値が正しいのか判断できなくなります。
迷ったら「VLOOKUPは最後にFALSE」と覚えてしまえば、カンタンですね。

うまくいかないときの対処法（トラブルシュート）

実際に試すと、思ったように動かないこともあるかもしれません。
よくあるつまずきと対処をまとめました。

関数の説明

今回の主役、VLOOKUP関数の概要です。

VLOOKUP関数（ブイ・ルックアップ）

=VLOOKUP(A14, $A$2:$C$11, 2, FALSE)

完成

検索値を直すと、#N/Aが消えて商品名と単価が正しく反映されました。
思ったとおりに動きましたか？
原因を1つずつ確認すれば、VLOOKUPのエラーはこわくありません。
今回のポイントは、完全一致（FALSE）と絶対参照（$）、そして検索値の型をそろえることの3つです。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

まずは今回のサンプル表で、ぜひ一度試してみてください。
「完全一致（FALSE）」と「絶対参照（$）」を押さえるだけで、VLOOKUPのトラブルはぐっと減りますよ。
また、他にも便利なExcelの時短テクニックがありますので、よろしければご参照頂ければと思います。