こんにちは。
田中です。

本日は久々のExcel VLOOKUPがテーマです。

ExcelのVLOOKUP関数は良く使う関数の1つではないでしょうか。
実際に「Excel VLOOKUP」と検索した場合の月間の検索ボリューム（どのぐらい検索されているのか）をみてみますと

月間平均15,000程検索ボリュームがあり、

非常に多く検索されています。
※2019年7月25日 Google のキーワードプランナーにて参照

それだけ「使い方がわからない」や「エラーの対処法」などで調べる人が多いのではないかと思います。
VLOOKUPを使っていて、データはあるのに＃N/Aになってしまう人は読み進めてみてください。

 

VLOOKUP関数のおさらい

VLOOKUPで必要な情報は下記4つの情報になります。

VLOOKUP(検索値,検索範囲,列番号,検索方法）

検索値　：検索範囲内先頭列で検索するデータを指定します。
検索範囲：目的のデータが含まれる文字列や数値または、論理値のテーブルを指定します。
列番号　：検索範囲の列番号を指定し、ここで指定された列で検索値と一致した値が返されます。

知りたい情報を検索範囲内にあるか調べ、列番号で指定した情報の値を入手する
という感じです。

今回のブログ、「VLOOKUPの落とし穴」ですが、
陥りやすい人の特徴があります。

• VLOOKUPが使えるようになってきた人。
• 久しぶりにVLOOKUPを使う人

です。

それではVLOOKUPに関する問題を出してみたいと思います。

 

VLOOKUP検索範囲の先頭列（1列目）を見る

今回、下図のような表があったと仮定し、商品の型番（検索値）の仕入れを行った担当者を割り出したいと思います。

VLOOKUP関数で求めます。

担当者は誰でしょうか？

正解は…

＃N/A（検索範囲内にデータが見つからない）

「式は合っているはずなのに！」と思うかもしれませんが、
残念ながら式は合っていません！

確かに、検索範囲内に「A-0001」は存在するのですが、

VLOOKUPの仕様上

指定された範囲の先頭列（1列目）で特定の値（検索値）を検索する

という事になっています。

先程の図で例でみますと、「仕入れ日」が指定された範囲の先頭列にあたります。

検索値「A-0001」が仕入れ日の列には存在していないため、＃N/Aが表示されていました。

 

データはあるのに＃N/Aになってしまう対処法

検索値は検索範囲で指定した先頭列しか見てくれません。
今回の場合ですと「仕入れ日」が検索の先頭列にありますので、この先頭列を検索値の型番になるようにします。

方法としては2つあります。

 

方法①：仕入れ日と型番の列を入れ替える

仕入れ日と型番の列を入れ替えます。
VLOOKUPは検索範囲の1列目に検索値が来るようにしなければなりませんので、
検索値がある型番を先頭列に持ってきます。

このとおり、検索値の担当者が正しく表示されるようになりました。

 

方法②：検索範囲をずらす

下図のように検索範囲選択時に型番が先頭列になるようにします

担当者が表示されるようになります。

 

上記の方法でめんどくさいと感じる方はINDEXとMATCH関数はおすすめです。

VLOOKUPの場合、検索値は検索範囲の1列目に検索する値がなければいけません。
しかし、今回のINDEXとMATCH関数は特に1列目である必要はありません。

INDEXはINDEX(配列,行番号,列番号)で指定したセルを表示してくれます。
MATCH関数は検索値が指定した範囲内の中に上から数えて何番目にあるか数えます。

下図のようにINDEX、MATCH関数であれば、日付のデータがある列を変更せずに、
探したいデータを見つけることができます。

一見、INDEXとMATCH関数を組み合わせた式は難しそうに感じますが、INDEXで指定している配列はVlookupの「検索範囲」と同じです。
列番号も同じです。
行番号ですが、VLOOKUPは検索範囲の先頭から検索値を探しますが、
Match関数では列を指定して探す事ができます。

ここがINDEX関数とMATCH関数の組み合わせの強いところです。

 

INDEXとMATCH関数についてより詳しく知りたい方は下記リンクでも記載していますので、
よろしければご覧くださいませ。

 

おわりに

VLOOKUPは指定した範囲内で検索する事ができると思われがちですが、
VLOOKUPで指定した検索値を検索することができるのは、検索範囲の先頭列だけです。

データがあるのになぜ#N/Aになってしまうのか。

このブログが役立ちましたら幸いです。