【エクセル中級者向け】VLOOKUP関数で同一の「検索値」が複数存在する場合に2つ以上の条件で検索する方法

AさんAさん

VLOOKUP関数で検索したい表の中に同じ「検索値」の別データがあるのですが、2つ以上のキーワードを選択して自由に好きなデータを検索することはできますか??

森田森田

結論から言うと可能です!
ただ、VLOOKUP関数は1つのキーワードでしか検索できない仕様なので、次の2ステップが必要ですよ。
1つ目は、検索したい表に対して一意の「検索値」を準備すること。2つ目は、VLOOKUP関数の引数「検索値」を2つ以上のキーワードを結合して、1つ目で用意した「検索値」と同じ値にすること、以上の2点ですね。
それでは、詳細をわかりやすく解説していきますね。

はじめに

本題に入る前に、この記事がおすすめな人を挙げてみます。

  • データの転記・集約作業が多い人
  • エクセルを使う頻度が高い人
  • 事務職の人

ちなみに、最低限「VLOOKUP関数の基礎」を把握している想定での記事となります。
※自信がない方はこちらの記事で学んでください。
【初心者向け】データ転記の作業時間を9割カット!VLOOKUP関数の使い方

VLOOKUP関数で同一の「検索値」が複数存在する場合とは?

よくあるのは、取引などの出来事を記録している表ですね。

たとえば、次のような取引の記録をまとめた「台帳」というシートがあるとします。

この中で一意(他データと重複していない)のデータはありませんね。

B列の「日付」もC列の「商品名」も2つ以上の重複したデータばかりです。

この表を元に、VLOOKUP関数で次の「請求明細書」シートへ任意の「日付」に該当するデータをすべて明細部分(黄色の塗りつぶし範囲)へ転記したいとします。

VLOOKUP関数の仕様上、1つのキーワードしか引数「検索値」に指定できません。

よって、VLOOKUP関数を普通に使うと、たとえば”2017/1/7”を「検索値」にする場合は、2行目のデータしか検索・転記できませんね。

そこで、解決策として次の2ステップを実行すれば良いです。

【STEP1】検索する表に一意の「検索値」を新たにつくる!

基本的な考え方として、そもそもの検索対象のデータの方をVLOOKUP関数の仕様に合わせてあげれば良いのです。

つまり、1つのキーワードしか引数「検索値」に指定できないので、一意の「検索値」を検索する表の中(今回は「台帳」シート)につくってしまえば良いということですね。

では、今回の「一意の検索値」はどうするかというと、以下の2つの条件を結合したものにしたいと思います。

  1. B列の「日付」
  2. 1の日付の中の通し番号(その日付の中で何番目のデータか)

この1と2を結合した「検索値」を「台帳」シートのA列へ追加します。

なぜ、A列かというと、VLOOKUP関数の仕様上、引数「範囲」の1番左の列が「検索値」を探す列になるためです。

この上記の1に指定していた「B列の日付」は同じ行のB列のセルを参照させれば良いですが、2の「1の日付の中の通し番号」はCOUNTIF関数を使用します。

COUNTIF関数とは?

COUNTIF関数は統計関数の一種で、検索条件に指定したセルの個数をカウントする関数です。

COUNTIF(範囲,検索条件)

イメージが湧くように、COUNTIF関数を単独で使ってみましょう。

たとえば、次の表のB列中に”2017/1/7”が何セルあるかをカウントしてみます。

B列中に”2017/1/7”が5セルあったので、COUNTIF関数の戻り値(返り値)は”5”になりました。

では、続いて今回の「1の日付の中の通し番号」をCOUNTIF関数単体で計算してみると以下のようになります。

「COUNTIF($B$2:$B2,$B2)」のように、引数「範囲」の起点となるセルは行列ともに絶対参照、終点となるセルは行を相対参照(列は絶対・相対のどちらでも可)にすればOKです。

これで、各日付がそれぞれ上から何番目のものかがわかりますね。

なお、COUNTIF関数の参照形式を工夫したのは、A3セル以降へ数式をコピペした際に、COUNTIF関数の引数「範囲」が下へ行くほど広がるようにしないと通し番号にならないためです。

これが、すべての数式でCOUNTIF関数の「範囲」をB列すべてにしていると、同じ日付の場合は当然ですがCOUNTIF関数の結果が同じになってしまいますからね。(日付が”2017/1/7”であれば、すべてのセルで同じ”5”が返る)

ちなみに、「範囲」が広がるイメージがわかない人は以下を見てくださいね。

この参照形式についてもっと詳細を知りたい人はこちらの記事もどうぞ→こちら

「一意の検索値」をつくる!

では、本題に戻ります。最初にA2セルから「一意の検索値」を入力していきましょう。

まず、上記の1に指定していた「B列の日付」は同じ行のB列のセルを参照させれば良いので、「=$B2」と数式を入力します。

次に、2の「1の日付の中の通し番号」と結合するため、アンパサンド[&]を入力するので、数式は「=$B2&」となります。

最後に、2の「1の日付の中の通し番号」に対しては、先ほどCOUNTIF関数単体で求めたとおり、「COUNTIF($B$2:$B2,$B2)」になりますので、アンパサンド[&]の後に入力しましょう。

