【Excel】VLOOKUP関数を使って複数条件で検索する方法

VLOOKUP関数では、基本的には1つの値を検索して値を表示させますが、工夫することで複数条件で検索することも可能です。また、INDEX関数、MATCH関数を使ってもできますので、その方法も紹介します。

VLOOKUP関数とは

VLOOKUP関数では、特定の値に紐づくデータを自動で入力することができます。

たとえば、商品の注文内容に対して自動で価格を表示させる際に用います。以下のような注文内容の価格を商品リストから引用して表示させてみます。商品リストは別のシートなどにすることが多いですが、今回はわかりやすさのため同じシートにしています。

VLOOKUP関数では、検索したい値（検索値）を指定し、検索に用いるデータの範囲、列から紐づく値を指定することができます。

関数	役割
VLOOKUP(検索値,範囲,列番号,[検索方法])	検索値で指定した値を、範囲の左端列から検索。 検索値と一致するデータの左から列番号目の値を抽出し表示する。

商品Dの価格を表示させるため、以下のように数式を入力します。注意が必要なのは、指定する範囲は商品名と価格の範囲です。VLOOKUPでは、左端の列を検索するためです。

=VLOOKUP(C4,$H$4:$I$13,2,FALSE)

それぞれの数式と役割については以下の通りです。

数式	役割
C4	「商品D」という名前で検索する
$H$4:$I$13	検索に用いたい商品名と価格のデータの範囲を指定する。今回は商品名で検索したいので、商品名の列が範囲の左端に来るようにする。 他の商品も同じ範囲で検索したいので、絶対参照にする。
2	検索した結果、表示させたいデータの列を指定する。今回は価格を表示させたいので範囲の「2」列目を指定する。
FALSE	検索値と完全に一致するデータを検索する

そうすると、以下のように商品Dに該当する金額が表示されます。

セルD4の数式を商品C、商品Fの価格のセルにもコピー&ペーストすることで、それぞれの価格が表示されるようになります。

VLOOKUP関数を使って複数条件で検索する方法

次にVLOOKUP関数を使って複数条件で検索する方法を紹介します。

たとえば、以下のように各商品に同じ名称の種類が設けられている場合に商品を特定するために使えます。

まず、商品リストに手を加えます。K列に商品名&種類という列を作成し、「商品名&種類」の数式を入力します。

そうすると以下のように商品名と種類が組み合わされた文字列が表示されます。これで準備は完了です。

次にVLOOKUP関数を用いて複数条件で検索していきます。

まず、商品Bのプレミアムの価格を検索するため、以下のように数式を入力します。

=VLOOKUP(C4&D4,$K$4:$L$12,2,FALSE)

数式	役割
VLOOKUP(C4&D4	セルC4とセルD4の文字列で検索する。今回は「商品B」と「プレミアム」という名前で検索する。
$K$4:$L$12	検索に用いたい商品名と価格のデータの範囲を指定する。今回は商品名と種類で検索したいので、新しく作成した「商品名&種類」の列が範囲の左端に来るようにする。 他の商品も同じ範囲で検索したいので、絶対参照にする。
2	検索した結果、表示させたいデータの列を指定する。今回は価格を表示させたいので範囲の「2」列目を指定する。
FALSE	検索値と完全に一致するデータを検索する。

そうすると以下のように「商品B」の「プレミアム」の価格が表示されます。

他の商品のセルにもコピーすれば、それぞれの「商品名」と「種類」の複数条件で検索された価格が表示されます。

このように検索するデータを少し加工することで、VLOOKUPを使って複数条件で検索することが可能です。今回は2つの条件で検索しましたが、条件が増えてもやることは同じです。

INDEX関数、MATCH関数を使って複数条件で検索する方法

VLOOKUP関数を使う方法では、検索するデータを加工しましたが、加工ができない場合は、INDEX関数、MATCH関数を使っても複数条件でも検索が可能になります。

INDEX関数

INDEX関数は、指定した範囲内のセルと行を指定してその値を取得することができます。

関数	役割
INDEX(配列,行番号,[列番号])	範囲内のセルと行を指定してその値を取得する

MATCH関数

MATCH関数は、指定した範囲の中で、指定した値がある位置を取得することができます。

関数	役割
MATCH(検索値,検索範囲,[照合の種類])	範囲内のセルと行を指定してその値を取得する。照合の種類は「1」以下、「0」完全一致、「-1」以上から選択することができる。

INDEX関数、MATCH関数を使って複数条件で検索する方法

INDEX関数とMATCH関数を組み合わせて、「商品B」の「プレミアム」の価格を検索するため、セルE4に以下のように数式を入力します。

=INDEX($K$4:$K$12,MATCH(1,($I$4:$I$12=C4)*($J$4:$J$12=D4),0))

数式	役割
INDEX($K$4:$K$12	セルK4~K12の範囲を検索する。今回は商品リストの「価格」を検索する。
MATCH(1	「1」に合致する行を探す。この場合、「1」は「TRUE*TRUE=1*1=1」で両方の条件を満たすということを意味します。
($I$4:$I$12=C4)*($J$4:$J$12=D4)	各条件に一致する行をTRUE/FALSEの形式で表します。TRUEの場合「1」を、FALSEの場合「0」を返します。つまり、「商品B」「プレミアム」に合致する行で「1」となります。
0	照合の種類を完全一致とする。

そうすると以下のように「商品B」の「プレミアム」の価格が表示されます。

他の商品のセルにもコピーすれば、それぞれの「商品名」と「種類」の複数条件で検索された価格が表示されます。

INDEX関数、MATCH関数を組み合わせることで、データを加工することなく複数条件で検索することができます。また、検索方向も垂直方向だけでなく、水平方向にも検索することができます。