VLOOKUP関数を複数条件にする方法|検索値を増やすには
ここでは、エクセルの「VLOOKUP関数を複数条件(複数の検索値)にする方法」を解説します。
ただし方法はかなり強引で、「検索値を&でつなげる」だけではできません。そのため、他の関数を使った方法も紹介します。
1.VLOOKUP関数を複数条件にするとは
まずは、ここで説明する「VLOOKUP関数を複数条件にする」とはどのようなことか、例を含めて確認します。
1-1.複数条件の全てに該当するデータを抽出したい
やりたいことは、「複数条件の『全て』に該当するデータを抽出する」です。
例は次の通りです。
- 「東京都」で「40歳」の人の氏名を抜き出す
- 「雑貨」で「1,000円以下」の商品名を抽出
- 「総務部」で「部長」、かつ「40歳」の人の名前を取出
このような条件の形は「AND条件」とも言い、例えば条件Aが「東京都」、条件Bが「40歳」であれば、「東京都の40歳の人」のデータを抜き出せます。
なお、条件の「どれかに該当」はOR条件と言い、これから紹介する方法とは異なります。
1-2.例を確認
一つ例を見てみましょう。
上の表は、社員の基本情報の一覧です。氏名や年齢、性別、住所、役職が記載されています。
この表から、「女性」で「係長」の人を抽出してみます。
始めに述べておきますが、VLOOKUP関数の複数条件は、上のように「作業列が必要」で「検索値もちょっと変」になります。
しかし一方で、これらさえ問題なければ可能です。
2.スマートなのは「INDEX+MATCH関数」
VLOOKUP関数を複数条件にするのは、先ほどのように「やり方がかなり強引」です。
ここではそのデメリットと代替方法を確認します。
2-1.VLOOKUP関数のデメリット
VLOOKUP関数で複数条件に該当するデータを抜き出そうとすると、次のようなデメリットが生じます。
- 本来は必要のない「作業列」が必要で見た目が悪い
- 「連結させた検索値」が必要で変
「作業列」とは、「本来は必要ないが、計算上で必要な列」のことです。
「スマートじゃない」、「見た目が悪い」といったイメージから、作業列は個人的には極力使わないようにしています。
また、検索値は「複数の文字列を連結させた形」にする必要があるため、やはりちょっと変です。
これらのデメリットは、ほぼ「個人的に許容できるか?」という内容のため、「VLOOKUP関数を複数条件にできるか?」という問いには「できる」ということになります。
2-2.INDEX+MATCH関数ならスッキリできる
一方で、「INDEX関数+MATCH関数」ならもっとスマートに同じことができます。
INDEX+MATCH関数のメリットは、次の通りです。
- 作業列が必要ない
- 検索値が普通
- 検索対象の列の位置が自由
対してデメリットは「数式が長い」ですが、コツさえ押さえれば簡単です。
VLOOKUP関数にこだわらないのであれば、こちらの方法がおすすめです。
詳しいやり方は、以下のリンク先のページで説明しています。
3.複数条件にするときの数式の形とポイント
では、VLOOKUP関数を複数条件にする時の数式の形とポイントを見てみましょう。
3-1.数式の形は
数式の形は、検索値を「文字列で入れる」か「セル番号で入れる」かによって変わり、それぞれ次の通りです。
【文字列で入れる場合】
【セル番号で入れる場合】
※ 条件が2つの場合
検索値を「文字列で入れる」なら、「2つの条件を続けて書き」、「"」で囲みます。
対して「セル番号で入れる」なら、「セル番号を『&』でつなぎ」ます。
例は次の通りです。
- =VLOOKUP("東京都総務部",A2:F9,2,FALSE)
- =VLOOKUP(A1&B1,A2:F9,2,FALSE)
ただし、数式の他にやることがあります。
3-2.複数条件にする時のポイント
複数条件にする時には、数式の形の工夫の他に必要なことがあります。
それは、次の通りです。
- 指定する「範囲」の左端に「作業列」を用意する
- 「作業列」に「検索対象のセル番号」を「&」でつなげる
- つなげる順番を検索値と合わせる
- 引数「範囲」に作業列を含める
VLOOKUP関数は、「条件を検索する列が『範囲』の左端」と決まっています。
そのため、「作業用として範囲の左端に1列追加」します。
次に、作業列に「検索対象になる同行のセル番号を『&』でつないで指定」します。この時、順番を検索値と合わせます。
準備した作業列の例は、次の通りです。
このように、VLOOKUP関数を複数条件にするには「数式」と「作業列」に工夫が必要です。
4.VLOOKUP関数を2つの複数条件にする方法
では、VLOOKUP関数を使って「2つの複数条件(検索値2つ)」にする方法を、実際に数式を作りながら見てみます。
条件は「女性」で「係長」の人とします。
4-1.元の表の左端に作業列を1列確保する
まずは元の表の左端に、作業列を1列確保します。
表が左端に寄せて作られていた場合には、列の挿入で確保しましょう。
4-2.作業列に検索対象を&でつなげる
次に、作業列の各セルに対して「検索対象の同行のセルの値を『&』でつなぐ」ことをします。
「検索対象のセル」とは、作業列と同じ行にある、条件に一致するかを確認したい各セルのことです。
作業列に次のような数式を入れます。
=セル番号1&セル番号2
例であればセルA2は
=D2&F2
となります。
その後、他のセルに数式をコピーします。
4-3.検索値を2つつなげる
続いて、検索値を「2つつなげる」ことをします。
先ほど確認した通り、繋げ方には次のパターンがあります。
- つなげた文字列を「"」で囲む(例:"女係長")
- 検索値を入れたセル同士を「&」でつなぐ(例:A1&B1)
ちなみに、「セルにつなげた文字列を入力(例:女係長)」 し、そのセル番号を検索値に入れる、という方法もあります。
4-4.作業列も範囲に含めてVLOOKUP関数の数式を作る
最後に、作業列も範囲に含めてVLOOKUP関数の数式を作ります。
結果、セルB13に入れた数式は次の通りです。
=VLOOKUP($A13$,$A$2:$F$9,2,FALSE)
これで完了です。
5.VLOOKUP関数を3つ以上の複数条件にする方法
続いて、VLOOKUP関数を「3つ以上の複数条件(検索値3つ以上)」にする方法です。
5-1.条件を3つ以上にするポイント
条件を増やす場合のポイントは、次の通りです。
- 「作業列」と「検索値」のつなげる対象を増やす
- つなげる順番を一致させる
基本的には検索値が2つの場合と一緒で、つなげる数が増えるだけです。
なお、注意するのはやはり「つなげる順番」です。作業列の値と検索値が一致するようにします。
5-2.例を確認
一つ例を見てみましょう。
条件は「男性」で「東京都」、「係長」の人とします。
まずは、作業列に検索対象の値を「&」でつなげます。
例の場合セルA2に入力するのは、
=D2&E2&F2
です。
次に、3つの検索値が一つの文字列になるよう&で繋げます。
後は、作業列も含めてVLOOKUP関数を作ります。これで検索値が3つでも、VLOOKUP関数でデータを抽出できるようになります。
【口コミ・評判のいいExcel本の紹介】
6.結果を別シートに表示させるには
最後に、「複数条件に該当したデータを別シートに表示させる方法」です。
別シートに表示させる際のポイントは、次の通りです。
- 「範囲」に「別シートの範囲」を指定する
- 形は「シート名!範囲」(または「'シート名'!範囲」)
- 「シートをクリック」→「範囲を左ドラッグ」で簡単に指定できる
- 他の引数は普段通り
引数「範囲」に「別のシートにあって抽出したい範囲」を指定すれば、条件に合った別シートのデータを抜き出せます。
そして、範囲を指定する時に「シートをクリック」→「範囲を左ドラッグ」すれば、シート名月の範囲が自動で入力されるため、便利です。
例えばSheet2に「Sheet1のA1からB3の範囲」と入れたい場合、手順は上のようにSheet1をクリック(①)し、A1からB3を左ドラッグ(②)します。
慣れると簡単ですよ。
以上、参考になれば幸いです。
VLOOKUP関数のその他の記事はこちらです。
- VLOOKUP関数その他の記事はこちら
- ◎基本的な使い方
- ◎「以上/未満」の条件指定の仕方
- ◎〇番台の条件にする方法
- ◎部分一致の条件指定の仕方
- ◎別シートから値を抜き出す方法
- ◎別ブックから値を抜き出す方法
- ◎数式コピーで横方向の値を簡単に取得する方法
- ◎2番目、3番目以降の値を抽出する方法
- ◎#N/Aエラーになる3つの原因と対処方法
- ◎#REF!エラーになる原因と対処方法
- ◎#VALUE!エラーになる原因と対処方法
- ◎空白を0ではなく空白で表示する2つの方法
- ◎「IF(ISERROR(VLOOKUP」の意味と使い方
- ◎IFERROR関数とVLOOKUPの組み合わせ方
- ◎空白でもエラーでも空白で表示する方法
- ◎複数条件にする方法
- ◎セルや範囲を固定しコピーのズレを防ぐ方法
- ◎文字列を条件にする方法
- ◎列番号の意味と指定の仕方