ExcelやGoogle Sheetsを利用する際に検索/行列関数の1つである「VLOOKUP」を使う機会は多いかもしれません。しかし、VLOOKUP関数はエラーが出やすく、エラーが出た場合にどこが間違っているのかがわかりにくいこともあります。
そこで、この記事ではVLOOKUP関数を利用する場合によくあるエラー「#N/A」、「#NAME?」、「#VALUE!」の原因と対処法を紹介します。VLOOKUP関数のエラーに困っている人は、ぜひ参考にしてください。
この記事の内容
VLOOKUP関数の構成
最初にVLOOKUP関数の構成を簡単に紹介します。VLOOKUP関数は以下のような構成担っているので、しっかり覚えておきましょう。
=VLOOKUP(検索値, 範囲, 列番号, 検索の型)
設定する4つの引数にミスがあるとエラーが発生するので、引数はしっかりチェックするようにしてください。なお4番目の検索の型の引数は省略することができます。ただし、省略すると「TRUE」の扱いになるのでデータの並び替えをしたくない場合は「FALSE」と入力しておきましょう。
エラー1:「#N/A」が出る時の原因と対処法
それでは、「#N/A」というエラーが出るときの原因と対処法をみていきます。
主な原因
「#N/A」エラーの主な原因は次の通りです。
- 検索する値が検索エリア内に存在しない
- 表記ゆれや誤字脱字がある
- 検索の型の引数を設定していないかつデータが昇順に並んでいない
もし、このエラーが発生した場合は以下の手順を1つずつチェックしてみてください。
対処1: 検索エリアの指定が間違っていないかをチェックする
このように#N/Aエラーがでたら、まずは検索エリアをチェックして検索する値が存在してるかを確認します。存在しているのにエラーが出ている場合は検索エリアの指定がずれている可能性があるので関数をチェックしましょう。
今回は検索エリアがずれていることが判明しました。これは最初に入力した以下の関数をオートフィルで入力したために発生したものです。
=VLOOKUP(D4, A2:B23, 2, FALSE)
このままオートフィルすると検索エリアがずれるので、最初の関数を以下のものに修正します。
=VLOOKUP(D4, $A$2:$B$23, 2, FALSE)
このように検索エリアを絶対参照にすることによって、オートフィルをかけてもエリアがずれないようにできるのできちんと設定しましょう。オートフィルは頻繁に使うのでこのエラーが発生しやすいといえるでしょう。
他にもさまざまな理由によって検索エリアがずれることがあるので、エラーが発生したら当該セルに入力されている関数をチェックし、検索エリアが正確かどうかを確認してください。
対処2: 表記ゆれや誤字脱字を修正する
VLOOKUP関数は基本的に検索する値に入力したものと一致しているものを検索して結果を返します。そのため、誤字脱字がある場合は#N/Aエラーが発生するので注意してください。
上記の例ではSearch欄が15になっているものは後ろに全角スペースがあるため、検索エリア内の数値と一致せずにエラーが発生しています。
2つめのエラーは検索値が「21*」となっていて余計な記号が入っているため、こちらもエラーが発生します。
3行目は検索値が「1A」で、検索エリア内にあるのは「1a」ですが、大文字と小文字は区別されないため正常にデータが返ってきます。
関数に問題が無いのにエラーが発生する場合は検索する値に誤字脱字が無いかどうかを確認してください。
対処3: 検索の型をきちんと設定する
この例では検索の型を指定する引数を省略しています。これによって「3」で検索した場合にエラーが発生していることがわかるでしょう。
検索の型を省略した場合は「TRUE」として扱われ、「TRUE」の場合は検索エリアが昇順に並んでいなけばならないというルールがあります。並んでいない場合はこのようにエラーが発生したり、「21」の欄でわかる通り全然違う値が返ってきたりするので注意してください。
VLOOKUP関数を利用する時は検索の型の引数をきちんと入力するようにしましょう。また、特別な理由がない限りはソートする必要がない「FALSE」を利用するのがオススメです。
今回の場合は
=VLOOKUP(D4, $A$2:$B$23, 2)
という関数を
=VLOOKUP(D4, $A$2:$B$23, 2, FALSE)
に修正するとエラーが出なくなります。
エラー2:「#NAME?」が出る時の原因と対処法
VLOOKUPを利用するときに「#NAME?」というエラーが発生することがあります。こちらもちょっとした入力ミスで発生するエラーなので、もし発生したら再度入力した関数をチェックしましょう。
主な原因
「#NAME?」エラーの主な原因は「存在しない関数を入力している」というものです。基本的に関数に誤字脱字があると発生するエラーです。
今回の例ではE4セルにエラーが発生していることがわかるでしょう。
対処: 関数の誤字脱字を修正する
「#NAME?」エラーは関数が間違っている時に発生するエラーなので、VLOOKUP関数で発生した場合には関数の「VLOOKUP」の部分に誤字脱字がないかを確認してください。
今回の場合は関数が「VLOOKUPP」になっていたために「#NAME?」エラーが発生しています。これを「VLOOKUP」に修正すると正常に結果が返ってくるのできちんと修正しましょう。
修正すると正常に検索結果が返ってくることがわかるでしょう。「#NAME?」エラーの対処法は基本的にこれだけです。
今回の例の場合、修正内容は次の通りです。
修正前: =VLOOKUPP(D4, $A$2:$B$23, 2, FALSE) 修正後: =VLOOKUP(D4, $A$2:$B$23, 2, FALSE)
エラー3:「#VALUE!」が出る時の原因と対処法
よくあるエラーには他にも「#VALUE!」があります。こちらも前述の2つと同様にちょっとしたミスで発生するエラーなので、もし発生した場合は入力した関数を注意深くチェックしましょう。
主な原因
「#VALUE!」エラーの主な原因は「引数にミスがある」というものです。具体的には次のようなものがあります。
- 列番号の数字がおかしい
- 検索の型の指定がおかしい
- カンマが抜けている
いずれもちょっとしたミスで発生するものなので、確認して修正してください。
対処1: カンマ抜けがないかを確認する
これはカンマ抜けでエラーが発生している場合の数式の例です。
この例の数式は
=VLOOKUP(D4, $A$2:$B$232, FALSE)
となっていますが、本来は
=VLOOKUP(D4, $A$2:$B$23,2, FALSE)
でなければなりません。
検索エリアと列番号の間のカンマが抜けているために関数がおかしくなり、「#VALUE!」エラーが発生しています。この場合は適切にカンマを入力することによって修正できるでしょう。
対処2: 列番号を修正する
VLOOKUP関数では、指定される列番号は正の整数でなければならないというルールがあります。しかし、入力ミスで0や負の整数が入っているとこのようにエラーが発生します。
今回の関数は
=VLOOKUP(D5, $A$2:$B$23, -1, FALSE)
となっていますが、これを
=VLOOKUP(D5, $A$2:$B$23, 2, FALSE)
に修正すると正常にデータが返ってくるでしょう。なお、「3」などの検索エリアに存在しない列番号を指定した場合は「#REF!」という別のエラーが返ってきます。
対処3: 検索の型を修正する
次にチェックしたいのは「検索の型」の引数です。ここにはTRUEまたはFALSEが入り、これ以外の数値が入っているとエラーが返ってきます。
今回の例では
=VLOOKUP(D5, $A$2:$B$23, 2, FALLSE)
となっていて検索の型を指定する引数に誤字があります。
そのため、この誤字を修正して
=VLOOKUP(D5, $A$2:$B$23, 2, FALSE)
にしましょう。これで正常にデータが返ってくるようになるでしょう。このように、ちょっとしたミスで「#VALUE!」が発生するので注意深く関数をチェックすることが大切です。
エラーを非表示にする方法
場合によってはこれらのエラーを表示させたくないということもあるかもしれません。特に、存在しないデータを検索した場合に「#N/A」を返すのではなく空欄にしたいと思う人は多いでしょう。
エラーを非表示にする方法は以下の通りなので、ぜひ参考にしてください。
IFERROR関数と組み合わせてエラーを非表示にする
エラーを非表示にする場合はIFERROR関数を利用します。IFERROR関数はエラーが発生した場合に特定の値を返すための関数で、その値に空白を指定することができます。
IFERROR関数の構文は=IFERROR(値, エラー発生時に表示するもの)というシンプルなものです。この「値」の部分にVLOOKUP関数を入れるだけなのですぐに覚えられるでしょう。
実際に使う方法は次の通りです。
この例では表に22がないため、E4のセルには「#N/A」エラーが表示されています。ここに入力されている関数は次の通りです。
=VLOOKUP(D4, $A$2:$B$23, 2, FALSE)
エラーを表示させないようにするために、この関数と前述のIFERROR関数を組み合わせると次の通りになります。
=IFERROR(VLOOKUP(D4, $A$2:$B$23, 2, FALSE), "")
実際にこの数式を入力すると次のように表示されるでしょう。
先程は「#N/A」エラーが表示されていたE4セルがブランクのセルになりました。別のセルにもこれを適用する場合はオートフィルを利用するか、ARRAYFORMULA関数と組み合わせて利用してください。
このようにVLOOKUP関数利用時にエラーが発生した場合は関数を1つずつ詳しくチェックして修正すると解決できるので、ぜひ参考にしてください。