目次
VLOOKUP関数の落とし穴
ExcelにおいてVlookup関数は仕事上で最もよく使われる関数と言ってもいいかもしれません。
特定のセルの値を基にマスタデータから検索した値を返す関数です。
しかしVlookupの弱点として、「検索で見つけた最初のデータのみ返すことができ、2番目以降のデータは無視される」というものがあります。
このため重複データがある場合に全ての値を返すためには工夫をする必要があります。
VLOOKUP関数の使用例
今回はこちらのデータを使います。
メーカーで使われるような部品の一覧です。
ここで「品番」を入力したら「棚番」が自動生成される部品リストを作るものとします。
このとき便利なのがVlookup関数です。
次のような部品リストを作ります。
Vlookup関数の引数は次のようになります。
品番を検索値に、棚番マスタから品番および棚番を範囲選択し、2番目の列にある棚番のデータを完全一致（FALSE）で取り出します。
そして関数をリストにある全ての部品に対して反映させます。
これで部品リストができました。
重複データに対するVLOOKUP関数の対応
完成したはずの部品リストですが、ここでマスタデータをもう一度見てみましょう。
実はマスタデータには、同じ品番の部品が紛れ込んでいるのです。
3行目にあるB000-0050-001のボルト、および8行目にあるV008-0009-001のブラケットは2つの棚に置かれていることがわかります。
つまり、部品リストをもとに部品を探す場合、本来この2か所を探す必要があるわけですが、先ほど作成したリストでは1種類の棚番しか表示されていません。
「検索で見つけた最初のデータのみ返すことができ、2番目以降のデータは無視される」
これがVLOOKUP関数の弱点です。
たとえばこの関数を横にフィルさせても、関数内の引数を変えてもこの問題を解決することはできないのです。
重複データをすべて検索する方法
Vlookup関数を利用して重複データをすべて表示させるには工夫が必要です。
ここでは棚番マスタに少しだけ手を加えることで解決を目指します。
棚番リストの編集
まずは棚番マスタに重複データがいくつあるかを求め、それを基に検索用の文字列を作成します。
ここで重複データの検索にはCOUNTIF関数を用います。
COUNTIFの引数に絶対参照と相対参照を使い分けることにより、重複データが現れた場合にそれが何番目なのかを返します。
9行目と12行目のデータが重複しており、2番目のデータであることがわかります。
ここでVlookupを用いた重複検索用の文字列を作ります。
CONCATENATE関数を用いることで「何番目のデータか」と「品番」をひとつのセルにまとめてしまいます。
これで棚番マスタの準備ができました。
部品リストの編集
続いて部品リストを編集します。
2番目以降の棚番を表示させる列を作成します。
ここで入力するVlookup関数には次のように入力します。
=VLOOKUP(CONCATENATE(2,B3),棚番マスタ!$D$3:$E$12,2,FALSE)
検索値はCONCATENATE関数で”2+品番”を合体させたものです。
もし3番目以降の重複データがあるならこの数字を3以降に変更すればOKです。
重複していないデータの除外
このままフィルを使うと、重複していないデータでエラーになります。
そこでIFERROR関数を使って重複していない場合はなにも表示されないようにします。
=IFERROR(VLOOKUP(CONCATENATE(2,B3),棚番マスタ!$D$3:$E$12,2,FALSE),"")
これで部品リストは完成です。
重複データがある中でも棚番が表示されるようになりました。
Excelの関数は便利ですが、当然手の届かない部分や使いにくい部分も出てきます。
そうした中で如何に工夫をして乗り越えるかが業務効率化の鍵となります。
まとめ
VLOOKUP関数は検索で見つけた最初のデータのみ返すことができ、2番目以降のデータは無視される
マスタデータの編集をすることでVLOOKUP関数で重複データを取り出せる
重複数の表示にはCOUNTIF関数が有効
本サイトでは誤記があってもVlookupで参照する方法や複数条件で検索する方法なども紹介しています。
