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完全マスター: 関数・テクニック・新機能を網羅した決定版ガイド
本書は、Excelの基礎から応用まで、包括的に学べるガイドブックです。当サイトの豊富な情報を、電子書籍向けに最適化し、読みやすく再構成しました。
【本書の特徴】
- 情報量：Word-A4サイズ換算で1,400ページ相当
- 基本操作から高度なテクニックまで、段階的に学習可能
- 140種類の関数を詳細に解説
- 109種類の実用的なテクニックと具体例を紹介
- 最新機能（XLOOKUP関数、LET関数、シートビュー、LAMBDA関数、スピル等）を徹底解説
【対象読者】
- Excel初心者からプロフェッショナルまで
- 体系的にExcelスキルを向上させたい方
- 業務効率化を目指すビジネスパーソン
- データ分析や可視化のスキルを磨きたい方
【本書の強み】
- 実務に即した例題と解説
- 視覚的な図表やスクリーンショットで理解を促進
- 最新のExcelバージョンに対応した内容
- 著者の長年の経験に基づく、実践的なTipsを多数収録
【更新とサポート】
- 常に最新の情報を反映するため、定期的に内容を更新
- 最新版の入手方法：Amazonカスタマーサポートへお問い合わせください
【入手方法】
- 買い切り：950円
- Kindle Unlimited：読み放題プランで利用可能
Excelマスターを目指す方、業務効率を劇的に向上させたい方に、自信を持っておすすめできる一冊です。この1冊で、Excelの可能性を最大限に引き出す力が身につきます。