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

2020年11月23日

VLOOKUP関数は強力な関数ですが設定する引数が多く制約も多いため

問題があって意図通りに動作しない時に原因の発見が難しい関数です。

この記事ではVLOOKUP関数でありがちな問題と対応方法を紹介します。

XLOOKUP関数で変更のある箇所も記載しています。

問題と対処法

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

検索値が存在しない

#N/Aエラーは検索結果が存在しない場合のエラーです。

本当に存在しない場合は必然的に発生するものなので

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

ただしSUM関数などに検索結果を組み込んでいると

連鎖的に#N/Aエラーになるため対処する必要があります。

XLOOKUP関数では引数:見つからない場合によって

コントロールできるように改善されています。

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

#N/Aエラーが表示される場合は他に原因があります。

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

範囲指定が相対参照

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

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

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

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

VLOOKUP関数コピーする場合は範囲(引数2)に$を付けて絶対参照にします。

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

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

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

VLOOKUP関数に慣れていても引っかかりやすい罠です。

範囲(引数2)の左端が「地方」のため「№」による検索が失敗している例です。

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

この場合は範囲(引数2)をC列から指定する必要があります。

どうしても表の左端に検索される列を置けない場合は特殊な対応が必要です。

XLOOKUP関数では指定方法が変わり、この問題がなくなっています。

前後に空白

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

意外と発生頻度が高いので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を指定する癖を付けましょう。

なおXLOOKUP関数では完全一致が既定値になるため、この問題が解消されます。

関連記事

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

よくあるトラブルの原因と対処方法の一覧