VLOOKUP関数でエラーが出た？#REF!エラーなどの解決法を詳しく解説

【関連記事】VLOOKUPとは？使い方や活用方法をわかりやすく解説

【関連記事】Excel（エクセル）の関数30選！よく使う基本の関数を一覧で紹介｜CANVAS

VLOOKUP関数でエラーが出た？

VLOOKUP関数は、Excelでよく使用される関数の1つで、表データを縦方向に検索し、特定のデータに対応する値を取り出す機能です。顧客情報や商品情報など、様々なデータの分析や整理に役立ちます。

その一方、構文に誤りがあったり検索範囲が違っていたり、あるいは検索キーと照合する値の型が違っていたりすると、該当するデータがあるのにヒットしないなど、エラーが出ます。エラーの原因が分からずVLOOKUPが苦手になった人がいるかもしれません。

しかし、VLOOKUPは使いこなせると大変便利です。またエラーの大半はパターンが決まっており、それさえ押さえればエラーに悩むことはなくなります。

この記事では、VLOOKUPでありがちなエラーのパターンとその原因、対処法について解説しますので、ぜひ参考にしてください。

【参考】：VLOOKUP 関数  ｜ Microsoft サポート

VLOOKUPで発生しがちなエラー

VLOOKUP関数は仕事でよく利用されるExcelの関数であり、表の範囲を指定したキーで縦に検索し、ヒットした値を取り出します。

しかし、VLOOKUP関数には引数が4つもあり、どれか1つでも間違っていたり、表自体にイレギュラーなデータが登録されていたりすると、すぐにエラーが出ます。VLOOKUP関数のエラーで悩まされた経験がある方は少なくないでしょう。

ここでは、VLOOKUP関数でよくありがちなエラーについて見ていきましょう。

VLOOKUP関数でよく発生するエラーと、その意味を確認しておきましょう。

▪ #N/Aエラー 「N/A」は「No Assign」のことで、検索キーに合致する有効なデータが見当たらない場合に発生します。

▪ #VALUEエラー VLOOKUP関数の3つ目の引数である「列番号」に文字列が入っているか、または1未満の値が入っている場合に発生します。

▪ #REFエラー 「REF」は「Reference（参照）」の略で、VLOOKUP関数の参照先であるセル範囲や列番号が間違っている場合に発生します。

#N/Aエラーは、VLOOKUP関数の数式で参照している数値が存在しないことを表すエラーです。#N/Aエラーが起きた時に確認すべきことと、その解決策について紹介します。

次のExcel表は、商品コードを入力して、商品名と単価を調べるVLOOKUP関数の例ですが、商品名欄と単価欄に#N/Aエラーが表示されています。

この#N/Aエラーが発生した場合には、次の3つがエラーの原因と考えられますので、それぞれ確認します。

▪ 検索キーのスペルミスを確認 最も多い原因としては、検索キーのスペルミスが想定されます。英数字以外の文字（記号）やスペースなどが入っていないか確認しましょう。上のケースでは検索する商品コード欄がスペースになっています。

▪ 検索範囲を確認 検索範囲が間違っている場合にもこのエラーが表示されます。特に、検索キー列が間違っていないか、よく確認してみましょう。

▪ 検索キー列が空白かどうか確認 検索キーが入力されていない場合、検索キーと一致するセルが見当たらない場合にも、#N/Aエラーが発生します。すべてのセルに正しく値が入力されていることを確認しましょう。

【参考】：VLOOKUP 関数の #N/A エラーを修正する方法｜Microsoft サポート

VLOOKUP関数で#VALUEエラーが発生するのは、入力した数式や参照先のセルに問題があるためです。#VALUEエラーが起きた時に確認すべきことと、その解決策について紹介します。

次のExcel表では、商品コードを入力しているにもかかわらず、商品名欄に#VALUEエラーが表示されています。

#VALUEエラーが発生した場合には、列番号引数の値を確認します。　　

▪ 列番号引数を確認 このエラーの多くは、3番目の列番号引数の値に、1未満の数字（0）が入っていることが原因です。正しい列番号を指定しましょう。このケースでは、商品名は列番号 "2"、単価の列番号は "3" が正しい数値です。

▪ 構文にミスがないか確認 VLOOKUP関数の()内に記述する引数はそれぞれ ","（カンマ）で区切りますが、参照範囲と列番号の間のカンマが漏れているケースでも#VALUEエラーが発生します。

▪ VLOOKUPの前にマイナスが入っていないか確認 偶然、VLOOKUP関数の頭に "-"（マイナス）が入っても、同様に#VALUEエラーが発生します。マイナス記号は小さく見つけにくいため、発見しにくいですが、表示を拡大して構文をチェックするとよいでしょう。

【参考】：VLOOKUP 関数の #VALUE! エラーを修正する方法｜Microsoft サポート

