低所得の青色申告個人事業主のブログ

開業から経験で得た情報をお知らせしていきたいと思います。

= グローバル ナビゲーション =

【Excel】【困った】VLOOKUP関数がひっかけてくれない7つの原因


[ スポンサー リンク ]

VLOOKUP関数はコード表などから値を取得するのによく使いますが、また取得に失敗することもままあります。

VLOOKUP関数にはいくつか落とし穴があります。

原因のいくつかを説明します。

 

VLOOKUP関数

関数

= VLOOKUP ( 検索値, 範囲, 列位置, 検索の型 )

 "検索の型" に 'FALSE' を指定したときは完全一致で検索します。

( 'TRUE' を指定したときは近似値で検索します。)

ここでは、"検索の型" はすべて 'FALSE' を指定したケースを説明します。

説明では以下の表を前提とします。

  A B C D E
1 国番号 2桁 3桁 数字 国名
2 1 US USA 840 アメリカ合衆国
3 7 RU RUS 643 ロシア
4 33 FR FRA 250 フランス
5 34 ES ESP 724 スペイン
6 64 GB GBR 826 イギリス
7 49 DE DEU 276 ドイツ
8 55 BR BRA 76 ブラジル
9 61 AU AUS 36 オーストラリア
10 65 SG SGA 702 シンガポール
11 81 JP JPN 392 日本
12 86 CN CHN 156 中華人民共和国
13 91 IN IND 356 インド

 

 

原因1:相対参照のため "範囲" がずれる

式を設定後にコピーやオートフィルを使用すると、本来指定したかった範囲とずれることがあります。

絶対参照や範囲の名前を使うことで防ぐことができます。

 

原因2:検索値に重複がある

 "範囲" 内に "検索値" が2つ以上あると、上にある "検索値" に対応した値を取得します。

本来 "範囲" 内に "検索値" が2つ以上あるものは VLOOKUP関数に向きません。

 

原因3:大文字・小文字が違う

ここからは具体的に説明します。

2桁国コードから国名を取得します。

  A B
15 jp = VLOOKUP ( A15, B2:E13, 4, FALSE )

ご想像の通り 'jp' ではエラーになります。

  A B
15 jp #N/A

正しい大文字の 'JP' にすることで取得できます。

  A B
15 JP 日本

このケースでは見た目が明らかに違うので、すぐ原因が分かりますが、ひとつの可能性として覚えておいてください。

 

原因4:全角・半角が違う

同じく2桁国コードから国名を取得しますがエラーになりました。

  A B
15 DE #N/A

半角に変換してから検索するように式を変更します。

ASC関数は全角から半角へ変換します。

  A B
15 DE = VLOOKUP ( ASC ( A15 ), B2:E13, 4, FALSE )
  A B
15 DE ドイツ

見た目が似ていて 検索(D) でひっかからないケースは、この可能性を疑ってください。

 

原因5:前後に空白がある

同じく2桁国コードから国名を取得しますがエラーになりました。

  A B
15 FR  #N/A

このケースは 検索(D) でひっかかるのでやっかいです。

見た目ではわかりませんんが 'FR_' と後ろに半角スペースが入っています。

このため 'TRIM関数' で前後の空白を取り除くとひっかかります。

  A B
15 FR  = VLOOKUP ( TRIM ( A15 ), B2:E13, 4, FALSE )
  A B
15 FR  フランス

 

原因6:データ型が違う

このケースも 検索(D) でひっかかるパターンです。

今回は国番号から国名を取得します。

  A B
15 7 = VLOOKUP ( A15, A2:E13, 5, FALSE )
  A B
15 7 #N/A

Excelで見ると見た目が違うのですが、実は '7' は左寄せ表示されます。

実際の A15 セルの中身は以下のとおり、先頭にアポストロフィがあります。

  A B
15 '7 #N/A

このため、検索値 '7' は文字列であり、数値 '7' とは異なると判定されてひっかからないのです。

このためVALUE関数で数値変換するとひっかかります。

  A B
15 7 = VLOOKUP ( VALUE ( A15 ), A2:E13, 5, FALSE )
  A B
15 7 ロシア

このため、検索値 '7' は文字列であり、数値 '7' とは異なると判定されてひっかからないのです。

このケースは一番やっかいです。

日付と文字列、'TRUE'と'FALSE'が文字列と認識されるケースもあります。

 

原因7:その他

列"位置" の指定が誤っている、そもそも式が文字列認識されている、なんてオチもあります。

 


以上がよくある VLOOKUP関数 の落とし穴です。

 VLOOKUP関数 は思うとおりに動いてくれない関数の代表格ですが、この記事が原因追及に役立てば幸いです。

 

ご質問は下の 「コメントを書く」 からお願いします。

ExcelVBA全般に関わる質問で、比較的簡単にお答えできるものはできる限り回答したいと思います。

回答を公開でなくメールでやり取りしたいという場合は、その旨記載していただければ非公開で回答することも可能です。

有償での作業依頼は非公開にしますので、条件等をお知らせください。