VLOOKUP関数で同一の検索値で複数のデータがある場合に任意のデータを検索する方法

便利なVLOOKUP関数ですが、使い方を工夫しないと困ることとして、同じ検索値が複数ある場合です。

なぜならVLOOKUP関数は参照範囲内に同じ検索値が複数ある場合、一番上のものしか引用できないためです。

場合によって、参照範囲内の上から2番目、3番目のものを引用したい場合に使えるテクニックについて今回は解説してみたいと思います(・∀・)

同じ検索値が複数ある場合に任意のデータを検索する方法のサンプル

今回は下図のような日次で欠勤・遅刻・早退が発生した際に記録している表があるとします。

VLOOKUP関数を検索値が複数ある場合の検索方法①

次に、そのデータを元に、日付ごとに誰が欠勤・遅刻・早退していたかを検索したいとします。

この場合のVLOOKUP関数の検索値は下図の通り「日付」です。

VLOOKUP関数を検索値が複数ある場合の検索方法②

ここで困る点としては、検索値となる日付は必ずしも1つとは限らないということ。

しかし、VLOOKUP関数は参照範囲の上から検索をはじめて、一番最初にHITしたものしか引用できません。

では、どうするか。
結論からいうと、複数の同一の検索値をそれぞれ唯一のものにしてあげればいいのです。

下図の通り、A列に新しい検索値(唯一のもの)を入力するための作業列を追加します。

VLOOKUP関数を検索値が複数ある場合の検索方法③

そして、新しい検索値は「日付の個数+日付(シリアル値)」とします。

「+」の部分は数式で[&]で表現します。

日付の個数はCOUNIF関数の参照の仕方を工夫して、起点は絶対参照、終点は相対参照にしています。($B$5:$B5のところ)
そうすることで、行が下に行くほどCOUNTIF関数の参照範囲は下に広くなっていきます。

続いて、実際にVLOOKUP関数を入力します。
まず、サンプルでいうと、J7セルにVLOOKUP関数を入力します。

VLOOKUP関数の検索値として、No.部分のI7セルと日付部分のK4セルを[&]で結合します。

No.部分が先ほどA列に追加した作業列の日付の個数になります。

そうすることで、先ほどA列に追加した作業列を検索値とすることができるようになります。

I7セルは縦にスライドするので列のみ絶対参照、K4セルはスライドしないのですべて絶対参照です。

そして、参照範囲は作業列で追加したA列を含めるのを忘れずに行います。
サンプルでは、A5:E20が対象となります。

VLOOKUP関数を検索値が複数ある場合の検索方法④

列番号のところはJ7セルをもとにオートフィルでL11セルまでコピペしたいので、MATCH関数を用いて可変にします。

VLOOKPU関数とMATCH関数を組み合わせる方法は下記記事参照。

VLOOKUP関数の「列番号」を可変にするテクニックその2【MATCH関数と組み合わせる】 | Excelを制する者は人生を制す ~No Excel No Life~

最後に検索方法は絶対参照なので「0」を入力してVLOOKUP関数の記述は終了です。
忘れずにオートフィルでL11セルまでコピペします。

なお、今回は日付欄に入力がない状態、あるいは該当の日付のデータが5個未満の場合にエラー値が返らないようにIFERROR関数を設定しています。

IFERROR関数は、エラーの場合に返す値を設定できる関数です。
【関数の構成】IFERROR(値,エラーの場合の値)

まとめ

自分がやりたいことと、関数のできることをうまくマッチングさせることが関数マスターの近道です。

慣れてくると、こういった工夫を考えることがゲームみたいで楽しくなりますよ(・∀・)

どうしても自分だけでは分からない場合は、周囲のエクセルに詳しい人に聞いたり、ググったりしましょう。

ちなみに私にお問合せいただいても良いですよ。

この記事が気に入ったら
いいね ! しよう