エクセルの関数

VLOOKUP関数でエラーがでた時の対策一覧

慣れないエクセルを使い始めた時に、VLOOKUP関数でエラーが出て困った事はありませんか?

先輩に聞きたいけど怖い…
エラーの直し方を調べたけどよくわからない…

安心してください。この記事ではエクセルのVLOOKUP関数のエラーとその対策をすべてご紹介します。

この記事を読めばあなたが直面しているエラーも必ず直せます。

VLOOKUPのエラー一覧

VLOOKUPでエラーが出る原因は以下の理由です。まずはあなたがどのエラーでつまづいているか確認をしましょう。

  1. エクセルに列を挿入したせいで、参照先がずれている
  2. データが増えたせいで、選択範囲が足りていない
  3. VLOOKUPを入力しているセルをコピペして移動させた
  4. 参照先のセルが文字列になっている
  5. 参照先のセルにスペースが入力されている(全角・半角)
  6. 参照先と参照元でエクセルのバージョンが違う
  7. 欲しいデータを引っ張ってこない

早速一つひとつ解説していきます。

エクセルに列を挿入したせいで、検索先がずれている

元々の表に項目を追加したくて、列を挿入した場合、VLOOKUP関数でエラーがでることがあります。

正常なVLOOKUP正常なVLOOKUP

 

列を挿入したせいでエラーがでる列を挿入したせいでエラーがでる
エラー対策 → 挿入された列分だけ数値を増やす

=VLOOKUP($F4,$B$4:$D$8,23,FALSE)

これだけ小さい表だと列を挿入したらすぐにエラーに気づくことができますが、何気なく追加した列のせいで他のシートや別のエクセルのVLOOKが壊れているというのはよくある事です。

エクセルで列を追加する時はそのデータが他のエクセルで使用されていないか確認しましょう

私も入社当時に先輩が作った表に列を追加しまくったせいで先輩に迷惑をかけたことがありますので、絶対に気安く列を追加するのだけはやめましょう。

データが増えたせいで、検索できていない

膨大なデータでずっと使っているエクセルで起こりがちなエラーです。

選択範囲が足りない選択範囲が足りない

ちゃんと式もあってるのに、E4に6と入力するとエラーが出る!

これはVLOOKの選択範囲が8行目までしか選択されていないからです。

エラー対策 → 選択範囲を増やす

=VLOOKUP($E4,$B$4:$D$89,2,FALSE)

VLOOKUPを入力しているセルをコピペして移動させた

=VLOOKUP($E4,B4:D9,2,FALSE)

この式が入力されているセルを一つ右にコピペしたとします。

VLOOKUPをコピペVLOOKUPをコピペ

 

これはVLOOKUPのセルを右に一つ移動させたことにより、式の中身もすべて右に一つずれたことが原因です。

エラー対策 → 選択範囲を$で挟む

=VLOOKUP($E4,$B$4:$D$9,2,FALSE)

エクセルの本では絶対参照とか難しい言葉で解説されていますが、要するに$で選択範囲を挟むと選択範囲を固定できるのです。

特別な使い方をしない限りはVLOOKUPで選択範囲を指定する場合は$で挟むようにしましょう。

$の入力の仕方は選択範囲を指定している時にキーボードのF4を押すのです。

参照先のセルが文字列になっている

VLOOKをおぼえたての人から一番質問が多いのはこのエラーです。

文字列を参照文字列を参照

 

B4もE4も見た目は同じ”1”なのになぜかエラーが出てしまいます。これは参照元が数値ではなく、文字列として認識されているためです。

エラー対策 → 文字列を数値にしてあげる
文字列を数値に変換文字列を数値に変換

 

文字列のセルを選択するとビックリマークが表示されるので、そこにカーソルを合わせ、「数値に変換する」を選択します。

これでエラーは直ります。

参照先のセルにスペースが入力されている(全角・半角)

このエラーも問い合わせが多いです。

半角によるエラー半角によるエラー

 

見た感じ同じですよね。でもエラーが出ています。

実はB4のaの後ろに半角スペースが入っているのです。

エラー対策 → 全く同じ文字なのにエラーが出る場合はスペースや半角・全角を疑う

VLOOKUPでデータを引っ張ってくるときはできるだけ、綺麗な(整った)値を参照するようにしましょう。

また、自分がデータを作成する時も不必要なスペースやわかりにくい全角・半角は使わないようにしましょう。

検索先のエクセルバージョンが違う

古いシステムからエクセルをダウンロードした時にたまに起こるエラーです。

古いエクセルを使用した場合古いエクセルを使用した場合

 

今使っているパソコンのエクセル(.xlsx)ではたくさんの行を表示することができます。

しかし、1バージョン前のエクセル(.xls)は今のエクセルより表示できる量が少ないのです。

ということは、昔のエクセル(.xls)を使って(.xlsx)のシートを参照しようとした場合、表示できる量が足りないのでエラーが出てしまいます。

エラー対策 → .xlsのデータを.xlsxのシートに貼り付けてそこでVLOOKUPをする

欲しいデータを引っ張ってこない

=VLOOKUP($E4,$B$4:$D$9,2)

この式を入力して仮に欲しいデータを引っ張ってきてくれない場合、考えられる原因はfalseを入力していないからです。

=VLOOKUP($E4,$B$4:$D$9,2,FALSE)

FALSEの説明はごちゃごちゃしているので省きます。でも、これだけはおぼえておいてください。

特別な理由が無い限りとりあえずFALSEを入力!

まとめ

仕事でエクセルのVLOOKUPを使っていて出くわすエラーは網羅できたと思います。

エラーが出た時は焦ると思いますが、冷静にデータをチェックすれば必ず原因を見つけることができます。

原因を見つけるのは慣れるまで時間がかかるかもしれませ。

でも、今回ご紹介した対策を頭の隅に置いておけば、確実に最短距離で原因を見つける事が可能です。

VLOOKUPでお困りの際はお気軽にツイッターでご相談ください。