結果、A2セルの数式は最終的に「=$B2&COUNTIF($B$2:$B2,$B2)」となりますね。

このA2セルの数式をA3セル以降にコピペすればSTEP1については完了です。

【STEP2】引数「検索値」は2つのセルを結合する!

続いて、VLOOKUP関数をセットする「請求明細書」シートに移ります。

こちらは最初にC15セルにVLOOKUP関数を入力しますが、注意すべきは引数「検索値」です。(他の「範囲」「列番号」「検索方法」は普通でOK)

この引数「検索値」は、STEP1にて用意した「台帳」シートの「一意の検索値」を指定することがポイントです。

「一意の検索値」は以下の2つの条件を含んでいましたね。(いずれも「台帳」シート)

  1. B列の「日付」
  2. 1の日付の中の通し番号(その日付の中で何番目のデータか)

引数「検索値」では、1は「請求明細書」シートのC9セルを指定します。C9セルには日付が入力されています。

そして、2の部分は「請求明細書」シートのB15~B19セルへ1~5の連番がそれぞれ入力されていますので、こちらを参照します。

C15セルのVLOOKUP関数であれば、同じ行のB15セルを参照すればOKです。

この1と2をアンパサンド[&]で結合すれば完了です。

つまり、C15セルであれば、VLOOKUP関数の引数「検索値」は「$C$9&$B15」となりますね。

数式全体では「=VLOOKUP($C$9&$B15,台帳!$A:$F,3,0)」となります。

あとは、このC15セルの数式をC15~E19セルへコピペすればOKです。

D・E列は引数「列番号」は変更しなければならないので、その点はご注意ください。

自動的に変更させたい場合は、引数「列番号」へMATCH関数などと組み合わせると良いですよ。

【中級者向け】VLOOKUP関数の引数「列番号」を自動で変更するテクニック②【MATCH関数】 | Excelを制する者は人生を制す ~No Excel No Life~

サンプルファイルで練習しよう!

では、今まで解説した部分について、実際にサンプルファイルをとおして練習してみましょう!

サンプルの条件

今回の題材は次のとおりです。

「商品」というシートに商品(くだものの名称)・単価が一覧表になっています。

この「単価」をD列の2行目以降に転記させた「台帳」シートがあります。

この「台帳」シートのC~E列の値を「請求明細書」シートのC15~E19セルへVLOOKUP関数で転記しましょう。

なお、キーワードは「台帳」シートのB列の「日付」と、その日付の通し番号を結合すれば良いのでしたね。

実際に操作しよう!

可能であれば、以下のサンプルファイルをダウンロードして実際に操作してみてください。

サンプルファイル_VLOOKUP関数_検索値①
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
(上記リンクから登録フォームへ遷移します)

ファイルを開いたら次の手順を実施してください。

  1. 「台帳」シートを選択
  2. A2セルへ「=$B2&COUNTIF($B$2:$B2,$B2)」を入力
    ※赤字の部分をコピーして貼り付けてください。
  3. A2セルをコピー
  4. A3~A15へ貼り付け(ペースト)
  5. 「請求明細書」シートを選択
  6. C15セルへ「=VLOOKUP($C$9&$B15,台帳!$A:$F,3,0)」を入力
    ※赤字の部分をコピーして貼り付けてください。
  7. C15セルをコピー
  8. C16~C19へ貼り付け(ペースト)
  9. D15セルへ「=VLOOKUP($C$9&$B15,台帳!$A:$F,4,0)」を入力
    ※赤字の部分をコピーして貼り付けてください。
  10. D15セルをコピー
  11. D16~D19へ貼り付け(ペースト)
  12. E15セルへ「=VLOOKUP($C$9&$B15,台帳!$A:$F,5,0)」を入力
    ※赤字の部分をコピーして貼り付けてください。
  13. E15セルをコピー
  14. E16~E19へ貼り付け(ペースト)

上記手順を行った結果は「台帳 (関数あり)」シートと「請求明細書 (関数あり)」シートにありますので、このシートの内容と実際に操作した結果が同じになっていればOKです。

また、「請求明細書」シートのC19セルの日付を変更すると、C15~C19セルのVLOOKUP関数の戻り値(返り値)もきちんと変わることもしっかりと確認してみましょうね。

さいごに

VLOOKUP関数は制約というか仕様が明確に決まっているため、「用いるデータの方を何とかする」という姿勢で考えると状況を打破できることが多いですよ。

今回の複数条件でのVLOOKUP関数を用いる方法自体も、以外と実務では使えるシーンがありますので、知っておくと良いですね。

なお、その他にもVLOOKUP関数関連の実務で役立つテクニックについて、私の書籍で紹介しているので、こちらもご参考にしていただければと思います。

▼VLOOKUP関数を基本から応用まで体系的に学びたい方向け

▼VLOOKUP関数の応用テクニックに加えて、その他IF関数なども学びたい方向け

ご参考になれば幸いですm(_ _)m

森田森田

関数を使っていると、こういう「発想の転換」が必要なシーンは絶対に遭遇します。意外と関数を使う前のデータのまとめ方が重要なんですよね。
いろいろ試行錯誤していくと、ポイントを押さえた表組みができるようになりますよ。