#REFエラーもVLOOKUP関数ではよく見かけるエラーです。#REFエラーエラーは、参照範囲と指数の値に矛盾があると起こります。その原因と解決策を見ていきましょう。次の表では商品名欄に#REFエラーが表示されています。

#REFエラーが発生した時は、次の2つの点について確認してみましょう。

▪ 列番号がセル範囲を超えていないか確認 列番号が参照範囲を超えていないか確認しましょう。上記の例では、商品名の列番号は参照範囲の左から2番目ですので、本来は "2" ですが、"4" が指定されたために#REFエラーとなっています。

▪ 参照範囲が誤っていないか確認 VLOOKUP関数で参照している範囲と、検索値の存在する範囲が整合しているか確認してください。範囲が整合していない場合、エラーが発生します。

【参考】：エラー値 #REF! を修正する方法｜Microsoft サポート

VLOOKUP関数を利用する際の注意点

VLOOKUP関数は便利な検索関数ですが、正しく利用する上でいくつか注意すべき点があります。これからVLOOKUP関数で思うような結果が得られない場合によくあるエラーについて解説をしていきますので、参考にしてください。

VLOOKUP関数は、検索範囲の1番左の列を検索キーとして使用します。そのため、検索キーの列が左端以外の列にある場合は、エラーが発生します。

例： 以下の表では、商品コードから商品名と単価を検索する場合、商品マスターの商品コードは表の1番左、C列に配置されている必要がありますが、E列に配置されているため、#N/Aエラーとなっています。

解決策： 上記の表では、商品コードのE列と、単価のC列を入れ替えます。これで正しく検索が行えます。

VLOOKUP関数の数式を他のセルにコピーした際に、一部だけエラーが出ることがあります。それは範囲指定が相対参照になっているため、コピーしたときにズレてしまうことが原因です。

例： 以下の表で、商品コードをキーに売上検索を行っていましたが、検索欄をコピーしてもう1箇所検索欄を増やしたところ、商品名と売上が#N/Aエラーとなりました。

コピーした "VLOOKUP関数" の引数を確認してみると、参照先が "G6:I10" となっており、データが存在しないセルを参照しています。本来は "C6:E10" の範囲を参照すべきですが、ズレたのは相対参照になっていたからです。

元のVLOOKUP関数の参照先を "$C$6:$E$10" と絶対参照にしておけば、コピーしても参照先がズレることはありませんでした。以下の表では絶対参照にしてからコピーしたため、参照先は変わっておらず、エラーが出ていません。

VLOOKUPに適するリストを作成する際、最も大切なことは、IDになる列を1番左端に設けることです。このIDはユニークである必要があります。IDに重複があると、正しい値を発見することができません。IDの重複では最初にヒットしたものしか見つかりませんので、注意しましょう。

このVLOOKUP関数を利用した場合、検索対象が見当たらない、無効な値のエラーの際に#N/Aエラーが表示されます。構文や参照範囲に誤りがない場合、対象データが見つからない時に#N/Aエラーが表示されるのは正しい姿です。

しかし取引先や顧客に提出する資料などに#N/Aエラーが表示されるのは避けたいところです。

こうした事態を回避するには、IFERROR関数を組み合わせることで、エラーを表示しないようにしたり、他の文言に置き換えたりすることができます。構文は次のように記述します。

=IFERROR(VLOOKUP(検索値,範囲,列番号,検索の型),"")

エラーが発生した時に、文言を表示したい場合には "" の間に文言を記述すれば、常にその文言が表示されます。例えば、"" の間に0を記述すれば、#N/Aエラーは常に表示を0にすることができます。

VLOOKUP関数のエラーをなくそう

この記事ではVLOOKUP関数でありがちなエラーについて、その原因と対策、VLOOKUP関数を利用する際の注意点などを解説しました。

VLOOKUP関数は便利な関数ですが、エラーが出ると、そのエラーつぶしで時間が掛かることがあり、結果的にその利便性を損なうことになります。VLOOKUPを使いこなしたい方はぜひエラー対処のスキルを高め、エラーをなくすよう努めましょう。

その他VLOOKUP関連記事

その他、VLOOKUPに関連する内容は、下記の記事でもご紹介しています。ぜひ参考にご覧ください。

VLOOKUP関数で複数条件を指定する？簡単にできる方法を図解

VLOOKUPで別シートを参照するには？記述方法や実践例を解説

VLOOKUP関数でエラーが出た？#REF!エラーなどの解決法を詳しく解説

VLOOKUP関数がうまく反映されない？その原因と対策を図解

VLOOKUPとIFの組み合わせで何ができる？便利な使い方を解説

VLOOKUPで部分一致検索するには？ワイルドカード使用法を解説

VLOOKUPとXLOOKUPの違いとは？使い方を図解で解説

VLOOKUPで0を表示しない方法とは？発生原因と対処法を解説

Pythonでのエクセル操作とは？できることや自動化、導入方法を紹介