コスパ最強の格安中古パソコンはこちら!

【決定版】VLOOKUPでエラーが出る・うまくいかない原因

VLOOKUP関数のエラーやうまくいかない原因

エクセルのVLOOKUP関数でエラーが出たり、うまくいかない場合などおかしいなと思う時に確認する内容をまとめておきました。

経験上、うまくいかない場合は今回紹介する項目を確認すれば大概うまくいきます。

みんな間違うところって大体同じだと思います。

VLOOKUP関数の基本的な考え方はこちらの記事を読みましょう。

VLOOKUP関数を初心者向けに分かりやすく解説 【初心者向け】Excel関数のVLOOKUPの使い方

VLOOKUPでエラーが出る原因

よくあるVLOOKUP関数でエラーが出る・うまくいかない原因をあげていますので、改めて確認していきましょう。

検索値は1番左の列にあるか

この間違いはかなり多いのできちんと覚えておきましょう。

検索値を検索されるのは、範囲として指定した1番左の列のみです。

これは絶対に覚えておきましょう。人が資料のリストから何かを探すときも、1番左の列で判断してから、右にある情報を読みますよね?それと同じなんです。

VLOOKUPは範囲の一番左を検索する

上記のような場合に、「=VLOOKUP(B4,J3:M14,4,FALSE)」と入力していれば、J列にB4の値がなければ、検索値なしと判定されます。(それ以外の列に該当の値があっても検索されません。)

そのため、VLOOKUP関数を使う場合には検索範囲の左端に検索キーとなる値をもって来る必要があります。

VLOOKUP関数に関係なくともキーとなる値は1番左の列においておくのが表としても見やすいですね。

セルの書式設定が違う

セルに設定されている書式が違う場合には同じ値とみなされない場合があります。

これが原因でうまくいかない場合が結構見受けられるので気を付けましょう。

エクセル関数で同じ値が認識されない エクセル関数で同じ値とみなされない場合に確認する項目

セルの書式設定が原因で違う値とみなされる可能性があるのは意外と知られていないですね。

値を取り出す列の指定は合っているか

列の指定は、A列から数えるのではなく、選択範囲の左端からの列数を指定します。

また、列の番号は範囲の1番左の列を1として数えられ、指定した範囲以外の列は指定できません。

=VLOOKUP(A1,B2:C4,2,FALSE)

例えば、上記の式のように範囲をB2~C4と指定し、指定列を2とした場合は、C列の値が取得されます。

この範囲の場合だと列に3以上の数値を指定しているとエラーが表示されます。

MEMO
結果として取得したい値がある列は指定範囲に含めておきましょう。

絶対参照になっているか

エクセル関数の参照には「相対参照」と「絶対参照」の2つに分けられます。

相対参照の式をコピーして他のセルに貼り付けた場合は、指定したい範囲がずれてしまっている場合あります。

例:F2で入力した「=VLOOKUP(E2,B2:C3,2,FALSE)」がF3だと「=VLOOKUP(E2,B3:C4,2,FALSE)」に変更されている。

コピーしたら範囲がずれてしまう

相対参照と絶対参照の違い
相対参照はC1に対してA1は2列左のセルというようにC1を基準にして指定されるので、D1に置き換えるとB2が該当のセルになります、絶対参照の場合はC1でもD1でも、どのセルでもA1を参照します。セルを指定する際に$A$1というように「$」を付けることで絶対参照となります。(行や列ごとに参照先をずらしたい場合などは、$A1のように固定したい列や行の方だけに「$」をつけます。)

このように参照先がずれてはいけない場合は、絶対参照を用いて指定しましょう。

上記の例の場合だと、「=VLOOKUP($E2,$B$2:$C$3,2,FALSE)」というように指定する必要があります。

検索は「FALSE」を指定しよう

VLOOKUP関数の4つ目の引数は検索方法を指定する項目です。

検索方法は「TRUE」「FALSE」の2通りを指定することができます。

  • TRUE:近似値
  • FALSE:完全一致

特別な理由がない場合は、検索方法は「FALSE」の完全一致を指定しましょう。

「TRUE」を指定していると近似値で結果が表示されるので、想定していない結果が表示される場合があります。

上から1番目の値が取得される

VLOOKUP関数は1番左の列の上から検索をかけていくので、同じ値が2つ以上あった場合は1番上にあるものが取得されます。

下の画像のような場合は、倉庫コードが110の方が結果として取得されます。

VLOOKUPの検索値の重複

そのような場合には、文字の連結などで一意となる項目を改めて作成するのが簡単にできて良いでしょう。

文字の連結
セル同士の文字の連結は「=A1&A2」のように「&」を使用すると文字の連結が可能です。

VLOOKUPの検索値の重複の回避

文字列の前後に空白が入っている

一見同じに見えても文字列の前後に空白が入っている場合があります。

その場合は、違う値と認識されるのでこちらの記事を参考に、空白を削除しましょう。

文字列の前後の空白を一括で削除する方法 文字列の前後の空白(スペース)を一括で削除する方法2通り

数式が計算されない

セルの書式設定が「文字列」に設定されているセルに数式を入力しても、そういう文字列として計算されずに表示されます。

数式が計算されずに表示される

その場合は、セルの書式設定を「標準」に設定したあと、セルを編集状態にしてから「Enter」を押すと、数式が計算されて表示されます。

MEMO
数式が「文字列」として表示さている状態で書式設定を変更しても数式が計算されないので、再度入力を確定しなおしましょう。

それでもうまくいかない場合

上記の内容を確認して修正したにも関わらずエラーが出る場合にはこちらの内容を見てみましょう。

検索値がなくてもエラーが表示される

VLOOKUP関数の式の入力は正しくても、実際の参照先に該当する値がなかった場合はエラーが表示されます。

その場合は結果が「#N/A」と表示されています。

基本をおさらいしよう

VLOOKUP関数の基本的な考えをおさらいしましょう。

VLOOKUP関数のことを知らずにパターンで覚えている人が多いように思います。

理解していると、間違いを自分で見つけられるようになります。

VLOOKUP関数を初心者向けに分かりやすく解説 【初心者向け】Excel関数のVLOOKUPの使い方

VLOOKUP関数で表示されるエラーの意味

表示されたエラーからどういう間違いをしているか、おおよその判断をすることができます。

#N/A

該当するものがなかった場合に表示されます。

そのため、式の入力自体が正しくても実際に該当がなかった場合にも表示されます。

また、見た目は同じであったも空白が入っていたり、文字の書式が異なっていたりすると表示されます。

この場合は、検索値と検索範囲の書式や入力内容を確認しましょう。

#REF!

列の指定が選択範囲を超えて設定されていたる、参照先がなかった場合に表示されます。

列の指定が範囲の左端からの数となっていることや参照先を正しく指定できているか確認しましょう。

#NAME?

このエラーは関数名が間違っている場合に表示されます。

VLOOKUPの「O」が抜けていないかなど確認しましょう。

最後に

エクセル関数を使うようになって初めの難関であろうVLOOKUP関数のエラーやうまくいかない場合の原因をまとめました。

1つ1つ項目を確認して、原因を解消していきましょう。

コメントを残す