vlookup関数がおかしい・エラーを解消する8つのチェックポイント

vlookup関数を使うと非常に効率的な作業ができます。
でも、慣れないと「#REF!」「#N/A!」などのエラーが、エラーが出なくても狙い通りの値が表示されないなど、トラブルが続出します。

そこで、vlookup関数がうまくいかない場合にチェックすべき8つのポイントをまとめました。
うまくいかない原因を効率的に突き止めていきましょう!

※下記を再生しても音は出ませんので、音が出せない環境でもご安心ください。

vlookup関数が動かない8つの原因と対策

チェックが簡単なものから順番に並べています。
うまくいかない原因がまったくわからないときは、最初から順番にチェックをしていってください。

入力した計算式がそのままセルに表示されてしまう

セルの書式設定(表示形式)が「文字列」になっていると、計算式がそのまま表示されます。

セルの表示形式を「文字列」以外のもの(例えば「標準」など)に変更しましょう。

なお、計算式を入力した後にセルの書式設定を直す場合は注意が必要です。

セルの書式設定を変更しただけでは、計算式が計算されません

書式設定を変更した後に、一回セルを選択して「Enter」を押してセルの内容を再確定させてください。
これで、計算式が計算されます。

表の選択範囲が狭すぎて「#REF!エラー」が出る

誤った例:
=vlookup(D2, $A$1:$A$6, 2, FALSE)

2つめの引数で参照先の表を指定するのですが、

このとき、表の範囲は、次のように設定をしないといけません。

  • 検索対象となるキー項目が一番左の列
  • 実際に表示させたい列を含める

今回は、
A列をキーにしてB列の値を表示させたいのであれば
次のように、参照先の表は「A列からB列まで」指定しないといけません。

正しい例:
=vlookup(D2, $A$1:$B$6, 2, FALSE)

表の選択範囲を「絶対参照」にしていないため「#N/A!」エラーが出る

誤った例:
=vlookup(D2, A2:B6, 2, FALSE)

2つめの引数で参照先の表を指定するとき、参照先の表は「絶対参照」で入力しましょう。

正しい例:
=vlookup(D2, $A$2:$B$6, 2, FALSE)

絶対参照で入力しないと、
計算式を別のセルにコピーすると、参照先の表の範囲がずれてしまうので、計算結果がおかしくなります。

vlookupokashii_1_3

↓絶対参照に直すと

vlookupokashii_1_4

検索項目の表記ゆれで「#N/A!」エラーが出る

検索したい値の表記が完全に一致していない場合には、vlookup関数はうまく動きません。

ありがちな例として、次のようなものがあります。

前後に空白が入っている

値の前後に空白文字が入っている場合、目で見てもわかりにくいのですが、vlookup関数で抽出することができません。
trim関数等を使って、空白を取ってからvlookup関数を使いましょう。

全角・半角が違う

値が片方が全角、片方が半角で入力されている場合にも、vlookup関数で抽出することができません。
特に、全角空白、半角空白が混在していると、見た目でわかりにくいのでやっかいです。

こういう場合には、asc関数・jis関数などで全角・半角を揃えてからvlookup関数を使いましょう。

ひらがな、カタカナの小さい文字や、濁点・半濁点が違う

預金入出金データを扱う場合には、ありがちなパターンなのですが、

  • 片方が「チュ」、もう片方が「チユ」というように、小さい文字かどうかが違う
  • 片方が「シ゛」(シ+濁点)、もう片方が「ジ」(濁点含め1文字)というように、濁点の具合が違う

という場合があります。

この場合も、vlookup関数はうまく動きません。

4つめの引数を指定していないため検索結果がおかしい

例えば、次のようにvlookup関数を使うとエラーが出る場合があります。

望ましくない例:
=vlookup(A1, $D$1:$E$999, 2)

vlookup関数を使うときには、本来は4つの引数(かっこの中のデータ)を入れます。
ただ、4つめの引数は省略することができます。

