Excel VLOOKUP関数で頻繁に発生する問題と対処方法
VLOOKUP関数は強力な関数ですが設定する引数が多く制約も多いため
問題があって意図通りに動作しない時に原因の発見が難しい関数です。
この記事ではVLOOKUP関数でありがちな問題と対応方法を紹介します。
XLOOKUP関数で変更のある箇所も記載しています。
問題と対処法
#N/Aエラーが表示される
検索値が存在しない
#N/Aエラーは検索結果が存在しない場合のエラーです。
本当に存在しない場合は必然的に発生するものなので
そのままにしておいても構いません。
ただしSUM関数などに検索結果を組み込んでいると
コントロールできるように改善されています。
もし範囲(引数2)に存在するはずなのに
#N/Aエラーが表示される場合は他に原因があります。
その例を続けて記述します。
範囲指定が相対参照
下の例では範囲(引数2)が相対参照($を付けない)なため
コピーした際に範囲がズレて合致しません。
VLOOKUP関数をコピーする場合は範囲(引数2)に$を付けて絶対参照にします。
（他の関数にも言えますが）
検索される側の検索値が左端にない
VLOOKUP関数に慣れていても引っかかりやすい罠です。
範囲(引数2)の左端が「地方」のため「№」による検索が失敗している例です。
この場合は範囲(引数2)をC列から指定する必要があります。
どうしても表の左端に検索される列を置けない場合は特殊な対応が必要です。
XLOOKUP関数では指定方法が変わり、この問題がなくなっています。
前後に空白
前後に空白が混入すると一致しなくなります。
意外と発生頻度が高いのでVLOOKUP関数がうまくいかない時は
空白が混ざっていないか確認しましょう。
あらかじめ検索値と検索される列に対してSUBSITUTE関数をかけて新たな列を作り、
その列をVLOOKUP関数の引数に指定するのも選択肢です
検索項目の表記ゆれ
全角・半角、小さい文字や濁点・半濁点は気付きにくいので疑ってみましょう。
全てに対応できるわけではありませんが、半角と全角はバイト数が異なるため
調査の際にLENB関数で比較したりASC関数やJIS関数で半角や全角で
統一した列を作るのも選択肢になります。
表示形式が異なる
下の例は数値の「100000」を表示形式でカンマ区切りしている値と
文字列として「100,000」を比較してVLOOKUP関数に失敗している例です。
表示形式が片方が数値で、片方が文字列であると一致しません。
見た目が同じのため気付き辛い例です。
VLOOKUP関数で上手くいかない際には
表示形式を「標準」にして探すことを選択肢に入れましょう。
日付でも起こり得ますし、少数の桁を途中で切っている可能性もあります。
頻発する場合は検索値(引数1)にVALUE関数で表記の漏れを吸収するのも有効です。
#REF!エラーが表示される
#REF!エラーが表示されるときは表の範囲（引数2）が狭いため
列番号（引数3）に対応する列がない場合です。
下記の例は1列の範囲しか設定していないのに
列番号に2を指定しているためエラーになっています。
この場合は範囲を広くして値を取得する列を含めれば#REF!エラーが解消して解決します。
意図しない結果が表示される
存在しないはずの0が表示
検索結果が空白の場合、0になる仕様です。
問題がある場合、数式で対処する必要があります。
検索値に合致する値が複数
検索値に合致する値が複数あると最初に合致する値を取得します。
この例では№の値を変更するか、
複数の結果に対応した数式にする必要があります。
検索方法(引数4)が省略されているかTRUE
検索方法(引数4)が省略かTRUEの場合は
あいまい検索となり検索値に完全一致しなくても
結果が表示されてしまいます。
（検索値以下で最大のものが表示される）
下の例では検索値に10を指定しますが、
存在しないため最大の9である島津が表示されてしまいます。
この検索方法を使用する頻度の少ない人が多数と思われます。
VLOOKUP関数を多用する方は最後にFALSEを指定する癖を付けましょう。
なおXLOOKUP関数では完全一致が既定値になるため、この問題が解消されます。
Excelを効率的に習得したい方へ
当サイトの情報を電子書籍用に読み易く整理したコンテンツを
買い切り950円またはKindle Unlimited (読み放題) で提供中です。
Word-A4サイズ：1,400ページの情報量で
（実際のページ数はデバイスで変わります）
基本的な使い方、関数の解説(140種類)、
頻出テクニックと実用例(109種類)、
XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピルなど便利な新機能を紹介。
体系的に学びたい方は是非ご検討ください。
アップデートなどの更新事項があれば随時反映しています。
なお購入後に最新版をダウンロードするには
Amazonへの問い合わせが必要です。