Aさん VLOOKUP関数を使っていてエラー表示が出ると、どうすれば解決できるのか、いつも悩みます。。
森田 気持ちはすごくわかります。
私もVLOOKUP関数を使い始めの頃は数式やワークシートの値を何度も見返したりしてエラーの原因を見つけるのに悪戦苦闘していました(笑)
実際に、エラーの種類は大別すると7種類あります。
今回はそれぞれの原因と解決策について解説していきますね。
はじめに
本題に入る前に、この記事がおすすめな人を挙げてみます。
- VLOOKUP関数を使う頻度が高い人
- VLOOKUP関数に限らず、関数のエラー解消に困っている人
- 事務職の人
ちなみに、最低限「VLOOKUP関数の基礎」を把握している想定での記事となります。
※自信がない方はこちらの記事で学んでください。
→【初心者向け】データ転記の作業時間を9割カット！VLOOKUP関数の使い方
VLOOKUP関数でエラーになるケースは全部で7種類
VLOOKUP関数を使っていると、どんなに慣れている方でもエラーが発生することをゼロにできるわけではありません。
私自身も、数式の”,”[カンマ]の漏れや、参照するセルを指定間違えなどが原因でエラーを起こすことがしばしばあります。
実は、エラーを起こさないことではなく、「エラーを解決するリカバリ作業を迅速にできるかどうか」が重要なのです。
このエラーのリカバリ作業を迅速にできるかどうかが、エクセル関数の上級者かどうかの分かれ道といえます。
使う頻度の高いVLOOKUP関数だけでも、このエラー解消が問題なくできるようになるために、まずは全体像を把握しましょう。
そのために、VLOOKUP関数でどんなエラーが起きるか、エラー結果を基準に全7種類を以下のとおり表にまとめてみました。
| No. | 数式確定 | エラー（戻り値） | エラー結果 |
|---|---|---|---|
| 1 | × | – | 「この数式には問題があります～」 というメッセージが表示 |
| 2 | 〇 | × | 戻り値が”#NAME?”というエラー値 |
| 3 | 〇 | × | 戻り値が”#VALUE!”というエラー値 |
| 4 | 〇 | × | 戻り値が”#REF!”というエラー値 |
| 5 | 〇 | × | 戻り値が”#N/A”というエラー値 |
| 6 | 〇 | 〇 | 戻り値が”0”の値 |
| 7 | 〇 | 〇 | 戻り値がユーザーの想定外の値（”0”以外） |
それでは、1つずつエラー結果ごとの原因とその解決策について解説していきます。
【エラー1】「この数式には問題があります～」というメッセージが表示
1つ目のエラーは、メッセージが表示されて数式の確定すらできないエラーです。
このメッセージとは、具体的には以下の内容です。
この数式には問題があります。
数式を入力していない場合は次の点に注意します。
最初の文字に等号(=)やマイナス記号(-)を使用している場合は、数式として認識されます。
- 入力内容:=1+1、セルの表示:2
これを回避するには、先頭に単一引用符(‘)を入力します。
- 入力内容:’=1+1、セルの表示:=1+1
原因
このメッセージが表示される原因は、数式の中で文字列・数値・セル範囲のいずれにも該当しない値を入れてしまった場合です。
たとえば、以下のように数式中の”,”[カンマ]が抜けてしまうことが主な原因です。
上記は引数「検索値」と引数「範囲」の間の”,”[カンマ]が漏れたため、引数「検索値」が”$I1$A$2:$G$11”という文字列・数値・セル範囲のいずれにも該当しない値になっていますね。
よって、VLOOKUP関数の引数で入力できるルール外のため、数式が確定できていないというわけです。
”,”[カンマ]の入力漏れが原因で同じエラーが出る場合は、主に以下の2つが挙げられます。
- 引数「検索値」と引数「範囲」の間
- 引数「列番号」と引数「検索方法」の間
（引数「検索方法」がFALSE/TRUEの形式で入力していた場合）
なお、このメッセージを消さないことには、数式を確定できないため、その場で解決するか、いったん数式をすべて消去する必要があります。
解決策
解決策は、シンプルに数式を見直して該当部分を然るべき内容へ修正することです。
そのために、重要なのはエラーの原因部分をどう判断するかですが、そのポイントは以下の2点です。
- VLOOKUP関数の引数の”,”[カンマ]の入力漏れがないか
- 数式内に文字列・数値・セル範囲のいずれにも該当しない値が入っていないか
つまり、1であれば、引数「検索値」と引数「範囲」の間の”,”[カンマ]を入れてあげればエラーが解消されますね。
そして、2は1が解消すれば解決するはずですが、もし”,”[カンマ]自体の漏れがなければ、各引数で文字列・数値・セル範囲以外の不自然な値があれば修正してあげればOKです。
特に、他の関数と組み合わせている場合は、”()”[カッコ]や”,”[カンマ]が増えて数式が読みにくくなりますので、より注意してチェックしていきましょう。
【エラー2】戻り値が”#NAME?”というエラー値
2つ目のエラーは、戻り値が”#NAME?”というエラー値です。
読み方は「ネーム」と呼びます。
なお、エラー2~5は数式の確定はできますが、VLOOKUP関数の戻り値がエラー値になってしまうものです。
原因
このエラーは以下のような場合に表示されます。
- 関数名の入力ミス
- 引数部分で”[ダブルクオーテーション]なしの文字列がある
続いて、具体例として実際の画面も見ていきましょう。
上記のように、数式の関数名を「VLOKUP」のようにスペルミスがあると発生します。
解決策
解決策は、シンプルに数式を見直して該当部分を然るべき内容へ修正することです。
その際、特に注視すべきポイントは以下の2点です。
- 関数名が正しいか
- 数式内に文字列がある際に”[ダブルクオーテーション]が左右に1つずつあるか
なお、1の関数名の部分は、以下のように半角モードで”=v”と入力すれば、Vから始まる関数が入力候補として表示されるため、その中からTabキーで選択するようにすれば、関数名のスペルミスが起きることはなくなりますよ。
- VLOOKUP関数を入れたいセルに”=v”と入力
- 候補の関数一覧から「VLOOKUP」をTabキーで選択
- VLOOKUP関数が自動入力されるため、後は任意の引数を設定
あとは、すでに設置されているVLOOKUP関数名をいじってしまうことがないように数式内の修正を行う際に注意すればOKです。
最後の2は、文字列をしている引数があれば”[ダブルクオーテーション]が左右に1つずつない、あるいは2つ以上あるという場合は、1つずつに修正してあげましょう。
【エラー3】戻り値が”#VALUE!”というエラー値
3つ目のエラーは、戻り値が”#VALUE!”というエラー値です。
読み方は「バリュー」と呼びます。
原因
このエラーは以下のような場合に表示されます。
- 引数「列番号」が”0”やマイナスの数値になっている
- 引数「列番号」に数値以外の値が指定されている
（引数「列番号」は数値を指定しなければならない） - 引数「検索方法」に論理値以外の値が指定されている
（引数「検索方法」は論理値（TRUE/FALSE or 1/0）を指定しなければならない）
続いて、具体例として実際の画面も見ていきましょう。
上記のように、意図せずとも、数式の引数「範囲」と引数「列番号」の間の”,”[カンマ]がないと、本来「列番号」の指定値であった”3”が「範囲」のセル範囲とくっつき、本来引数「検索方法」の指定値であった”0”が「列番号」扱いとなっていますね。
この場合、引数「列番号」が”0”というVLOOKUP関数のルール外のためにエラーとなっています。
ちなみに、引数「検索方法」の値がなくなっていますが、この引数は省略可能となっているため、数式自体は成立している状態です。
解決策
解決策は、こちらもシンプルに数式を見直して該当部分を然るべき内容へ修正することです。
その際、特に注視すべきポイントは以下の3点です。
- VLOOKUP関数の引数の”,”[カンマ]抜けがないか
- 引数「列番号」に数値以外の値が入っていないか
- 引数「検索方法」に論理値以外の値が入っていないか
まず、1は1つのVLOOKUP関数の数式中に少なくとも”,”[カンマ]は2つ以上必要ですので、”,”[カンマ]に不足があれば追加しましょう。
続いて、2は引数「列番号」が数値以外になっている場合、数値の内容へ修正してください。（セル参照や別の関数の戻り値が数値になっている場合は問題なし）
最後の3も2に似ていますが、引数「検索方法」が論理値以外になっている場合、完全一致参照なら”FALSE”か”0”を、近似一致参照なら”TRUE”か”1”か省略するように修正しましょう。
【エラー4】戻り値が”#REF!”というエラー値
4つ目のエラーは、戻り値が”#REF!”というエラー値です。
読み方は「リファレンス」と呼びます。
原因
以下のような場合に表示されます。
- 引数としてセル参照していたセルを削除や移動させてしまった
- 引数「列番号」が引数「範囲」の列数を超えた数値になっている
ちなみに、後者の意味は、引数「列番号」が”3”で、引数「範囲」が”A:B”で2列分のセル範囲であるということですね。
続いて、具体例として実際の画面も見ていきましょう。
よくあるケースは、上記のように、意図せずに参照セルを削除してしまう場合ですね。
その場合、該当の引数部分が”#REF!”というエラー値に変わるため、どの引数の参照セルを消してしまったか分かりやすいです。
上記の例では、引数「検索値」部分が”#REF!”というエラー値になっていましたね。
そのため、VLOOKUP関数の戻り値自体も”#REF!”のエラー値になっていることが分かります。
解決策
解決策は、こちらもシンプルに数式を見直して該当部分を然るべき内容へ修正することです。
その際、特に注視すべきポイントは以下の2点です。
- “#REF!”になっている引数はどこか
- 引数「列番号」が引数「範囲」で指定しているセル範囲の列数を超過していないか
まず、1の場合は該当する引数を再度セル参照し直せばOKです。
続いて、2は引数「列番号」を引数「範囲」の列数内に合わせるか、引数「範囲」の方を引数「列番号」の列数以上になるように再設定すれば良いですね。
【エラー5】戻り値が”#N/A”というエラー値
5つ目のエラーは、戻り値が”#N/A”というエラー値です。
読み方は「ノー・アサイン」と呼びます。
原因
以下のような場合に表示されます。
- 引数「範囲」の1番左の列中に引数「検索値」の値がない
（引数「検索値」の参照セルが未入力の場合含む） - 引数「検索値」の入力間違い
- 引数「検索値」の値と引数「範囲」の1番左の列の値が同一だが表示形式が異なるため、別データと判断されている
このエラーの特色は、先述の1～4つ目のエラーと違い、数式自体は問題がなくとも、参照するデータの整合性がとれないことでエラーになる場合が多いです。
特に、これからデータを入れていく予定の表などでは、未入力箇所の行にもVLOOKUP関数を仕込んでおくケースが一般的なため、このエラー値が表示されてしまいます。
具体的には以下のとおりです。
上記のように、順次引数「検索値」となる値を適宜追加していく形式の表では、事前にセットしておいたVLOOKUP関数が”#N/A”のエラーになってしまうケースが多いですね。
解決策
解決策は、以下の3点です。
- 引数「検索値」と引数「範囲」の1番左の列の値が誤っていれば、正しい内容へ値を修正
- 1が問題なく、引数「検索値」となる値の入力待ちでエラー表示の場合はIFERROR関数と組み合わせる
- 引数「検索値」の値と引数「範囲」の1番左の列の値が同一だが表示形式が異なる場合は、表示形式を同じものに書式変更する
上記2のIFERROR関数との組み合わせを行うと、あらゆるエラーを非表示にすることができますよ。
IFERROR関数とは？
このIFERROR関数は論理関数の一種で、数式がエラーの場合に任意の値を表示してくれる関数です。
IFERROR(値,エラーの場合の値)
このIFERROR関数は、別の関数と組み合わせて使う想定の関数のため、単独で使うことはありません。
IFERROR関数の引数「値」へ別の関数の数式を入れてあげ、仮にその数式がエラーになった場合に、どんな値を表示させたいかを引数「エラーの場合の値」へ入力すればOKですよ。
IFERROR関数の詳細を知りたい方はこちらの記事をご参照くださいね。→こちら
IFERROR関数と組み合わせる！
実際に、VLOOKUP関数とIFERROR関数を組み合わせたものが以下の内容です。
IFERROR関数の引数「値」に、もともとのVLOOKUP関数の数式を丸ごと入れています。
続いて、引数「エラーの場合の値」は”[ダブルクオーテーション]を2つ指定していますね。
こちらは、今回VLOOKUP関数がエラーになる場合はブランク（空白）表示にしたかったためです。（”[ダブルクオーテーション]を2つでブランク表示になります）
注意点としては、IFERROR関数は良くも悪くもすべてのエラーを対象にするため、先述のエラー2～4のエラー値の場合も同じように「エラーの場合の値」が返ってしまいます。
数式を直さなければならないところも包み隠されてしまうため、事前にエラー内容を切り分けた上で使うようにしましょう。
また、IFERROR関数はExcel2007以降の関数です。
Excel2003以前の場合は、代わりにIF関数とISERROR関数を組み合わせることで同じようにエラーの場合の値を指定することが可能ですよ。
IF関数+ISERROR関数の詳細を知りたい方はこちらの記事をご参照くださいね。→こちら
なお、解決策3の方法は複数あり、詳細は別記事で解説していますので、こちらの記事も併せてご参照ください。
【中級者向け】検索する表の中にVLOOKUP関数の引数「検索値」があるのに、なぜかエラーになる3＋1ケースの原因と対策 | Excelを制する者は人生を制す ～No Excel No Life～
【エラー6】戻り値が”0”の値
6つ目のエラーは、VLOOKUP関数の戻り値が”0”になるという場合です。
こちらは、数式的には問題なく、VLOOKUP関数の戻り値も特定のエラー値ではないので、厳密にはエラーではないですが、意図しない結果であることが多いため、エラーに含めています。
原因
原因は、VLOOKUP関数の戻り値の検索先のセルがブランクの場合に発生します。
つまり、以下のような状態ですね。
上記のように、「台帳」シートのVLOOKUP関数で「商品」シートの単価を参照したかったのですが、「商品」シートの単価がブランクだったために、VLOOKUP関数の戻り値が”0”になっていることが分かります。
もちろん、きちんと「商品」シートの単価のセルに”0”と入力されている場合は、VLOOKUP関数の戻り値としては正しいので、そのような場合は今回のようにエラー扱いしなくてOKですよ。
解決策
解決策は、以下の2点です。
- VLOOKUP関数の戻り値となるセルへ値を入力できる場合はあらかじめ入力しておく
- 別シートの計算や入力待ちなどで1が難しく、かつ”0”表示を回避したい場合はIF関数と組み合わせる
まず、1はVLOOKUP関数で検索する表を完成させればOKです。
問題は2ですね。たまに、複数シートが相互に関連する場合に起こる可能性がありますが、もし”0”表示を避けたい場合はIF関数で組み合わせましょう。
IF関数とは？
このIF関数は論理関数の一種で、ある条件に対して、該当する場合と該当しない場合のそれぞれに任意の値を表示させるように設定できる関数です。
IF(論理式,[真の場合],[偽の場合])
IF関数の詳細を知りたい方はこちらの記事をご参照くださいね。→こちら
IF関数と組み合わせる！
たとえば、以下のようにすればOKです。
上記では、IF関数の引数「論理式」で「VLOOKUP(台帳!$B2,商品!$A:$B,2,0)=0」のようにVLOOKUP関数の戻り値が”0”の場合を条件に設定します。
そして、その条件に該当する場合は、今回はブランク表示にしたいので、引数「真の場合」へ”[ダブルクオーテーション]を2つ指定します。
最後に、引数「偽の場合」には、VLOOKUP関数の数式を丸ごと入れればOKですね。
【エラー7】戻り値がユーザーの想定外の値（”0”以外）
最後の7つ目のエラーは、エラー値やメッセージが表示されない、ユーザーの想定外の戻り値が出てしまっている場合を指します。
（エラー6で解説した”0”の値を除きます）
原因
こちらは、エクセル的には数式のルール上は間違いないですが、もともと想定していた引数の値を何らかの理由で間違えていることが原因です。
これは、特に引数「列番号」を別の数値にしてしまっているケースが大半ですね。
たとえば、以下のような状態です。
上記のように、特に複数列に基本となるVLOOKUP関数をコピペで再利用する場合に起きやすいです。
解決策
このエラーについては、ピンポイントでの解決策を提示することは難しいです。
あくまでもユーザーが想定していた戻り値になっているか、VLOOKUP関数をセットする際は必ず数式を見返すようにしましょう。
特に、引数「列番号」が誤った数値になっている場合が多いので、VLOOKUP関数の検索する表へ列を挿入や削除することも本エラーの原因になっている可能性がありますので注意してくださいね。
ちなみに、引数「列番号」の数値を自動で取得できるテクニックを活用すると、こういったエラーになる可能性を未然に防ぐことができますよ。
詳細はこちらの記事をご参照くださいね。
【中級者向け】VLOOKUP関数の引数「列番号」を自動で変更する3つのテクニック | Excelを制する者は人生を制す ～No Excel No Life～
さいごに
今回はVLOOKUP関数のエラー全7種類について触れてきたので、かなりのボリュームになりました。
実際に、実務でエラーに直面した際に、該当する箇所を読みながらエラー解消にチャレンジしてみてください。
こうしたエラーの解消（リカバリ作業）の経験を増やしていくことで、経験則的に何が原因でエラーになっているかのアタリをつけることができるようになっていきます。
基本的な使い方に加え、こうしたエラー解消までできるようになったら、VLOOKUP関数マスターと言っても過言ではないですよ(笑)
なお、エラー解消以外のVLOOKUP関数の各引数に対する応用テクニックを私の書籍で紹介しているので、こちらもご参考にしていただければと思います。
▼VLOOKUP関数を基本から応用まで体系的に学びたい方向け
▼VLOOKUP関数の応用テクニックに加えて、その他IF関数なども学びたい方向け
ご参考になれば幸いですm(_ _)m
森田 本記事は、実は私の書籍である「すごい！関数」の中で盛り込むはずだったコンテンツのひとつです。
ページ数の都合で泣く泣くカットした部分でしたが、VLOOKUP関数を使う方全員に理解してほしい重要ポイントなので記事にまとめて直してみました。
エラーが起きると、本当に解消するまでに時間もかかりますし、精神的にもきついので、実際にエラーが出て困っている方の助けになれば嬉しいですね。
