VLOOKUP関数はコード表などから値を取得するのによく使いますが、また取得に失敗することもままあります。
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 | インド |
原因１：相対参照のため "範囲" がずれる
式を設定後にコピーやオートフィルを使用すると、本来指定したかった範囲とずれることがあります。
絶対参照や範囲の名前を使うことで防ぐことができます。
原因２：検索値に重複がある
"範囲" 内に "検索値" が２つ以上あると、上にある "検索値" に対応した値を取得します。
本来 "範囲" 内に "検索値" が２つ以上あるものは VLOOKUP関数に向きません。
原因３：大文字・小文字が違う
ここからは具体的に説明します。
2桁国コードから国名を取得します。
| A | B | |
|---|---|---|
| 15 | jp | = VLOOKUP ( A15, B2:E13, 4, FALSE ) |
ご想像の通り 'jp' ではエラーになります。
| A | B | |
|---|---|---|
| 15 | jp | #N/A |
正しい大文字の 'JP' にすることで取得できます。
| A | B | |
|---|---|---|
| 15 | JP | 日本 |
このケースでは見た目が明らかに違うので、すぐ原因が分かりますが、ひとつの可能性として覚えておいてください。
原因４：全角・半角が違う
同じく2桁国コードから国名を取得しますがエラーになりました。
| A | B | |
|---|---|---|
| 15 | ＤＥ | #N/A |
半角に変換してから検索するように式を変更します。
ASC関数は全角から半角へ変換します。
| A | B | |
|---|---|---|
| 15 | ＤＥ | = VLOOKUP ( ASC ( A15 ), B2:E13, 4, FALSE ) |
| A | B | |
|---|---|---|
| 15 | ＤＥ | ドイツ |
見た目が似ていて 検索(D) でひっかからないケースは、この可能性を疑ってください。
原因５：前後に空白がある
同じく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 | フランス |
原因６：データ型が違う
このケースも 検索(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'が文字列と認識されるケースもあります。
原因７：その他
列"位置" の指定が誤っている、そもそも式が文字列認識されている、なんてオチもあります。
以上がよくある VLOOKUP関数 の落とし穴です。
VLOOKUP関数 は思うとおりに動いてくれない関数の代表格ですが、この記事が原因追及に役立てば幸いです。
ご質問は下の 「コメントを書く」 からお願いします。
ExcelやVBA全般に関わる質問で、比較的簡単にお答えできるものはできる限り回答したいと思います。
回答を公開でなくメールでやり取りしたいという場合は、その旨記載していただければ非公開で回答することも可能です。
有償での作業依頼は非公開にしますので、条件等をお知らせください。
