Office Hack

ExcelのVLOOKUP関数で#N/Aエラーが起きたときの対処法

  • by yoshihiro
  • Release
  • Update

VLOOKUP関数を使用していると「#N/A(ノー・アサイン)」エラーが出たり、意図しない結果になったりすることがあります。「#N/A」エラーとはVLOOKUP関数やMATCH関数などの検索関数で検索する値が見つからないときに発生します。

「#N/A」エラーになるのは関数の書式が違ったり、引数の使い方が間違っていたりすることがほとんどです。その原因を見つけ、対処すれば正しい結果を求めることができるのでご安心ください。

この記事ではVLOOKUP関数で「#N/A」エラーになる主な例を挙げながらその対処方法を説明していきます。エラーになった際に1つずつ確認してみましょう。

#N/Aエラーの例とその解決方法

下記で#N/Aエラーになる主な事例とその解決方法を説明していきます。

検索値が検索範囲の左端の列にない

VLOOKUP関数では検索範囲の左端の列の値のみを検索します。検索値が検索範囲の最初の列にない場合は、「#N/A」エラーが表示されます。

検索範囲の誤った例

例えば上記の表で、C10セルに商品コードを入れるとD10セルの商品名に価格テーブルから紐づいた商品名を表示するとします。

もし商品名を出したいから検索範囲も商品名からで大丈夫と思い、D10セルに『=VLOOKUP(C10,$C$4:$D$6,2,FALSE)』を入力しました。商品コードを含まないC4からD6セルの範囲を指定しています。この指定は正しく検索できるのでしょうか?

検索値検索範囲の左端の列にない

結果としては、D10セルに「#N/A」エラーが表示されてしまいました。

VLOOKUP関数の第2引数である「検索範囲」は第1引数の「検索値」を左端の列に含むようにしなければなりません。上記の例では、正しくは『=VLOOKUP(C10,$B$4:$D$6,2,FALSE)』のように検索値である「商品コード」を左端の列にしたB4からD6セルの範囲を指定する必要があります。

検索値が検索範囲の最初の列にあるかどうかチェックしましょう。

完全一致を指定していて見つからない

VLOOKUP関数では第4引数の「検索の型」でFALSEを指定すると完全一致で検索します。完全一致するものがない場合、「#N/A」エラーが表示されます。

完全一致が見つからない例

例えば上記の表で、C10セルに商品コードを入れるとD10セルの商品名に価格テーブルから紐づいた商品名を表示するとします。

D10セルに『=VLOOKUP(C10,$B$4:$D$6,2,FALSE)』を入力しました。この指定は正しく検索できるのでしょうか?検索範囲の価格テーブルにおいて「商品コード」のセル左上にエラーマークが出ています。

#N/Aエラーの発生

結果としては、D10セルに「#N/A」エラーが表示されてしまいました。

VLOOKUP関数の第4引数である「検索の型」にFALSEを指定した場合、完全一致である必要があります。上記の例では、見た目的には価格テーブルに「1001」があるので検索できると思いましたが実はセルの表示設定が異なります。

書式が文字列になっていた例

B4セルのエラーマークをクリックすると「数値が文字列として保存されています」と表示されました。表示設定で「文字列」として設定されたために完全一致で検索することができず「#N/A」エラーとなってしまった例になります。

この場合では、価格テーブルの「商品コード」を文字列ではなく、数値に変換する必要があります。もしくは検索値であるC10セルを文字列に変更することで完全一致で検索できるようにできます。

FALSEを指定する場合、検索値と検索範囲の最初の列の値が表示設定などを含めて同じかどうかチェックしましょう。

検索値が検索範囲の最小値よりも小さい

VLOOKUP関数では第4引数の「検索の型」でTRUEを指定すると近似値で検索し、一致するものがなければ検索値の次に小さい値を返します。検索値が検索範囲の最小値より小さいと「#N/A」エラーが表示されます。

検索値が最小値より小さい場合

例えば上記の表で、C10セルに商品コードを入れるとD10セルの商品名に価格テーブルから紐づいた商品名を表示するとします。

商品コード1000に近いものを検索したいと思い、C10セルに『1000』と入力してD10セルに『=VLOOKUP(C10,$C$4:$D$6,2,TRUE)』を入力しました。この指定は正しく検索できるのでしょうか?

検索値が最小値より小さい場合の結果

結果としては、D10セルに「#N/A」エラーが表示されてしまいました。

VLOOKUP関数の第4引数である「検索の型」でTRUEを指定して近似値を検索したい場合、検索値は検索範囲の最小値より大きい必要があります。
上記の例では、検索値であるC10セルに1001以上の数値を入力する必要がありました。

TRUEを指定する場合、検索値が検索範囲の最小値より大きいかどうかチェックしましょう。

