VLOOKUP関数を間違いなく入力しているはずなのに、「#N/A（ノー・アサイン）」のエラーが出てしまい困惑している人もいるのではないでしょうか。
本記事では、考えられるVLOOKUPでの「#N/Aのエラー」の原因と対処法を紹介。また、IFERROR関数を使った「#N/Aエラー」を表示させない方法も紹介するので、ぜひ参考にしてくださいね。
- VLOOKUP関数で#N/Aエラーが出る7つの原因と対処法
- VLOOKUP関数で#N/Aエラーを表示させない方法
VLOOKUP関数で#N/Aエラーが出る7つの原因と対処法
VLOOKUP関数を正しく使えているはずなのに「#N/Aエラー」が出てしまって焦りや、解決できないことへ戸惑う人も多いのではないでしょうか。
「#N/Aエラー」と表示されるのには必ず理由があります。以下では、考えられる7つの原因とその対処法についてわかりやすく解説していきます。
- 原因1．全角と半角が一致していない
- 原因2．検索範囲の左端に検索値が見つからない
- 原因3．検索値が昇順になっていない
- 原因4．完全一致を指定しているが、一致しているものが見つからない
- 原因5．検索値が絶対参照になっていない
- 原因6．検索値が小数で計算した値になっている
- 原因7．検索値＜検索範囲の最小値となっている
ひとつずつ確認してエラーの解決を目指しましょう。
1．全角と半角が一致していない
VLOOKUP関数で#N/Aエラーが出る1つ目の原因は、全角と半角が一致していないことです。
上記の画像のように「VLOOKUP の評価で値「１」が見つかりませんでした。」とメッセージが出ている場合は、まずは「数字」を確認してみましょう。
上記の画像のように、左上の管理番号は「半角」で書かれているのに対し、「B11」の管理番号は「全角」で書かれています。
半角と全角の不一致が原因の場合は、以下の2つの解決策があります。
1つ目は、左上にある管理番号自体を変える方法です。赤で囲まれた2つの管理番号の書式さえ一致していれば、エラーが出ないことがわかります。
2つ目は、右下にある管理番号を「半角」に変え、左上にある管理番号の書式に揃える方法です。
このようなエラーを出さないためにも、EXCELやスプレッドシートは基本的に半角を使うように統一することをおすすめします。
2．検索範囲の左端に検索値が見つからない
VLOOKUP関数で#N/Aエラーが出る2つ目の原因は、検索範囲の左端に検索値が見つからないことです。
検索範囲とは、「=VLOOKUP(検索キー, 範囲, 指数)」の、「範囲」のことをいいます。
上記の例を確認していきましょう。「管理番号」で商品名を導き出したいと思っている場合、管理番号も検索範囲に入れなければいけません。
だからといって、検索範囲を上記の画像のように広げてもエラーが出てしまいます。
これは、検索範囲の左端に検索値、今回の場合「管理番号」がないためです。
検索範囲を決めるときは、必ず検索キーが書かれている場所を左端にすることを覚えておきましょう。
3．検索値が昇順になっていない
VLOOKUP関数でエラーが出る3つ目の原因は、検索値が昇順になっていないことです。
#N/Aエラーにはなりませんが、検索方法が「TRUE」である場合、VLOOKUP関数が明らかにおかしい結果になることもあります。今回の場合、管理番号が「4」であるのに「D」と出力されません。
VLOOKUP関数のFALSEとTRUEの使い分け
VLOOKUP関数は、4つ目の引数に「TRUE」もしくは「FALSE」を入力します。FALSEを「0」、TRUEを「1」または空欄と入力することもあります。
FALSEとTRUEの違いは、FALSEが完全一致であるのに対してTRUEが近似一致であることです。
例えば、FALSEの場合、検索値にないものを指定すると#N/Aエラーが出てしまいます。
TRUEを入力した場合、検索値にないものを入力しても#N/Aエラーにはなりません。「前行の値」よりも大きく、「次行の値」よりも小さい数字を入力した場合、前行の値の結果が出力されます。
今回の場合、検索値「50」は、「40以上60未満」なため、前行の値である「40」の結果である「1500」が入力されています。
このように、前行と次行の関係が使用されるため、TRUEでは昇順でないとおかしな結果になる可能性が高いです。
データを昇順に変える2つの方法
「大量にあるデータを昇順に戻すのは億劫だ」と思っている人もいるのではないでしょうか。ボタンひとつで簡単に昇順に戻せるので安心してくださいね。
昇順に並べたいものがある行をクリックします。今回の場合「A行」をクリックすると、上記の画像のような画面が表れます。
「昇順でシートを並び替え」をクリックすると、上記の画像のように並び替えられてはいますが、全体がばらばらになってしまうことも。昇順で並び替えたいもの以外が同じ行に入っている場合はこの方法はおすすめしません。データのみを並べ替えたい場合は、以下の方法を試してみましょう。
昇順に並べ替えたい場所のみを選択し、左クリックします。「セルでの他の操作項目を表示」>「範囲を並び替え」をクリック。
「データにヘッダー行が含まれている」や「並べ替え条件」を調節しながら、「並び替え」をクリックしましょう。
この方法を使えば、上記の画像のように並べたいもののみを昇順にできました。
また、VLOOKUP関数も正常に機能するようになり、エラー解除できました。
昇順になっていなくても、「FALSE」なら問題なく使用できることも併せて覚えておくといいですね。
4．完全一致を指定しているが、一致しているものが見つからない
VLOOKUP関数で#N/Aエラーが出る4つ目の原因は、完全一致を指定しているが、一致しているものが見つからないことです。
第4引数を「FALSE」もしくは「0」にしている場合、検索値の完全一致が必要です。
誤って、検索値にない数値を入力していないか確認してみましょう。
また、書式が一致していない場合も、#N/Aエラーがでます。上記の例の場合、検索範囲の数字が「文字列」であるのに対して、検索値は「数値」になっています。
書式の変換方法
書式をどちらか一方にあわせて、完全に一致するように変更しましょう。以下では、「文字列」と「数値」の入れ替え方をご紹介します。
「文字列」から「数値」に書式を変更したい場所を選択し、「表示形式」>「数字」>「数値」をクリックします。
また、数値から文字列に変更したい場合は、「表示形式」>「数字」>「書式なしテキスト」を選択。
書式を揃えると#N/Aエラーが解除され、VLOOKUP関数も正常に作動します。
5．検索値が絶対参照になっていない
VLOOKUP関数で#N/Aエラーが出る5つ目の原因は、検索値が絶対参照になっていないことです。絶対参照とは、「$」をつけて参照するセルの範囲を固定することをいいます。
上記のように#N/Aエラーが出たセルを確認してみると、「検索範囲」がずれていることがわかります。
ドラッグしてコピーすると、このように検索範囲がずれるため「絶対参照」にすることを忘れないようにしましょう。
絶対参照のやり方
「$A1」「A$1」の違いがわからない人もいるのではないでしょうか。
「$A1」の場合Aが固定され、「A$1」の場合1が固定されています。このように、「$」をつけると、$をつけた後の数字が変わらなくなることを覚えておきましょう。
今回の場合、「A5:C10」と本来の範囲である「A1:C6」からずれているため、「A$1:C$6」と固定します。どうしてもわからない場合は「$A$1:$C$6」と範囲全体を固定しても大丈夫です。
絶対参照をすることで、ドラッグしてコピーした後も検索範囲が変わらず、正常にVLOOKUP関数が作動します。
6．検索値が小数で計算した値になっている
VLOOKUP関数で#N/Aエラーが出る6つ目の原因は、検索値が小数で計算した値になっていることです。特にEXCELの場合、検索値を少数で計算するときは注意しましょう。
このように正しくVLOOKUP関数を入力できていても、#N/Aエラーが出てしまいます。
上記の画像のように「=SUM(0.2+0.2+0.2+0.2+0.2+0.2+0.2+0.2+0.2+0.2)」を計算した値は2と表示されますが、「浮動小数点演算」のため誤差が生じてしまいます。
浮動小数点演算とは、コンピューターが十新法で計算したものを二進法で計算し、再び十新法に戻すために起きてしまう誤差のことです。
例えば「0.2」を 2 進数で表すと、0.00110011（繰り返し）です。コンピューターは、永遠に続く数（無限小数）をそのスペックに応じて保存します。例えば二進数の「0.00110011001100110011001100110011」を十進数に変更すると「0.19999999995343387」になります。もとの0.2とずれが生じていることが「浮動小数点誤差」です。
EXCELの場合、小数点で計算すると浮動小数点誤差が起こり、2と表示されているが二進法での表し方が違うため#N/Aエラーが出ます。
浮動小数点誤差の解決方法
浮動小数点誤差を解決するためには、そもそも小数点計算を行わないこと以外にも「ROUND関数」を使う方法があります。
「ROUND関数」とは、「=ROUND(数値, 桁数)」と使用します。四捨五入したいセルや数字を入力した後、 四捨五入した結果小数点◯位まで表示したいかを入力するだけです。
例えば「=ROUND(2.563),2」の場合、小数点2位まで残るため「2.56」と表示されます。
このように、「ROUND」を使うと、#N/Aエラーが表示されずVLOOKUP関数が正常に機能するようになります。
Googleスプレッドシートの場合「=sum(0.2+0.2+0.2+0.2+0.2+0.2+0.2+0.2+0.2+0.2)」であっても、正常に機能します。
7．検索値＜検索範囲の最小値となっている
VLOOKUP関数で#N/Aエラーが出る7つ目の原因は、検索値＜検索範囲の最小値となっていることです。
TRUE（近似一致）の場合であっても、検索値が検索範囲よりも小さいと#N/Aエラーが出てしまいます。
「前行の値」よりも大きく、「次行の値」よりも小さい数字を入力した場合に、「前行の値」が入力されるのがTRUE。前行の値よりも大きいものがなければエラーになってしまうので、検索値を変更したり検索範囲を広げたり変更してみましょう。
VLOOKUP関数で#N/Aエラーを表示させない方法
「VLOOKUP関数を完成させておいて、入力した後すぐ出力するようにしておこう」と関数を組んでいると、検索値を入力していないため「#N/Aエラー」が表示されます。
わずらわしい「#N/Aエラー」を消すためには、IFERROR関数を使うことを覚えておきましょう。
IFERROR関数は 「=IFERROR(値, [エラー値])」と入力します。
今回の場合、「値」の場所にVLOOKUP関数を入力し、「エラー値」に表示させたいものを入力します。何も表示させたくない場合は「””」を、文字を入力したい場合は「”該当なし”」などと書きます。「””」が必要になることを覚えておきましょう。
今回の場合、「=IFERROR(VLOOKUP(B11,A1:C6,2,TRUE),"")」と入力しました。
このように「#N/Aエラー」と表示されず、スッキリとした表になりました。
VLOOKUP関数の#N/Aエラーは考えられる原因をひとつずつ対処しよう
- VLOOKUP関数を使用するときは、「全角」「半角」「書式」に注意する
- 検索範囲が左端になっているか確認する
- 絶対参照や浮動小数点誤差に気をつける
本記事では、VLOOKUP関数の#N/Aエラーが出たときの原因について紹介しました。エラーが出ている場合は、何かしらの設定を間違えていることを疑いましょう。
特に半角や全角、書式など常に注意して、数値を入力するようにすることをおすすめします。また、検索範囲や絶対参照、小数点なども検討することを忘れないようにしてください。
エラーを解消したいときには、本記事でご紹介した考えられる原因をひとつずつ試してみてはいかがでしょうか。
【関連記事】
VLOOKUP関数の使い方とは？準備から基本の式の作り方を解説
VLOOKUP関数は他の基本的な関数と比べると複雑でわかりにくい関数です。「なんとなく難しいイメージがある」「使いこなせなさそうで避けている」という方も多いのではないでしょうか。 本記事で...
VLOOKUP関数とは？役立つシーンと、基本の型を紹介
Excelやスプレッドシートを活用するときに覚えておきたい関数のひとつである「VLOOKUP関数」。なんとなく難しそうなイメージがある人も多いのではないでしょうか。 本記事では、VLOOK...
VLOOKUPで合計値は出せない！SUMIF関数を組み合わせて算出する方法
「VLOOKUPで検索した数値を合計したいけれど、できない」と困っている人もいるのではないでしょうか。 残念ながらVLOOKUP関数のみでは、合計値は出せません。そのため、「SUMIF関数...
はてブU-NOTEをフォローしておすすめ記事を購読しよう