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つの条件を結合したものにしたいと思います。
- B列の「日付」
- 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つの条件を含んでいましたね。（いずれも「台帳」シート）
- B列の「日付」
- 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関数_検索値①
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）
ファイルを開いたら次の手順を実施してください。
- 「台帳」シートを選択
- A2セルへ「=$B2&COUNTIF($B$2:$B2,$B2)」を入力
※赤字の部分をコピーして貼り付けてください。 - A2セルをコピー
- A3～A15へ貼り付け（ペースト）
- 「請求明細書」シートを選択
- C15セルへ「=VLOOKUP($C$9&$B15,台帳!$A:$F,3,0)」を入力
※赤字の部分をコピーして貼り付けてください。 - C15セルをコピー
- C16～C19へ貼り付け（ペースト）
- D15セルへ「=VLOOKUP($C$9&$B15,台帳!$A:$F,4,0)」を入力
※赤字の部分をコピーして貼り付けてください。 - D15セルをコピー
- D16～D19へ貼り付け（ペースト）
- E15セルへ「=VLOOKUP($C$9&$B15,台帳!$A:$F,5,0)」を入力
※赤字の部分をコピーして貼り付けてください。 - E15セルをコピー
- E16～E19へ貼り付け（ペースト）
上記手順を行った結果は「台帳 (関数あり)」シートと「請求明細書 (関数あり)」シートにありますので、このシートの内容と実際に操作した結果が同じになっていればOKです。
また、「請求明細書」シートのC19セルの日付を変更すると、C15～C19セルのVLOOKUP関数の戻り値（返り値）もきちんと変わることもしっかりと確認してみましょうね。
さいごに
VLOOKUP関数は制約というか仕様が明確に決まっているため、「用いるデータの方を何とかする」という姿勢で考えると状況を打破できることが多いですよ。
今回の複数条件でのVLOOKUP関数を用いる方法自体も、以外と実務では使えるシーンがありますので、知っておくと良いですね。
なお、その他にもVLOOKUP関数関連の実務で役立つテクニックについて、私の書籍で紹介しているので、こちらもご参考にしていただければと思います。
▼VLOOKUP関数を基本から応用まで体系的に学びたい方向け
▼VLOOKUP関数の応用テクニックに加えて、その他IF関数なども学びたい方向け
ご参考になれば幸いですm(_ _)m
森田 関数を使っていると、こういう「発想の転換」が必要なシーンは絶対に遭遇します。意外と関数を使う前のデータのまとめ方が重要なんですよね。
いろいろ試行錯誤していくと、ポイントを押さえた表組みができるようになりますよ。
こんには。ｖｌｏｏｋｕｐ複数で同一の検索値で複数のデータがある場合に任意のデータを検索する方法について
を、見たがままエクセルに同じように計算式を組み込んでやってみたのですが、うまく数字が反映されません。
↓ Ｃの日付欄に数式をいれております。
＝ＩＦＥＲＲＯＲ（VＬＯＯＫＵＰ（＄B325＆＄C＄３２２,＄B332：＄F＄348,ＭＡＴＣＨ（D＄332,＄B＄332：
＄F＄350,0）,0）””）
Ａ Ｂ Ｃ D Ｅ
日付 0
Ｎｏ 社員番号 氏名 事由
1
2
3
4
5
Ａ Ｂ Ｃ D Ｅ F
332 日付 社員番号 氏名 事由
333 142706 12月2日 111111 高橋二郎 遅刻
334 242706 12月2日 120011 井上明 欠勤
335 142707 12月3日 222211 高田信子 早退
336 142708 12月4日 135666 二位金八 欠勤
337 142709 12月5日 200135 花坂 享 欠勤
338 142710 12月6日 148222 森田弓 欠勤
339 242710 12月6日 165888 大阪花子 欠勤
340 342710 12月6日 200135 花坂 享 遅刻
341 442710 12月6日 200135 花坂 享 早退
342 142714 12月10日115613木田久美子 遅刻
343 142715 12月11日154864三木藤子 早退
344 142716 12月12日203251松本木久蔵 早退
345 142717 12月13日152623町田満 遅刻
346 242717 12月13日185483真中光雄 欠勤
347 142720 12月16日154648鳥取美智子 早退
348 242720 12月16日164948松沢伸晃 早退
takakoさん
コメントありがとうございます。
上記の数式をC列の日付に入れている、ということですが本記事のサンプルでは、数式ではなく日付を入力してVLOOKUP関数の検索値の一部にしています。
本記事ではVLOOKUP関数を入力しているのは、J7セルなのでそもそも数式を入力しているセルが異なるのでは、という点が気になりますね。
また、数式の中身についても、元データの表形式（行列のどこにどのデータが入っているか）がいまいち分からなかったので、それぞれの検索値が妥当なのかが判断つきません。。
ひとつ確実なのは、IFERROR関数の引数「エラーの場合の値」の””の前のカンマが抜けているのは誤りですね。
具体的な回答をするには、実際のデータを送ってもらえると回答しやすいです。
もし、ご希望の場合は、コンタクトフォーム経由でご連絡いただけると幸いですm(__)m
うまく表記できなくて、申し訳ありません。
今回は、ＩＦ関数とＶＬＯＯＫＵＰとＭＡＴＣＨ関数の複合系ですが
それぞれ、単体の関数の場合だとどうなるかも知りたいです。
宜しくおねがいします。
takakoさん
一応参考までに過去記事の中で単独で解説している記事のリンクを張っておきますね。
ご参考にしていただければ幸いですm(__)m
なお、MATCH関数は単独の記事がないので今度アップしますね。
現在これと同じ機能を配列数式で組んでいて、組んだ本人でさえ見たくもない数式になっています。
この記事方法ならシンプルで見やすく、同一データに唯一の値が付与されていることから色々と応用が効きそうです。
参考になりました。
よよぎさん
コメントありがとうございます。
ご参考になったということでとてもうれしいです(*^-^*)
配列数式は1つの数式で完結できるのは便利ですけど、自分でも後から見返したくないほど複雑になりがちですよね。
私も結構同じような経験ありますので十二分に分かります(;^_^A
作業セルで数式を分割すると1つ1つの数式がシンプルになるので色々と応用効いて便利です。
ぜひ、今後もご活用くださいませ(^^♪