VLOOKUP関数が思うように使えない場合

VLOOKUP関数は他の関数と少し違うかも・・

VLOOKUP関数は、Excelでは良く使われる機能の1つだと思いますが、SUMやIFなどと比べると少し敷居が高いかもしれません。
SUMやIFでは基本的には「1つのセル」を(複数)扱うのに対して、VLOOKUP関数の場合は立体的な関数(配列数式的)となり、表全体が対象範囲となるためです。とはいえ、悩むほど大げさなものではなく、使っていけば慣れてきます。
本ページでは問い合わせの多いVLOOKUPのエラーについて考えてみたいと思います。
なお、VLOOKUPの効果や使い方(関数挿入の仕方)などはヘルプなどでご確認ください。

注意！　エラーでないエラー！？

使い慣れてない方がVLOOKUP関数を使うと、エラーでないのにエラーと判断する場合があります(ただ、厳密にいえばエラーなのですが、ここでは他のエラーと意味合いが違うために、このように表現している点でもあるので注意してください)。
下記の画像は「エラーがない」場合です。赤枠の部分に「3」と入力すると、左の表からそれに該当する「スイカ」が赤枠下の「商品名」に表示されています。
VLOOKUPエラーなし

一方、下記は赤枠の部分に「10」と左の表に存在しない番号を入力したところです。 VLOOKUPエラーあり
予想通りエラー「#N/A」が表示されました。しかしこれはエラーであることはエラーですが、VLOOKUPの式自体は間違っていません(強いて言えば「10」と存在しない番号を入力したことが間違いです)。ここが気を付けるべきポイントの1つです。
言い換えると、「あ～、エラーじゃん、式間違えてのかよー」等と思ってしまうと解決の糸口が遠くなってしまうので注意が必要です。

決してVLOOKUPに限った話ではないですが、この点についてはエラーの認識を正しく伝えるように(特に誰かに電話やメールで聞くときに)注意が必要です。通常は「関数・数式でエラー」というと、式の設定が間違っている、データがおかしいなどというところに問題の原因をフォーカスすると思われます。

良く見かけるVLOOKUPのエラー

弊社で良く見かけるエラーの原因は、表の選択が(気づかぬうちに)ずれている場合とVLOOKUPの式の設定が間違っていることがほとんどです。前者は「エラーでないエラー」の類です。
通常、VLOOKUPが登場するような表は、上記の画像のように、対象となる表とVLOOKUP関数を使うセルが、1つのシートにまとまっていることはなく、シートをまたがる形(シート2つ)で使われていることが多く、データ数も数千単位になると複雑に思えてしまい、原因を探すのに時間がかかったりします。

下記でも説明の便宜上、2つの表を1つのシートにまとめたものにしていますが実際には2シート分割するようなイメージで見るとベターかもしれません。

表の範囲を確認する

相対参照と絶対参照が分かると、この手の間違いは減り、更に理解もしていれば納得しながら修正ができると思います。
いずれにしても、VLOOKUPで参照となる表を選択するときの注意事項です。このエラーの特徴は上記で説明した画像のような1点(セル1個)だけにVLOOKUPの結果を表示すると言う場合でなく、式をコピーして複数のセルに表示させたい(VLOOKUPを複数使う)ときに発生します。
VLOOKUPで売上表を作る
上記の場合、右側の「売上表」の「商品名」が空白になっているので、ここにVLOOKUP関数を使って、左側の「商品一覧表」から商品名を抽出してみたいと思います。
下図のような流れです。
VLOOKUPで枠線の中をそれぞれチェックする

更に下記の画像を見てください。1つのセルだけVLOOKUP関数を入れたところで「カレー」が表示されていますが、その式の中を除くと表(の範囲)を選択している箇所があります。 VLOOKUPで表の範囲を指定

この時点で気づいた方もいらっしゃると思いますが、この式を下記のようにコピーしていきます。
VLOOKUPの式をコピー

すると下記のようになりました。
VLOOKUPで式をコピーした結果のセル
なぜか一番下の商品名だけエラー(商品が見つからないエラー)になっています(本当は「サラダ」が表示されるはず)。
先ほどのようにこのセルの(VLOOKUP関数の式の)内容を見れば、その原因が分かります。
VLOOKUPで式コピーで範囲がずれる
E7のセルは、本来対象となる表の範囲からずれていることが原因です。
なお、E3のカレーと表示されたセル以外もずれていますが、表の対象範囲がかろうじてかぶっているために結果が正しく表示されていますが、これも問題を見難くさせる要因になるので注意が必要です。
このように数式をオートフィル等でコピーしてもくずれないように適宜絶対参照にしておく必要があります。
表の参照範囲を絶対参照にする

もっともこの方法も、参照元の範囲が以下のようになると、太刀打ちできなくなるため、実際に参照を入れるときには、今後、表自体の大きさ(データ数)が変わり得るのか等も含めて考えておく必要があります。
表の参照範囲を参照するときの注意

参照元のデータの数が増えても意識しない方法として簡易で対応するには以下の方法でできます。
表の参照範囲を参照するとき列全体を選択
このように表というよりも、列全体を選択すれば何行あってもVLOOKUPの対象範囲となるため表の範囲を意識する必要がなくなります。ただし、1行目、2行目も対象範囲に含まれるので、VLOOKUPの検索にヒットしそうなら文字を削除するなどして気を付ける必要があります。

表示形式について

検索したい項目の値も注意する必要があります。ここでは分かりやす入れとして日付を挙げていますが、その他「セルの書式設定」で見た目の値と実際の値が違う場合に注意が必要です。
Vlookupのキーになる項目を年月にする
上記の例では正しく「りんご」と値が拾えていますが、下記の場合だとエラーになりますが、何が違うか分かるでしょうか。
Vlookupのキーになる項目を年月してもうまくいかない
ここでVLOOKUP関数の対象となっている年月の書式を変えてみます。
書式設定で表示方法を変えてみる
書式設定で年月日に変更する
このようにすると原因が分かります。これでVLOOKUPは検索される側の値と検索する値の書式も見るということが分かります。
この手の影響を受けるかもしれないのは電話番号や郵便番号だったりします。書式設定でハイフンを自動で「見た目」として表示する場合などです。
VLOOKUP関数が思うようにいかない場合にこの点もチェックしてみるといいと思います。

VLOOKUPの仕様について

これもVLOOKUP関数に限ったことではないですが、関数には仕様というルールがあります。この仕様から外れた設定をしたときに「エラーですよ」とセルに表示して教えてくれる場合もあれば、残念ながら教えてくれない場合もあります。
やっかいなのは教えてくれない場合です。関数からみれば、「仕様に基づいた結果」としてセルに表示するだけですが、作業者から見ると予期しない結果だったりするので注意が必要になります。
このような点に関心ある方はvlookup関数の落とし穴に気をつけるもご覧ください。