vlookupがN/Aエラー！文字列はあるのに反映されない時の対応方法

エクセルを用いて集計作業をする際にとても役立つ関数の1つであるvlookup。

vlookup関数を使うことで、条件に一致する情報を一覧から抽出することができるので、エクセルの中で一番便利だと思っている関数です。

このvlookup、とても便利なのですが、使ってみると次のような現象がよく発生します。

vlookup関数の使い方は何度確認しても間違っていないし、参照元としているデータには条件に一致するデータは確かにあるのに、なんで結果が#N/Aエラーなんだろう・・意図したように文字列が反映されない・・・という具合です。

そこでこの記事では、データはあるのにvlookupの結果が#N/Aエラーとなってしまい、うまく反映されない場合の原因と対応方法についてまとめました。

データはあるのに反映されないとお困りの場合、ここで紹介するポイントを押さえることでvLookupを使いこなすことができるようになります。本記事の内容をマスターし、vlookupの#N/Aエラー問題をすぐに解決できるようになりましょう！

データはあるのにvlookupの結果が#N/Aエラーとなってしまう事象について

データはあるのにvlookupの結果が#N/Aエラーとなるケースはいくつかパターンがありますが、特に数値データ（商品コードなど）を条件にしてvlookupを行っている場合に発生することが多いです。

例えば次のようなものです。

この例では、D2セルに「111111」を設定しているので、E2のvlookupの結果としてはB8セルの値「商品111111」を反映するような想定をしています。ところが、B8セルには「#N/A」となってしまい、うまく文字列を反映できていません。

A列に「111111」に該当するデータはあるため検索できそうなのですが、なぜか反映されない。

この例を見てすぐに原因が分からない場合、vlookupを使用時に注意が必要な視点が抜けています。

それは、文字列と数字は違うものとして扱われるという点です。

参考：vlookupの別シート検索がうまくいかない場合の対処方法

vlookupの結果が#N/Aエラーとなり想定通りに反映されない原因

データはあるのにvlookupの結果が#N/Aエラーとなる一番の要因は、文字列と数字の違いです。

どういうことかを次に解説します。

vlookupの結果が#N/Aエラーとなる原因

データはあるのに#N/Aエラーとなる一番の要因は、次だと考えられます。

よって、条件セルと範囲セルの書式を一致させてあげれば、VLOOKUPで該当するデータを抽出させることができるようになります。

書式の不一致によりデータはあるのに反映されないとは？

このケースでvlookupの結果が＃N/Aエラーとなるのは、次のような使い方をしようとしている場合が多いです。

先述のvlookupの#N/Aエラー例は、まさにこの状態に当てはまっています。

データとして一意なコードを元に名称を取得する。vlookup関数を活用するケースとしてぴったりなのですが、なぜこのようなケースで発生する場合が多いのか。

それは、コードが書いてあるセル（列）の書式が「文字列」だけれど、vlookupで条件に指定した値は「数値」になっているためです。

例えば先述の例では、D2セルに検索値として「111111」を設定していますが、D2セルの値は数値です。

一方、E2セルのvlookup関数の第二引数ではAB列を範囲としているためA列を条件にデータを引っ張ってきて来ようとしていますが、A列の値は文字列となっています。

つまり、値としては同じ「111111」なのですが、セルの書式が数値の場合とセルの書式が文字列の場合は違うものとして扱われているということです。

このように、セルの書式の違いが原因で、同じ値でも違うものとして判断されるということがvlookupでは発生します。だから、「データはあるのに反映されない！」といったことが起こります。

データはあるのに反映されない場合の対応方法

この現象を回避するのは簡単です。

セルの書式を一致させることを意識すれば、データはあるのに反映されないといった現象はなくせます。

先ほどの例ではvlookupの結果は#N/Aエラーとなっていましたが、範囲セルと条件セルの書式は次のようになっていました。

なので、回避方法としてはセルの書式を一致させることを行います。具体的には、

• 範囲セルの書式を数値にする
• 条件セルの書式を文字列にする

のどちらかを行います。

条件セルの書式を文字列に合わせて#N/Aエラーを回避

条件セル（D列）の書式を文字列にした場合の例が、次のD3セルです。

設定している値はD2、D3セルともに「111111」です。

D2セルのvlookupの結果は#N/Aエラーとなっていますが、D3セルのほうは商品名が反映されており、想定通りの動きになっているのがお分かりになるかと思います。

何を行ったかというと、D3セルはセルの書式設定を「文字列」にしています。

D3セルをよく見るとわかりますが、セルの左上に緑の三角形が表示されており、これはセルの値が文字列となっていること意味しています。

vlookupの第二引数はAB列を指定しており、一番左のA列のセルの書式は文字列。条件セルであるD列も書式が文字列のため、同じ値＆同じ書式＝同じと判断され、検索にヒット＝反映されるようになりました。

セルの書式を文字列に変更する方法

セルを文字列にする方法ですが、次の通りです。ここでは、D4セルに対して書式を文字列にする方法をご紹介します。

まず、対象セルを右クリックし、セルの書式設定開きます。

