Excel(エクセル）のVLOOKUP関数(ブイルックアップ)は、作業を効率化出来る代表的な関数です。
表を縦方向に検索して、検索値を抽出することが出来ますが、検索値が一つだけだと抽出データが重複してしまう場合がありませんか？
今回は、VLOOKUP関数の使い方を復習しつつ、『複数の条件』を検索値にすることで見つけたい値を正確に抽出することが出来るVLOOKUP関数の『複数条件』の設定方法を解説します。
最後の見出しでは、VLOOKUP関数を使わず、『INDEX関数とMATCH関数を使った検索方法』も紹介しますので、VLOOKUP関数の利用がどうしても苦手だという方は、ぜひ参考にしてみて下さい。
VLOOKUP関数とは(おさらい)
まずはエクセルのVLOOKUP関数の使い方をおさらいしましょう。
VLOOKUP関数は、
指定した検索条件に『当てはまる値』を、指定した検索範囲から調べて、指定した位置から抽出することが出来る
関数です。
もう少しかみ砕いて、具体的な例で説明すると、
商品番号が『２５』の商品を、指定した商品リスト表から調べて、表の左から３列目の『商品名』を抽出することが出来る
関数です。
この説明でも分かりづらい方は、この後に紹介するVLOOKUP関数の使い方を見れば、理解出来るようになると思いますので、いったん『表から条件に合った値を抽出することが出来るんだな』と、思っていただいて結構です。
VLOOKUP関数の構文は下のとおりで、４つの引数を指定する必要があります。
＝VLOOKUP(検索値、検索範囲、列番号、[検索方法]）
下の商品リスト表を例に具体的に説明します。
商品リスト表のセルB4～セルE15(緑色）には、[商品番号][商品カテゴリ][商品名][金額]がまとめられています。枠外のセルH4(赤色）にVLOOKUP関数を用いることで、商品番号”25”（黄色）の商品名をB4～E15の検索範囲(緑色）から抽出することが出来ます。
この表は説明用に小さくしているので、目視でも商品番号”25”の商品名を見つけることは可能ですが、スクロールしないと見れないような膨大なデータの中から検索値を抽出する必要がある時はVLOOKUP関数が大活躍します。
👇VLOOKUP関数の復習はこちらの記事から。
VLOOKUP関数の使い方(おさらい)
続いて、VLOOKUP関数の使い方をおさらいを兼ねて手順を追って紹介します。使い方は十分熟知している方は次の見出しに読み進めてください。
手順１. 検索値を入力
検索範囲から検索したい条件(検索値）をセルG4に入力します。ここでは「商品番号”25”の商品名を抽出する」ために、引数「検索値」で指定するために”25”を入力します。
手順2. VLOOKUP関数を挿入するセルを選択
VLOOKUP関数を挿入するセルH4をクリックします。つまり、抽出結果を表示したいセルのことです。
手順3．「関数の挿入」ボタンからVLOOKUP関数を検索
【関数の挿入】画面が表示されますので、VLOOKUP関数を検索し、【OK】ボタンをクリックします。
手順4. VLOOKUP関数の引数を設定
【関数の引数】画面が表示されるので、検索値、検索範囲、列番号、検索方法の４つの引数を下の図を参考に指定して「OK」で画面を閉じます。引数「検索方法」は、完全一致を条件に検索するため、”FALSE”と入力してください。
手順5．VLOOKUP関数の抽出値を確認
検索範囲の中から商品番号”25”の左から３列目の値である”風邪薬”が抽出されました。
【補足説明】VLOOKUP関数の挿入
手順３，4では、【関数の挿入】ボタンを使用して関数を挿入しましたが、数式バーやセルに直接入力することも出来ます。関数の使用に慣れている方は、VLOOKUP関数を挿入するセルに「=VLOOKUP(G4,$B$4:$E$15,3,FALSE)」を作成した方が時短に繋がります。
VLOOKUP関数で複数条件を指定する方法
まず、上の図を見て下さい。VLOOKUP関数で”B店”の”ブラシ”の値段を見つけたくても、”Ｂ店”のセルは３つあり、”ブラシ”のセルは３つありますよね。
このように、検索する条件が一つだけだと抽出データが２つ以上あり重複してしまう場合があります。そんな時は、どのように対応すればいいのでしょうか。
それでは下の見出しで解説します。
複数の条件に対応する方法
それでは、さきほどの表を使って、”B店”の”ブラシ”という２つの条件に対応する方法を例に紹介します。
結論から先に言ってしまうと、下の２つの手順を実施するだけで複数条件に対応出来ます。
１．表にそれぞれの条件を結合したVLOOKUP関数用のキー列を挿入する
２．VLOOKUP関数の1つ目の引数「検索値」に”＆”を利用する
まず、一つ目の「表にそれぞれの条件を結合したVLOOKUP関数用のキー列を挿入」を説明します。
「キー列」とは、関数を利用出来るようにするための鍵(キー)となる新規列を表に追加することです。
VLOOKUP関数で”B店”で検索してもダメ、VLOOKUP関数で”ブラシ”で検索してもダメなので、下の図のように”B店ブラシ”という新しい検索用の文字列を追加します。
続いて２つ目の「VLOOKUP関数の1つ目の引数「検索値」に”＆”を利用する」について説明します。
VLOOKUP関数の引数は、下の図のように、”＆”を使って条件をつなぎ合わせる(結合）ことが出来ます。こうすることで、表内に新規に追加した「キー列」の中から、引数「検索値」に設定した検索条件を見つけることが出来るようになります。
(検索条件を結合）
次の見出しで、この複数条件を使う２つのテクニックを使って、VLOOKUP関数の複数条件の利用方法を説明します。
複数条件(２つ)でVLOOKUP関数を利用する方法
まずは下の表を使って、”B店”の”ブラシ”の値段をVLOOKUP関数で見つける手順を紹介します。
手順１．キー列を追加
「商品名」の右に新規に１列を追加し、「店舗」と「商品名」を“＆”で結合し、検索用のキーを作成します。
=B4&C4
手順２．キー列を完成
手順１で作成したキーを、下のセルにもコピペして、「キー列」を完成させます。
手順３．VLOOKUP関数を挿入
VLOOKUP関数を挿入し、１つの目の引数「検索値」を、”G4&H4”というように、店舗名のセルG4と商品名のセルH4を“＆”で結合します。残りの引数の設定方法は、下の図のように、通常のVLOOKUP関数の利用と同じ方法です。
=VLOOKUP(G4&H4,$D$4:$E$11,2,FALSE)
その結果、”B店”の”ブラシ”の値段、”600”円を抽出することが出来ました。
このように”＆”を用いて表内にキー列を追加し、さらにVLOOKUP関数内の検索値も”＆”を利用することで、複数条件の設定が可能です。”＆”を２つだけでなく、３つ用いることで、３つの複数条件でVLOOKUP関数を利用することも可能ですので、次の見出しで紹介します。
複数条件(３つ)でVLOOKUP関数を利用する方法
下の表を使って、”B店”の”ブラシ”の”一般価格”の値段をVLOOKUP関数で見つける手順を紹介します。
手順１．キー列を追加
「商品名」の右に新規に１列を追加し、「店舗」と「商品名」と「会員/非会員」を“＆”で結合し、検索用のキーを作成します。下のセルにも全てコピーして「キー列」を完成させます。
=B4&C4&D4
手順２．VLOOKUP関数を挿入
VLOOKUP関数を挿入し、１つの目の引数「検索値」を、”H4&I4&J4”というように、店舗名のセルH4と商品名のセルI4と会員/非会員のセルJ4を“＆”で結合します。残りの引数の設定方法は、下の図のように、通常のVLOOKUP関数の利用と同じ方法です。
=VLOOKUP(H4&I4＆J4,$E$3:$F$11,2,FALSE)
その結果、”B店”の”ブラシ”の”一般価格の”値段、”500”円を抽出することが出来ました。
HLOOKUP関数やXLOOKUP関数でも複数条件は可能
VLOOKUP関数以外にも、HLOOKUP関数やXLOOKUP関数でも、下の表のように同様の手順で複数条件の指定が可能です。
表の中に「キー列」を追加し、１つ目の引数「検索値」に”＆”を利用するだけです。
下の記事でもHLOOKUP関数の複数条件の使い方を紹介していますので、ぜひ参考にしてみてください。
INDEX関数とMATCH関数を使うと、「キー列」が不要
VLOOKUP関数で複数条件を指定する場合、「キー列」を追加するため表の見栄えが悪くなることが気になる人もいるのではないでしょうか。
そんな時は、VLOOKUP関数を利用せず、INDEX関数とMATCH関数を組み合わせて利用することで、表の中から任意の値を抽出することが可能です。
INDEX関数とは
INDEX関数（インデックス）は、指定された行と列が交差する位置にあるセルの値を抽出する関数です。VLOOKUP関数と同じく、表から抽出することが出来るので、この関数をVLOOKUP関数の代わりに利用します。
INDEX関数の構文は下のとおりです。
＝INDEX(配列,行番号,[列番号])
配列は検索範囲のことで、行番号と列番号は数値の入力されたセルを指定するか、直接関数の中に数値を指定します。
実施に下の商品リスト表でINDEX関数を使ってみます。
F11セルに下のINDEX関数が挿入されています。表(B4:D11)の中から、５行目(F6セル)の３列目(G6セル)の値(3,500円）を抽出することが出来ています。
=INDEX(B4:D11,F6,G6)
MATCH関数とは
MATCH 関数(マッチ)は、検索範囲の中から検索値を検索し、その範囲内での検索値の相対的な位置を返します。
＝MATCH(検索値, 検索範囲, [照合の型])
検索値： 検索範囲の中で照合する値を指定します。
検索範囲： 検索するセルの範囲を指定します。
照合の型 ： ”-1”、”0”、”1 ”の数値のいずれかを指定しますが、”1 ”の場合は省略可能です。今回は、「検索値に一致する値のみが検索の対象」とする”0”を利用します。
実際に、下の図でMATCH関数の使用例を説明します。
上の図では、B5セルに「=MATCH($C$2,B2:E2,0)」とMATCH関数を挿入しています。
検索値のC2セル(商品カテゴリ)は、検索範囲のB2:E2の左から２番目にあるので、関数の結果は”２”と表示されます。
INDEX関数とMATCH関数を組み合わる手順
それでは、下の商品リストを例に、“B店”の“ブラシ”の値段を抽出するためににINDEX関数とMATCH関数を使って、複数条件で検索する方法を手順に沿って解説します。
INDEX関数が『VLOOKUP関数の代わりに使う関数』で、『MATCH関数が検索範囲から抽出する位置を指定する役割』を果たします。
手順１．表の枠外に検索条件を書き出す
表の枠外に複数の条件を書き出します。今回は、条件の１つ目が店舗名が“B店”、条件の２つ目のが商品名が“ブラシ”となります。
手順２．INDEX関数を挿入する
検索値を抽出したいセルに、INDEX関数を挿入し、一つ目の引数「配列」を指定します。「配列」は表全体のことを指しますので、列項目を除くB4:D11を指定します。
=INDEX(B4:D11,
手順３．INDEX関数の中にMACH関数を挿入する
INDEX関数の２つ目の引数「行番号」を指定するために、MATCH関数を挿入します。
MATCH関数の１つ目の引数「検索値」は手順１で作成した複数条件のセルを指定します。MATCH関数の２つ目の引数「検索範囲」は、B列の範囲(B4:B11)とC列の範囲(C4:C11)を”＆”でつないで指定します。３つ目の引数「検索の型」は”0”を指定してください。
=INDEX(B4:D11,MATCH(F4&G4,B4:B11&C4:C11,0)
手順４．INDEX関数の３つ目の引数「列番号」を指定
INDEX関数の３つ目の引数「列番号」を指定します。値段は表の３列目に存在するため、”３”と指定します。
これで関数は完成ですが、「Enter」キーは押さず、次の手順５を操作してください。Office365のバージョンを利用している方は、「Enter」キーを押しても大丈夫です。
=INDEX(B4:D11,MATCH(F4&G4,B4:B11&C4:C11,0),3)
手順５．「Ctrl」キー＋「Shift」＋「Enter」キーで確定
関数を確定させるため、「Ctrl」キー＋「Shift」を押しながら「Enter」キーを押してください。
下の図のように“B店”の“ブラシ”の値段”600円”が抽出することが出来ました。また、数式バーを見た時に、{ }で関数全体が囲まれていれば関数が正常に完成しています。
VLOOKUP関数の複数条件は「キー列」の追加がポイント
今回はVLOOKUP関数の複数条件を設定方法をご紹介しました。表に「キー列」の追加し、引数に”＆”を用いるだけの方法なので、想像より簡単だったと思います。誰でも利用出来ますので、ぜひこの機会にVLOOKUP関数をさらにマスターしましょう！