VLOOKUP関数の#REF!エラー原因と対処法！参照が壊れる理由と直し方！

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

ですよね。

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

についてまとめます！

完成イメージ

VLOOKUP関数の #REF! エラーは、数式が指している参照先が壊れているときに表示されます。
とくに多いのが、参照範囲の外の「列番号」を指定したときと、参照していたセルや列を削除したときです。
今回は
・VLOOKUPの #REF! の原因を特定して、正しく直す方法
をゴールにします。
#REF! と #N/A・#VALUE! の違いも、見分けられるようになります。

上の画像が完成形です。#REF! だった数式を直し、存在しない商品コードは「該当なし」と表示されるようになりました。

なお、参照がずれない絶対参照の書き方や INDEX/MATCHへの置き換え、VBAで参照切れを一括チェックする方法もあわせてご紹介します。
早速実装して試してみましょう！

STEP1. 商品表を用意する

まずはVLOOKUPで検索する元になる商品表を用意します。
A列が商品コード、B列が商品名、C列がカテゴリ、D列が単価、E列が在庫数です。
1行目が見出し、2行目以降がデータで、10件並んでいます。
この商品コードをたよりに、商品名や単価を取り出すのが今回のゴールです。

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

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

STEP2. VLOOKUPで商品名を検索する

次に、検索したい商品コードを入力する小さな表を作ります。
今回は商品表の下に、G列から検索用の表を用意しました。
G14セルに P-005 と入力し、H14セルに商品名を取り出す数式を入れます。
H14セルに入れる数式はこちらです。

=VLOOKUP(G14, $A$2:$E$11, 2, FALSE)

引数は「検索値・範囲・列番号・検索方法」の4つです。
G14の商品コードを、$A$2:$E$11の中から探して、2列目（商品名）を返します。
最後の FALSE は「完全に一致するものだけを探す」という意味です。
セルを選んで数式バーに入力します。
実際の入力画面がこちらです。

STEP3. 検索結果を確認する

H14セルに商品名が表示されました。
同じように、単価も隣のI14セルに取り出しておきます。

=VLOOKUP(G14, $A$2:$E$11, 4, FALSE)

4列目を指定すると単価が返ります。
あとはH14:I14を下の行にコピーすれば、複数の商品をまとめて検索できます。
P-005はプリンタ、P-008は外付けHDDが正しく取り出せました。
ここまではエラーが出ずに動いています。

STEP4. #REF!の原因① 列番号が範囲より大きい

ここで、あえてエラーを出してみます。
P-003の行（16行目）で、数式の列番号を7に変えてみましょう。

=VLOOKUP(G16, $A$2:$E$11, 7, FALSE)

すると、H16セルに #REF!エラー が表示されました。
参照範囲は A2:E11 の5列しかないのに、7列目を取り出そうとしたためです。
VLOOKUPの列番号は、範囲の列数を超えると #REF! になります。
「指定した列が存在しない」というサインなのです。

STEP5. #REF!の原因② 参照先の列を削除した

もうひとつ、よくある原因が参照していたセルや列を削除したときです。
下の画像は、K28セルの商品コードを検索して、L28セルで商品名を取り出す例です。
この状態では、正しく「プリンタ」が返っています。
ここで、参照しているK列（商品コード）を丸ごと削除してみましょう。