4つ目の引数を省略した場合、

  • 参照先の表(上の式だと$D$1:$E$999の一番左列のD列)が、事前に並び替えされていないと正しい結果が得られません
  • 参照先の表に、目的のデータがない場合でも、目的のデータに一番近いデータの値を検索してしまいます。
正しい結果が得られない例:
vlookupokashii_1_11

vlookup関数に慣れていないうちは、何も考えずに「FALSE」を入れておいたほうがいいです。

望ましい例:
=vlookup(A1, $D$1:$E$999, 2, FALSE )

参照先の表に、同じ項目が複数ある

参照先の表に、同じ項目が複数ある場合、

  • vlookup関数の4つめの引数がfalseだと一番上の行
  • vlookup関数の4つめの引数がtrueだと一番下の行

が検索されます。

上で書いた行「以外」の行を抽出したい場合、vlookup関数ではうまく動きません。

vlookupokashii_1_5

慣れないうちは、参照先の表には、同じ項目は1つだけにするようにしましょう。
※参考:vlookup関数で条件に一致する最終行を抽出する方法

セルの表示形式が原因で「#N/A!エラー」が出る

エクセルでは、vlookup関数が一致しているかどうかは、
書式設定をする前の実際に記録されている「値」が一致しているかどうかで判断しています。

仮に見た目が一緒だとしても、「実際に記録されている値」が異なると、vlookup関数でエラーが出てしまいます。

対応1:セルの表示形式を「標準」にして確認をする

エクセルで実際に記録されている値を確認するためには、セルの表示形式を「標準」にしてみてください。

この状態で「表示されている値が全く一緒」でなければvlookup関数では、値が抽出できません。

vlookupokashii_1_6

↓セルの書式設定を「標準」に直す

vlookupokashii_1_7

↓その状態で同じ値になるように修正すると

vlookupokashii_1_8

対応2:セルの文字の配置を「標準」にして確認をする

さらに見た目が全く一緒でも抽出できない場合もあります。

エクセルは、入力されたデータを「数値」か「文字列」として管理しているのですが、
この「数値」「文字列」の区分が一致していないと、うまく抽出ができません。

数値か文字列かを見分けるためには、セルの表示形式を標準にした後に、セルの文字の配置(横位置)を「標準」にしてください。

次のように、左詰め、右詰めのどちらになるかが変わります。

  • 文字列の場合:左詰め
  • 数値 の場合:右詰め

そして、この「文字列」「数値」の状態が一致していないと、全く同じ値でもエラーになってしまいます。
このような状態になった場合には、文字列・数値の状態を揃えるようにしましょう。

vlookupokashii_1_9

↓文字列・数値の状態を揃えると

vlookupokashii_1_10

小数の演算誤差の影響

小数をキーにしてvlookup関数を使うという場合には、演算誤差の影響で、vlookup関数がうまく動かない場合があります。

見た目で小数を使っているように見えなくても、

  • 時刻
  • パーセンテージ

などは、エクセル内部では「小数」でデータを管理していますので、vlookup関数がうまく動かない場合があります。

vlookup関数のキー項目には、小数は使わないようにしましょう。
※参考:小数をif関数、vlookup関数に使うときは演算誤差に注意

vlookup関数の計算式が正しくても「#N/A!」エラーになる場合も

vlookup関数の計算式が正しくても、
検索したい値が参照先の表に存在していない場合には「#N/A!」というエラーが表示されます

結局、エラーが出たからといって、計算式が間違えているとは限りませんので、ご注意ください。

エクセル基礎講座 「無料」動画マニュアル

「経理事務のためのエクセル基礎講座(初級編)」(動画マニュアル 総収録時間162分)を無料プレゼント中です!

このマニュアルで解説していることを一通り学べば、経理事務を行う上で最低限必要となる知識が得られます。

ご登録者の方には、合わせて、公認会計士が実体験を通して身に付けたエクセルを使う技をメールにてお伝えしていきます!

無料動画講座 登録フォーム

※ご登録頂いたメールアドレスに、エクセルを使いこなすための情報を配信するメールセミナー「エクセル倍速講座」も合わせて配信させていただきます。