Excel VLOOKUP関数で頻繁に発生する問題と対処法

VLOOKUP関数は強力な関数ですが

設定する引数が多く制約も多いため

問題があって意図通りに動作しない時に

原因の発見が難しい関数です。

この記事ではVLOOKUP関数で

ありがちな問題と対応方法を紹介します。

問題と対処法

#N/Aエラーが表示される

検索値が存在しない

#N/Aエラーは検索結果が

存在しない場合のエラーです。

本当に存在する場合は、

必然的に発生するものなので

そのままにしておいても構いません。

ただしSUM関数などに

VLOOKUP関数の結果を組み込んでいると

連鎖的に#N/Aエラーになるため

対処する必要があります。

範囲(引数2)に存在するはずなのに

このエラーが表示される場合は

他に原因があります。

その例を続けて記述します。

範囲指定が相対参照

VLOOKUP関数をコピーした場合に発生します。

下の例では範囲(引数2)が相対参照($を付けない)なため

コピーした際に範囲がズレて合致しません。

VLOOKUP関数が相対参照になっているためコピーした結果がエラーとなっている例

VLOOKUP関数をコピーする場合は

範囲(引数2)に$を付けて絶対参照にします。

(他の関数にも言えますが)

VLOOKUP関数の範囲を絶対参照に修正

検索される側の検索値が左端にない

VLOOKUP関数に慣れていても

引っかかりやすい罠です。

範囲(引数2)の左端が「地方」のため

「№」による検索が失敗している例です。

範囲の左端が検索値ではないため検索に失敗している例

この場合は範囲(引数2)を

C列から指定する必要があります。

どうしても表の左端に検索される列を

置けない場合は下記の方法を参照してください

前後に空白

前後に空白が混入すると一致しなくなります。

意外と発生頻度が高いので

VLOOKUP関数がうまくいかない時は

空白が混ざっていないか確認しましょう。

あらかじめ検索値と検索される列に対して

SUBSITUTE関数をかけて新たな列を作り、

その列をVLOOKUP関数の

引数に指定するのも選択肢です

検索項目の表記ゆれ

全角・半角、小さい文字や濁点・半濁点は

気付きにくいので疑ってみましょう。

全てに対応できるわけではありませんが、

半角と全角はバイト数が異なるため

調査の際にLENB関数で比較したり

ASC関数やJIS関数で半角や全角で

統一した列を作るのも選択肢になります。

表示形式が異なる

下の例は数値の「100000」を

表示形式でカンマ区切りしている値と

文字列として「100,000」を比較して

VLOOKUP関数に失敗している例です。

表示形式が片方が数値で、

片方が文字列であると一致しません。

カンマ形式で表示されている数値と、文字列のカンマ付き数字でVLOOKUP関数に失敗している例

見た目が同じのため気付き辛い例です。

VLOOKUP関数で上手くいかない際には

表示形式を「標準」にして探すことを

選択肢に入れましょう。

日付でも起こり得ますし、

少数の桁を途中で切っている可能性もあります。

頻発する場合は検索値(引数1)に

VALUE関数を組み込んで表記の漏れを

吸収するのも選択肢です。

#REF!エラーが表示される

#REF!エラーが表示されるときは

表の範囲(引数2)が狭いため

列番号(引数3)に対応する列がない場合です。

下記の例は1列の範囲しか設定していないのに

列番号に2を指定しているためエラーになっています。

表の範囲(引数2)が狭く、#REF!エラーが出る場合

この場合は範囲を広くして値を取得する列を含めれば

#REF!エラーが解消して解決します。

表の範囲を広く修正した例

意図しない結果が表示される

存在しないはずの0が表示

検索結果が空白の場合、0になる仕様です。

下記の記事が対処方法になります。

検索値に合致する値が複数

検索値に合致する値が複数あると

最初に合致する値を取得します。

検索値に合致する値が複数ある例

この例では№の値を変更するか、

複数の結果に対応した数式にする必要があります。

検索方法(引数4)が省略されているかTRUE

検索方法(引数4)が省略かTRUEの場合は

あいまい検索となり検索値に完全一致しなくても

結果が表示されてしまいます。

(検索値以下で最大のものが表示される)

下の例では検索値に10を指定しますが、

存在しないため最大の9である

島津が表示されてしまいます。

この検索方法を使用する頻度の

少ない人が多数と思われます。

VLOOKUP関数を多用する方は

最後にFALSEを指定する癖を付けましょう。

関連記事

VLOOKUP関数の使い方・活用方法の記事一覧

遭遇しやすい問題への対処方法の記事一覧