次に、表示形式タブ内の分類から「文字列」を選択し、OKをクリックします。

最後に、セルに対して値を入力します。ここでは「111112」を入力していますが、入力後は文字として表示され、VLOOKUPでも商品名を取得できていることがお分かりになるかと思います。

基本的な設定方法は以上です。

既に数値が設定されているセルに対して書式を変更する場合の注意点

既に数値が設定されているセルに対して書式を文字列にする場合、単純に上記の方法では書式が文字列に反映されませんので注意が必要です。

例えば先述のサンプルのD2セルに対して書式を文字列したいような場合です。

D2セルに対して、「右クリック→書式設定→文字列に変更→OK」と一連の操作をした後、画面表示は次のようになっています。

セル内の値が右揃え表示から左揃え表示になったので、セルの書式は文字列になったことが確認できますが、E2セルのVLOOKUP関数の結果は依然として#N/Aエラーとなっています。

これは、D2セルの値が数値のままになっているためです。D2セルを見るとわかりますが、セルの左上に三角形のマークがついていません。

つまり、すでに数値が設定してあるセルに対して書式を文字列に変えただけでは、書式は文字列にまだ変わっていない、ということを意味しています。

では文字列にするにはどうすればよいかというと、セルをダブルクリックしてカーソルを外す、ということを行います。この操作をすることで、セル内の値が文字列として設定されなおされ、結果、VLOOKUPも有効に機能するようになります。

既に数値が設定されているセルに対して書式をまとめて変更するTips

先述の注意点では、

セルの書式設定を「文字列」に変更後、セルをダブルクリック＆カーソルを外す

という操作をすることでセルの書式に文字列が反映されるということをご紹介しました。

ただ、書式を文字列に変えたいというセルデータが大量にある場合、1セルずつダブルクリック＆カーソルを外す、という操作をして文字列化するのは、正直めんどくさくてやりきれないと思います。

そのような場合、文字列用の列を追加することで、一括でセルの書式を文字列化しvlookupで反映させることができます。

まず、D列とE列の間に行を追加します。

次に、追加した行（ここではE列）に、D列の値を文字列化する数式を入れます。E2列の場合は「=D2&""」を設定します。

&""をすることで、文字列になります。E列の表示は数値が左揃えになっていることが確認できると思います。

最後に、F列のvlookup関数で、第一引数の検索値をD列セルからE列セルに変更します。

以上で完了です。

ポイントは、

1. 文字列用の列を作り
2. その列の値を検索値としてVLOOKUPで使用する

ということです。

範囲セルの書式を数値に合わせて#N/Aエラーを回避

もう一つの#N/Aエラー回避方法は、数値同士で比較させる方法です。

先述の例では、vlookupの第二引数に設定している範囲（A列）のセルの書式が「文字列」になっていましたが、ここを「数字」として変換に使用します。

まず、A列のセルを見るとわかりますが、セルの左上に緑の三角表示されていますので、「文字列」書式となっています。A2セルにカーソルを合わせると、次のようにセルの右側または左側にビックリマーク（！）の表示がされます。

ビックリマークをクリックすると選択肢が幾つか表示されますので、「数値に変換する(C)」をクリックします。

すると、A2セルが数値に変換されます。値が右寄せで表示され、先頭の0がなくなり、単に「1」の表示に切り替わります。

これに伴い、E列に定義していたvlookup関数では、A2列の数字「1」とD2列の数字「1」で比較され一致するので、E2セルでは商品名が反映されるようになりました。

書式を合わせたけど反映されない場合

とはいえ、書式を合わせたけれどvlookupが#N/Aエラーとなってしまうケースもあります。

そのような場合は次の点を確認します。

vlookupの第二引数（検索範囲としたセル）について、一番左の列のデータが数字・文字列で混在しているような場合、一部データは値が反映されるが一部データは#N/Aエラーで反映されないという現象が発生します。

特に厄介なのが、このような現象になっているということに気づけない、という点です。

検索範囲としたセルが数万行になっているような場合、どこのセルで書式の不一致があるかを目視で確認することは現実的にできません。そのため、vlookupの結果が取れているかでしか判別ができないためです。

このようにデータが数字・文字列で書式が混在しているような場合、一度文字列に変換した列を作り、その列をキーにして使うとよいでしょう。

まとめ

エクセルのvlookupにおける、データはあるのに#N/Aエラーで反映されない時の原因とと対応方法についてご紹介しました。

ポイントはセルの書式を確認するということです。

既に値が入っているセルに対してセルの書式設定をしても有効化されないケースがありますので、今回ご紹介した方法を参考に対応してみてくださいね。

Excel 最強の教科書［完全版］--すぐに使えて、一生役立つ「成果を生み出す」超エクセル仕事術 [ 藤井 直弥 ]

また、この記事に書いたようなちょっとした困ったことでも、自分に経験や知識が足りないために対応できなかったり、時間をかけたわりに全然進まなかったりと、思うようにいかない事ってありますよね。そんなときは専門家に相談したり解決してもらったりする方法もありますので、一人で悩まず対処してみましょう。