Aさん VLOOKUP関数で「検索値」に指定したキーワードが「範囲」の中にあるのに、なぜかエラーになってしまいます。。
入力ミスではないかはチェックして間違いないはずなんですけど、どうしてですかね？？
森田 もしかすると、「表示形式」が一致していない可能性がありますね。「検索値」に指定したキーワードは数値や日付、時刻のみではないですか？
Aさん あっ、そうです！数値です。
そういえば、セルの左上に緑色の三角があったような。。
森田 セルの左上に緑色の三角があったということであれば、数値ですけど文字列扱いになっている可能性がありますね。
同じ値でも表示形式が異なるだけで別の値としてみなされてしまいますので、原因と解決方法について解説していきますね。
はじめに
本題に入る前に、この記事がおすすめな人を挙げてみます。
- データの転記・集約作業が多い人
- エクセルを使う頻度が高い人
- 事務職の人
ちなみに、最低限「VLOOKUP関数の基礎」を把握している想定での記事となります。
※自信がない方はこちらの記事で学んでください。
→【初心者向け】データ転記の作業時間を9割カット！VLOOKUP関数の使い方
確かに「検索値」があるはずなのにエラーになる？
たとえば、次のような状態を指します。
「検索用」シートのB3セルのVLOOKUP関数がエラーになっていますね。
このVLOOKUP関数の引数「検索値」は、同じシートのA3セルにある”1”です。
また、引数「範囲」は「仕入数（数値_日付）」シートのA～C列です。
この中の1番左のA列中には”1”がA2セルにあるのに、VLOOKUP関数がエラーになっているのは不思議ですね。
検索できない原因は「表示形式」
ずばり、原因は「表示形式」です。
気づいてほしいのは、数値や日付のセルの左上隅に緑色になっている部分です。
このマークは、「エラーインジケーター」といいます。
これは、周囲のデータと比べて不自然な場合や、エラー（もしくは、その可能性がある）の場合に表示される親切機能です。（時々おせっかいですが）
該当セルを選択していると、注意マークが表示されるので、▼マークをクリックすると、なぜエラーインジケーターが表示されているか確認および解決することが可能です。
今回のケースは「数値が文字列として保存されている」ので、数値データなのに文字列扱いされています。
一方のVLOOKUP関数の引数「範囲」に指定した「検索値」の検索範囲であるA列は数値のままでした。
よって、「1（文字列）」≠「1（数値）」となったためにVLOOKUP関数はエラーになったのです。
よくあるのが、何かしらのシステムからエクスポート（出力）したデータの数値や日付のデータが文字列化されており、そのデータをコピペした内容をVLOOKUP関数で参照しようとする場合に起こるケースです。
意外とシステム側でエクスポート後のデータの表示形式まで制御してくれていない場合があるので、注意しましょうね。
ちなみに、エラーインジケーターのメニューにある「数値に変換する」をクリックするだけで今回のケースは解決できます。（詳しい内容はこちらの記事をどうぞ→こちら）
しかし、セルの数が多い場合や、毎回文字列化された数値や日付をコピペするといった場合は、他の関数と組み合わせることで、処理を簡単にすることができますよ。
よくある3つのケースごとに具体的な組み合わせ方は以下のとおりです。
【CASE1】「検索値」は文字列、「範囲」は数値
1つ目のケースは、VLOOKUP関数の引数「検索値」が文字列、かつ引数「範囲」の1番左の列が数値の場合です。
先ほどまで解説していた原因の例といっしょですね。
こういった場合、「検索値」＝「範囲」になるようにすれば良いですが、変更を行なう対象は「検索値」の方です。
今回のケースであれば、この「検索値」へVALUE関数を組み合わせます。
VALUE関数とは？
このVALUE関数は文字列関数の一種で、任意の文字列となった数字を数値に変換してくれる関数です。
VALUE(文字列)
このVALUE関数を単体で使ってみるとイメージが湧きやすいと思います。
たとえば、次の「検索」シートのA3セルが文字列化しているため、VALUE関数を試してみましょう。
上記のとおり、D3セルへVALUE関数をセットし、引数「文字列」に文字列化したA3セルの値”1”を指定した結果、数値に変換された”1”が戻り値（返り値）となりました。
VALUE関数について、もっと詳しく知りたい方はこちらの記事もどうぞ→こちら
引数「検索値」にVALUE関数を組み合わせる！
このVALUE関数をVLOOKUP関数の引数「検索値」へ入れてあげましょう。
つまり、「検索値」が「$A3」だったところを「VALUE($A3)」にするだけでOKです。
VALUE関数自体は簡単な関数なので、この組み合わせは難しくないですね。
【CASE2】「検索値」は文字列、「範囲」は日付
2つ目のケースは、VLOOKUP関数の引数「検索値」が文字列、かつ引数「範囲」の1番左の列が日付の場合です。
ケース1の数値が日付に変わったものと思えばOKです。
考え方は先ほどと同じく、「検索値」の方に他の関数を組み合わせて「検索値」＝「範囲」にしましょう。
今回は「検索値」へDATEVALUE関数を組み合わせます。
DATEVALUE関数とは？
このDATEVALUE関数は日付関数の一種で、任意の文字列となった日付をシリアル値に変換してくれる関数です。
DATEVALUE(日付文字列)
ちなみに、シリアル値とは、1900年1月1日から数えて何日目の日付かを数値にしたものです。
このDATEVALUE関数も単体で使ってみるとイメージが湧きやすいと思います。
たとえば、次の「検索」シートのA6セルが文字列化しているため、DATEVALUE関数を試してみましょう。
上記のとおり、D6セルへDATEVALUE関数をセットし、引数「日付文字列」に文字列化したA6セルの値”2017/1/1”を指定した結果、シリアル値に変換された”42736”が戻り値（返り値）となりました。
この”42736”というシリアル値は、2017年1月1日という日付が、1900年1月1日から数えて42736日目だという意味になりますね。
D6セルの表示形式を日付にしてあげれば、A6セルと同じ表示に変更することもできるようになります。
引数「検索値」にDATEVALUE関数を組み合わせる！
このDATEVALUE関数をVLOOKUP関数の引数「検索値」へ入れてあげましょう。
つまり、「検索値」が「$A6」だったところを「DATEVALUE($A6)」にするだけでOKです。
ケース1とまったく同じ要領ですね。
【CASE3】「検索値」は数値、「範囲」は文字列
3つ目のケースは、VLOOKUP関数の引数「検索値」が数値、かつ引数「範囲」の1番左の列が文字列の場合です。
ケース1とは逆のパターンというわけですね。
今回の「検索値」＝「範囲」にするために、「検索値」へTEXT関数を組み合わせます。
TEXT関数とは？
このTEXT関数は文字列関数の一種で、任意の値を指定の表示形式の文字列に変換してくれる関数です。
TEXT(値,表示形式)
このTEXT関数も単体で使ってみるとイメージが湧きやすいと思います。
たとえば、次の「検索」シートのA11セルの数値が入っているため、TEXT関数を試してみましょう。
上記のとおり、D11セルへTEXT関数をセットし、引数「値」にA11セルの値”1”を指定しました。
TEXT関数の第2引数の「表示形式」には今と同じ数値のままで文字列化したいので、”0”を指定しました。
この結果、文字列化された数値”1”が戻り値（返り値）となりました。
念のため、本当に文字列化されたかも見てみましょう。
TEXT関数で変換前のA11セルとTEXT関数で変換後のD11セルをイコール[=]でつなぐ数式「=A11=D11」をE11セルに入れて判定します。
ご覧のとおり、E11セルには”FALSE”という結果が出ていますので、「A11=D11」は不一致であるということがわかります。（一致していた場合は”TRUE”になります）
TEXT関数は表示形式の概念を理解することがポイントになります。
もっと詳しく知りたい方はこちらの記事もどうぞ→こちら
引数「検索値」にTEXT関数を組み合わせる！
このTEXT関数をVLOOKUP関数の引数「検索値」へ入れてあげましょう。
つまり、「検索値」が「$A11」だったところを「TEXT($A11,"0")」にするだけでOKです。
ケース1・2と同じ要領ですね。
ちなみに、「検索値」は日付、「範囲」は文字列の場合はこのケース3と同じ対応方法で大丈夫です。
その場合は、TEXT関数の引数「表示形式」を日付のものを設定しましょうね。
サンプルファイルで練習しよう！
今回のケース1～3のエラーを実際に解決してみましょう。
可能であれば、以下のサンプルファイルをダウンロードして実際に操作してみてください。
サンプルファイル_VLOOKUP関数_検索値②
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）
ファイルを開いたら次の「実際に操作してみよう！」の①～④を実施してみてください。
実際に操作してみよう！① [CASE1]
- 「検索」シートを選択
- B3セルの検索値の「$A3」を「VALUE($A3)」へ修正
わからない場合は本記事の【CASE1】の説明部分をもう1度読み返してください。
実際に操作してみよう！② [CASE2]
- 「検索」シートを選択
- B6セルの検索値の「$A6」を「DATEVALUE($A6)」へ修正
わからない場合は本記事の【CASE2】の説明部分をもう1度読み返してください。
実際に操作してみよう！③ [CASE3]
- 「検索」シートを選択
- B11セルの検索値の「$A11」を「TEXT($A11,"0")」へ修正
わからない場合は本記事の【CASE3】の説明部分をもう1度読み返してください。
実際に操作してみよう！④ [おまけ]
- 「検索」シートを選択
- B14セルの検索値の「$A14」を「TEXT($A14,"yyyy/m/d")」へ修正
わからない場合は本記事の【CASE3】の説明部分をもう1度読み返すとともに、表示形式の部分はTEXT関数の記事か表示形式の記事をご覧ください。
どうしても分からない場合は
上記手順を行った結果は「検索 (関数あり)」シートにありますので、このシートの内容と実際に操作した結果が同じなっていればOKです。
【番外編】時刻の60進法（0：00）を「検索値」にする場合も注意！
先ほどまで説明した内容と少々原因が異なりますが、VLOOKUP関数の引数「検索値」に指定する際に注意が必要なものとして、時刻があります。
ここでいう時刻は60進法（0：00の形式）を指し、たとえば次のように、なぜかエラーが出る場合があります。
ご覧のとおり、引数「検索値」に設定したD3セルと、引数「範囲」の1番左のセルとなるA3セルは同じ”11:00:00”なのに、なぜかエラーになっていますね。
実は、この原因は以下のようにシリアル値（10進法）に直すとわかります。
上記のように、実はD3セルは”0.458333333333333”、A3セルは” 0.458333333333332”と、実は小数点第15位が異なる値であったというオチですね。
なお、普通に時刻を定数でセル上に入力している場合は、起こる可能性は低いですが、計算結果の値となる時刻や他システムからエクスポートした時刻を今回のようにVLOOKUP関数の「検索値」として使用すると起こる可能性があります。
まあ、時刻を「検索値」にする方はあんまりいないかもしれませんが、そういった可能性があるということだけ心に留めておいてください。
ちなみに、このシリアル値の時刻の考え方は、1日をシリアル値の”1”とみなすため、時間に直すと24時間ということになります。
つまり、1時間は「1（日）÷24（時間）」で計算した結果となる”0.041666666666667” （小数点第15位で四捨五入）となります。
今回の11時であれば、「1（日）÷24（時間）×11（時間）」で計算した結果が”0.458333333333333”になるわけですね。
さいごに
VLOOKUP関数を間違いなく設定したはずなのに、エラーになると本当に焦ります。
そして、何度も見直しても自分でエラー原因がわからないときのモヤモヤは何とも言えない気分になりますね。
VLOOKUP関数の基本的な数式や引数のルールに問題ない場合は、今回の事象が当てはまるケースがありますので、ぜひ実務で困った際は思い出してくださいね。
今回はVLOOKUP関数の「検索値」に特化した記事でしたが、それ意外のVLOOKUP関数の各引数に対する応用テクニックを私の書籍で紹介しているので、こちらもご参考にしていただければと思います。
▼VLOOKUP関数を基本から応用まで体系的に学びたい方向け
▼VLOOKUP関数の応用テクニックに加えて、その他IF関数なども学びたい方向け
ご参考になれば幸いですm(_ _)m
森田 本記事の内容は、私も実務中にVLOOKUP関数の設定が間違っていないはずなのに、ほんとうに理由がわからなくて困っていた経験を踏まえた内容です。
ネットで調べ、いろいろな可能性をひとつずつ潰しては別の手法を試し、ようやく腹落ちできたので記事にまとめることができました。
同じように困った経験がある方の参考になると嬉しいですね。
