【Excel】VLOOKUPが動かない!エラーが出た!を解決!

LINEで送る
Pocket

何かとエラーが出たり、思うように動かないことが多いVLOOKUP関数ですが、使いこなせるようになれば大幅に作業を効率化できますので是非マスターしたいものです。

今回は私が今までにVLOOKUP関数でつまづいた原因をまとめてみました。

スポンサーリンク

VLOOKUPのよくあるエラーの原因

サンプル画像は右側のブルーの表が参照先(検索範囲)のリストになります。

サンプル画像の見方

不要なスペースが入っている

文字列の前後などに不要な空白文字が入っていると#N/Aエラーが返ってきます。

セル内に不要なスペースが入っている

表を見てもパッと見では気づきにくいのでデータが多い場合はTRIM関数を使いセル内の余分なスペースを削除して整えるといいかもしれません。

全角・半角の違い

全角文字と半角文字は別物と扱われます。特に全角スペースと半角スペースには注意です。

全角と半角の違い

セルの書式設定の表示形式でユーザ定義が設定されていた

下記の例では顧客リストのそれぞれのセルに「鈴木様」など敬称まで入力されているように見えますが、実はセルの書式設定のユーザー定義で「様」を表示させています。

ユーザー定義で表示形式が設定されている

このような場合、検索値を入力するC4に「鈴木」と入力すればエラーは出ませんが「鈴木様」と入れてしまうとエラーになります。

「円」や「個」などユーザー定義を使うことも多いですよね。

近似値検索(TRUE)なのに参照データが昇順に並んでない

検索の型がTRUEの場合、参照する検索範囲のリストの左端を昇順に並べる必要があります。FALSEの場合は昇順でなくても完全一致した値を探しますので大丈夫です。

近似値検索なのに参照データが昇順に並んでない

VLOOKUP関数の書式の4つ目が「TRUE」か「1」、または省略されている場合は全て近似値を検索しますので参照するリストは昇順に並べ替えて使います。

=VLOOKUP(D4,$G$4:$H$9,2,TRUE)
=VLOOKUP(D4,$G$4:$H$9,2,1)
=VLOOKUP(D4,$G$4:$H$9,2)

エラーが出るならまだマシですが、普通にデータが表示されていても間違った値が入っていることもありますので注意が必要です。

完全一致・近似値などの検索の型についての詳細は下記のURLをご覧ください。
【Excel】初心者向け VLOOKUP関数の使い方

参照先の表に、同じ検索値が複数ある

VLOOKUP関数の書式の4つ目が「FALSE」なら一番上の行の値が表示されてしまいます。 「TRUE」でも正しく動作しません。

検索範囲に重複する値がある場

検索値と検索範囲が両方とも文字列か数値のどちらか同じじゃなければならない

検索値と参照先の一番左の列は「文字列」か「数値」で統一しないと「#N/A」エラーが出てしまいます。

書式設定が同じでなければエラーになる

すでに入力されたセルは [ セルの書式設定 ] > [ 表示形式 ] から変更しただけでは変換できませんのでご注意ください。詳しくは下記の記事をご覧ください。
【Excel】文字列が数値にならない理由と一括で変換する方法

【Excel】数値から文字列に変換する方法

検索範囲がズレている!?

検索範囲を指定するときは絶対参照にしないとオートフィルでコピーをしたときに範囲がズレてしまいます。ズレた範囲に検索したい値がなければエラーになってしまいます。

検索範囲(参照先)は絶対参照にしないとコピーしたときにズレてしまう

検索範囲がズレているので検索値が見つからずエラーになることもある

例の場合だと列番号のG、Iと行番号の4、9の前に「$」を付け「$G$4:$I$9」にします。

=VLOOKUP(C4, $G$4:$I$9, 2, FALSE)

スポンサーリンク