検索値を参照する列が昇順で並び替えられていない

VLOOKUP関数では第4引数の「検索の型」でTRUEを指定する場合、検索値を参照する列は昇順で並び替えておく必要があります。検索値を参照する列が昇順になっていないと正しい結果にならないことがあります。

検索値を参照する列が昇順で並び替えられていない例

例えば上記の表で、C10セルに商品コードを入れるとD10セルの商品名に価格テーブルから紐づいた商品名を表示するとします。

バナナを検索したいと思い、C10セルに『1002』と入力してD10セルに『=VLOOKUP(C10,$B$4:$D$6,2,TRUE)』を入力しました。この指定は正しく検索できるのでしょうか?

検索値を参照する列が昇順で並び替えられていない例の結果

結果としては、D10セルに「りんご」が表示されてしまいました。「#N/A」エラーではないですが意図しない結果になりました。

VLOOKUP関数の第4引数である「検索の型」でTRUEを指定して近似値を検索したい場合、検索値を参照する列(検索範囲の左端の列)を昇順に並び替えておく必要があります。上記の例では、価格テーブルを商品コードで昇順に並び替えておく必要がありました。

TRUEを指定する場合、検索値を参照する列が昇順に並び替えられているかチェックしましょう。

小数で計算した値を検索値に使用している

小数を計算した結果を検索値にしている場合、演算誤差が発生して「#N/A」エラーが表示されたり、正しい結果にならないことがあります。検索値には小数を計算した結果を使用しないようにしましょう。

小数を使用した場合

例えば上記の表で、C10セルに商品コードを入れるとD10セルの商品名に価格テーブルから紐づいた商品名を表示するとします。

バナナを検索したいと思い、C10セルに「2」を表示させたいと思いますが、今回はSUM関数を使用して『=SUM(0.2+0.2+0.2+0.2+0.2+0.2+0.2+0.2+0.2+0.2)』のように入力して小数の合計値で「2」にしています。次にD10セルに『=VLOOKUP(C10,$B$4:$D$6,2,FALSE)』を入力します。この指定は正しく検索できるのでしょうか?

小数を使用した場合の結果

D10セルに「#N/A」エラーが表示されてしまいました。

C10セルは表示上では「2」になっていますが、浮動小数点誤差の影響でExcel内部では「2」未満となっています。それゆえ完全一致していないと判定されてしまいました。浮動小数点数とは、小数点の後に続く数値としてExcel内で保存している値になります。Excel内部の計算が2進数で行われるために発生する事例でした。

上記の例では、ROUND関数を使用して丸めるか、小数を計算した結果を検索値として使用しないようにすると検索することができます。

小数で計算した結果を検索値に使用していないかどうかチェックしましょう。

検索範囲を「絶対参照」にしていないためズレてしまう

VLOOKUP関数の数式をコピーして他のセルでも使用した場合、元の表データの範囲がズレてしまうときがあります。これは検索範囲が絶対参照になっていないことが原因です。検索範囲は絶対参照にしましょう。

※絶対参照とは、セルに入力された計算式をコピーする際、それぞれのセルの参照先を統一してしまうことをいいます。

絶対参照にしていない例

例えば上記の表で、C10セルに商品コードを入れるとD10セルの商品名に価格テーブルから紐づいた商品名を表示するとします。

D10セルに『=VLOOKUP(C10,B4:D6,2,FALSE)』と入力しました。C10セルに商品コード『1001』と入力するとD10セルに正しく「りんご」と表示されます。今度はC10セルの式をC11セルにコピーします。

検索範囲がずれてしまった例

検索範囲を確認すると、B5からD7セルの範囲が変わってしまっています。これはセル範囲を絶対参照にしていなかったために発生してしまった例です。元となるデータ範囲は絶対参照にしましょう。数式上で検索範囲「B4:D6」を選択し、F4を押すと「$B$4:$D$6」と絶対参照になります。

検索範囲が絶対参照になっているかどうかチェックしましょう。

VLOOKUP関数専門の本をご紹介

VLOOKUP関数をより深く勉強したい方はこちらの本でさらに理解を深めてみてはいかがでしょうか?実際の操作画面を示しながら、徹底的にわかりやすく解説したフルカラー入門書です。

VLOOKUP関数のツボとコツがゼッタイにわかる本

立山秀利(著)
206ページ

その他、様々なVLOOKUP関数の使い方

XLOOKUP関数も合わせてチェック!

VLOOKUP関数の後継として新しくXLOOKUP関数が発表されました。XLOOKUP関数の基本的な使い方を説明しています。

他の関数も合わせてチェック!

Office Hackでは、ここでご紹介できなかった関数の一覧ページもご用意しております。ぜひ、参考にしてください。

よろしければ参考にならなかった点をお聞かせください