K列を削除すると、L28セルの数式は =VLOOKUP(#REF!, $A$2:$E$11, 2, FALSE) に変わりました。
参照していたセルそのものが無くなったため、数式はどこを見ればよいか分からず #REF! になります。
このように、参照していた列や行そのものを削除すると、数式の参照は #REF! に置き換わります。
参照するセルの一部が消えただけのときは、範囲が自動で縮んで別の列を指してしまうこともあるので注意しましょう。

STEP6. #REF!と#N/A・#VALUE!の違い

エラーにはいくつか種類があり、意味がそれぞれ違います。
今回の表でも、17行目に存在しない商品コード P-999 を入れて #N/A を出してみました。
エラーの見分け方を表にまとめました。

#REF! は「参照そのものが壊れている」サインで、#N/A は「参照先は正しいが、データが見つからない」サインです。
この違いを押さえると、原因の切り分けがぐっとラクになります。

STEP7. 対処法① 絶対参照でコピー時のズレを防ぐ

#REF! や #N/A を防ぐコツが、範囲を $（ドルマーク） で固定する絶対参照です。
数式を右や下にコピーすると、範囲が勝手にずれてしまうことがあります。
下の画像の左側は、範囲を B2:F11（相対参照のまま右にずれた状態）にした例です。
商品コードを探す列がずれてしまい、見つからずに #N/A になりました。
右側は、範囲を $A$2:$E$11 に固定した例です。
こちらはコピーしても範囲が変わらないので、正しく「プリンタ」が返ります。
範囲の指定は $A$2:$E$11 のように固定しておくのが安全ですね。

STEP8. 対処法② INDEX/MATCHに置き換える

列番号で指定するVLOOKUPは、途中に列を挿入・削除すると番号がずれてしまいます。
そこで、列番号を使わない INDEX関数＋MATCH関数 への置き換えがおすすめです。
商品名を取り出す数式はこちらです。

=INDEX($B$2:$B$11, MATCH($G$14, $A$2:$A$11, 0))

MATCHで「商品コードが何行目にあるか」を調べ、INDEXで「その行の商品名」を取り出します。
列を挿入しても自動で追従するので、レイアウトが変わりやすい表でも安心です。
実際の入力画面がこちらです。

STEP9. VBAで参照切れを一括チェックする

数式が多いシートでは、壊れた参照を1つずつ探すのは大変です。
そんなときは、VBAでエラーになっているセルをまとめて見つけるのがおすすめです。
Alt+F11でVBAエディターを開き、標準モジュールに次のコードを貼り付けます。

Sub CheckRefError()
    Dim c As Range
    For Each c In ActiveSheet.UsedRange
        If IsError(c.Value) Then
            c.Interior.Color = RGB(255, 199, 206)
            c.Font.Color = RGB(156, 0, 6)
        End If
    Next c
End Sub

実行すると、#REF! や #N/A になっているセルが赤く塗られて一目で分かります。
ボタンに登録しておけば、クリック1つで参照切れをチェックできます。

タカヒロ

IsError は「そのセルがエラーかどうか」を判定する命令です。
エラーのセルだけを色付けするので、どこを直せばいいかがすぐ分かります。
参照切れの確認はこれ1つで済むのでカンタンですね。

STEP10. #REF!を直して完成させる

最後に、壊れてしまった数式を直しましょう。
#REF! が出ているH16セルは、列番号を範囲内の 2 に戻せば直ります。

=VLOOKUP(G16, $A$2:$E$11, 2, FALSE)

#N/A が出ているH17セルは、値が見つからないだけなので参照は壊れていません。
見つからないときに「該当なし」と表示するなら、IFERROR関数 で包みます。

=IFERROR(VLOOKUP(G17, $A$2:$E$11, 2, FALSE), "該当なし")

これで、エラーの原因ごとに正しく対処できました。

完成

#REF! だった数式を直して、商品名が正しく取り出せるようになりました。
P-003はキーボード、存在しないP-999は「該当なし」と表示されています。
思ったとおりに動きましたか？
#REF! が出たら、まず「参照が壊れていないか」を疑うのが近道です。
原因ごとに対処できると、数式のトラブルにも落ち着いて対応できますね。

さいごに

いかがでしょうか。

今回は、

についてまとめました。

まずは今回のサンプル表で、ぜひ一度試してみてください。
#REF! を見かけたら、列番号が範囲内か、参照先を削除していないかを順に確認してみてくださいね。
また、他にも便利なExcelの時短テクニックがありますので、よろしければご参照頂ければと思います。