クエッションマーク

エクセルを用いて集計作業をする際にとても役立つ関数の1つがVLOOKUPです。VLOOKUP関数を使うことで、条件に一致する情報を一覧から抽出することができますよね。

私の中では、エクセル関数の中で一番便利だと思っている関数です。

VLOOKUP関数は便利なのですが、たまに次のような現象が発生することがあります。

データはあるのに、該当するデータが抽出できずうまくいかない・・・

関数の使い方も間違っていないし、目視で見ても条件に一致するデータが範囲内に存在している。だけどVLOOKUPで一致するデータが無いという結果になる、という具合です。特に数値データ(商品コードなど)を条件にVLOOKUPを行っている場合に発生することが多いです。

VLOOKUPで見つからない事例

このような現象を一度は経験したことはないでしょうか。

そこで今回は、エクセルのVLOOKUP関数を使う際にたまに発生する、該当するデータが見つからずにうまくいかない場合への対応方法をご紹介します。

VLOOKUP関数は便利な反面、使用するデータによっては

データはあるのに見つからない

データの途中から何かうまくいかない

ということが発生します。これからご紹介するポイントを押さえて、このような現象を回避できるようにしょう!

VLOOKUPでうまくいかない原因

VLOOKUPが#N/Aとなる原因

まず初めに結論からです。

VLOOKUP関数でうまくデータが見つからない(VLOOKUPの結果がN/Aとなる)のは、

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

に発生しています。

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

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

おそらく、次のようなケースにおいてVLOOKUP関数でうまくいかない現象が発生すると想定されます。

VLOOKUPがうまくいかない場合のほとんどのケース

数値を条件にしている

例)商品コードを元に商品名をVLOOKUPしたい

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

では、なぜこのようなケースで発生する場合が多いのか。

それは、コードが書いてあるセル(列)の書式が「文字列」になっている場合があるからです。

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

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

この一覧をVLOOKUPで使用した場合、条件セルの値を数値にすると、うまくいきません。

例えば次の例は、D2セルに検索値として「111111」を設定し、この情報を元にE2列ではAB列を範囲としてVLOOKUPしているサンプルです。E2セルのVLOOKUP関数では#N/Aとなっており、該当するデータを取得できていません。

VLOOKUPで見つからない事例

E2セルの数式に問題はありませんし、A~B列に条件として設定した「111111」が存在していますので、VLOOKUPで該当する名称「商品111111」がヒットしそうですが、出てきていないことがお分かりになるかと思います。

このような現象がVLOOKUPでは発生するので、セルの書式を一致させて回避することが必要になります。

うまくいかない場合の対応方法

先ほどの例ではVLOOKUPで該当データが見つからずに#N/Aとなっていました。

理由は書式が一致していないためです。

エラーの原因

範囲セル(先述のA列):文字列

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

なので、回避方法としては

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

のどちらかを行えばよいのです。

セルの書式を文字列に合わせる

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

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

設定している値はD2、D3セルともに「111111」ですが、D2セルのほうはVLOOKUPの結果が#N/Aとなっていますが、D3セルのほうはVLOOKUPで商品名が取得できているのがお分かりになるかと思います。

何を行ったかというと、D3セルはセルの書式設定を「文字列」にしています。D3セルをよく見るとわかりますが、セルの左上に緑の三角形が表示されているかと思います。

エクセルセル内左上の三角形

これは、D3セルの値が文字列となっていることを意味しています。ルックアップ元の範囲(A~B列)の一番左の列のセルの書式は文字列のため、条件値であるD列も書式を文字列にすることで、ヒットするようになりました。

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

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

エクセルのセルの書式設定を開く手順

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

エクセルのセルの書式設定画面

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

書式が文字列のセルに商品コードを設定した後の状態

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

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

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

例えば上記サンプルのD2セルの場合、セルの書式設定を「文字列」に設定した後の状態は次のようになっています。

既に値が設定されているセルに対して書式を文字列にした直後

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

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

つまり、セルの書式設定を変更したけれど値は文字列になっていない、ということを意味しています。

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

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

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

先述の注意点1では、

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

という操作をすることで値が文字として認識される点をご紹介しました。

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

そのような場合、文字列用の列を追加することで、一括でセルの書式を文字列化し、VLOOKUPでヒットするようにすることができます。

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

文字列用の行の追加

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

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

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

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

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

以上で完了です。

ポイントは、

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

ということです。

条件セルを数値に合わせる

もう一つの方法は、数値同士で比較をする方法です。

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

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

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

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

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

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

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

これに伴い、E列に定義していたVLOOKUP関数では、A2列の数字「1」とD2列の数字「1」でルックアップがかかり、E2セルでは商品名が表示されるようになりました。

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

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

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

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

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

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

書式を合わせたけどうまくいかない場合

これまで説明した通りですが、VLOOKUP関数を利用する際のポイントは次の通りです。

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

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

VLOOKUP利用時のポイント

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

とはいえ、これを行ってもうまくいかない場合というのもあります。そのような場合は次の点を確認してみてください。

チェックポイント

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

VLOOKUPで検索範囲としたセルについて、一番左の列のデータが数字・文字列で混在しているような場合、一部データはうまく値が取れるけれど、一部データは#N/Aとなってうまく見つからないという現象が発生します。

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

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

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

まとめ

エクセルのVLOOKUPでうまくいかない場合の原因と対応方法についてご紹介しました。

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

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

Twitterでフォローしよう

おすすめの記事