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

2016年4月12日

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

前後に空白が入っている

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

全角・半角が違う

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

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

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

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

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

という場合があります。

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

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

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

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

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

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

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

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

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

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

が検索されます。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

小数の演算誤差の影響

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

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

• 時刻
• パーセンテージ

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

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

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

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

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

おすすめ記事

• エクセルで取引先の情報を転記するには？
• vlookup関数のエラー処理にiferror・iserror関数を使わない理由
• 計算式のコピーでvlookup関数の列番号をずらす簡単なテクニック
• vlookup関数で条件に一致する最終行を抽出する方法
• vlookup関数で複数条件を指定する方法
• vlookup関数を使いこなすために「アナログなイメージ」を掴もう
• vlookup関数で左側の値を取得する方法、、、って意味あるの？