vlookupがなぜかN/A!文字列はあるのに反映されない時の対応方法

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

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

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

vlookupの結果がなぜかN/A!データはあるのになんどやっても反映されない・・・

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

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

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

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

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

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

vlookupが#N/Aとなる例
  • A1~B10セル:商品コード・商品名の対応表
  • D2セル:商品コードの手入力欄
  • E2セル:D2セルの商品コードを元に、AB列から該当する商品名をvlookupで抽出

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

VLOOKUPで反映されない事例

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

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

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

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

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

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

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

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

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

vlookupの結果がN/Aとなる原因

条件セルの書式が範囲セルの書式と一致していないため

※条件セル:vlookup関数の第一引数に指定したセル
※範囲セル:vlookup関数の第二引数に指定したセルの範囲の中で一番左の列のセル

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

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

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

vlookupが反映されず#N/Aとなるほとんどのケース

数値を条件にしてvlookupを使用している

例)商品コードを元に商品名を検索&反映したい

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

VLOOKUPで反映されない事例

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

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

「0000001」や「00102030」など、コードとして桁数を合わせるためにセルの書式設定が文字列になっている。

エクセルの商品コードと商品名一覧の例

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

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

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

エラーの原因

範囲セル(A列):文字列

条件セル(D列):数値(標準)

VLOOKUPで見つからない事例

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

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

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

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

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

#N/A時のセルの書式

範囲セル(A列):文字列

条件セル(D列):数値(標準)

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

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

のどちらかを行います。

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

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

条件セルを文字列にしてVLOOKUPした場合の例

設定している値は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も有効に機能するようになります。

セルをダブルクリック後のVLOOKUP結果

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

先述の注意点では、

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

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

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

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

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

文字列用の行の追加

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

追加した行にD列を文字列化する関数を設定

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

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

追加したE列に対してF列ではVLOOKUPする

以上で完了です。

ポイントは、

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

ということです。

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

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

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

例題としてA列の書式が文字列

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

セルにカーソルを合わせた際に表示されるビックリマーク

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

エクセルセルの数値変換方法

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

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

文字列から数字へのセルの書式の一括設定方法

セルを1つずつカーソルを合わせて「数値に変換する」を行うのは結構手間なので、複数のセルに対してまとめて数値に変換したい場合、次のように行います。

1.数値変換したいセルを範囲選択します。文字列セルを複数選択した場合もビックリマーク(!)が表示されますので、「数値に変換する」をクリックします。

複数セルの一括数値変換方法

2.以上で完了です。A列が文字列から数値に変換されましたので、E列でもルックアップ結果が表示されるようになりました。

ルックアップの数値変換結果

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

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

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

チェックポイント

VLOOKUPの第二引数の範囲内のデータのセルの書式が数字・文字列で混在していないか?

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

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

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

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

まとめ

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

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

vlookup関数を利用する際のポイント

第一引数のセル書式と、第二引数の一番左列のセルの書式を合わせる

VLOOKUP利用時のポイント

上記例の場合、D3セルとA列のセルの書式を合わせればOK!

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

Twitterでフォローしよう