エクセルで「複数の条件を同時に満たすデータだけを取り出したい」という場面は、実務でも非常によく発生します。
しかし、VLOOKUP関数は本来1つの検索値にしか対応していないため、複数条件での抽出には少し工夫が必要です。
本記事では、VLOOKUP関数で複数条件に一致するデータを抽出する方法を、補助列を使う方法・配列数式を使う方法・XLOOKUP関数を使う方法の3つのアプローチから丁寧に解説します。
それぞれのメリットと使いどころも整理していますので、自分の環境や目的に合った方法を見つけていただけるはずです。
XLOOKUPへの対応方法も後半でしっかり解説しますので、ぜひ最後までご覧ください。
VLOOKUPで複数条件抽出をするための基本的な考え方
まず前提として、VLOOKUP関数は検索値が1つしか指定できないという仕様を理解しておく必要があります。
通常のVLOOKUP関数の構文は以下の通りです。
【VLOOKUP関数の構文】
=VLOOKUP(検索値, 範囲, 列番号, 検索方法)
この「検索値」に指定できるのは1つだけのため、複数の条件を同時に満たすデータを検索するには工夫が必要です。
代表的なアプローチは大きく3つあります。
1つ目は補助列に複数の条件を結合した文字列を作成する方法、2つ目は配列数式でCHOOSEやIFと組み合わせる方法、3つ目はExcel365以降で使えるXLOOKUP関数を活用する方法です。
以下のサンプルデータを使って、それぞれの方法を順番に確認していきます。
| 行 | A列：商品名 | B列：カテゴリ | C列：担当者 | D列：売上数 |
|---|---|---|---|---|
| 1 | 商品名 | カテゴリ | 担当者 | 売上数 |
| 2 | 桜餅 | 和菓子 | 田中 | 120 |
| 3 | 柏餅 | 和菓子 | 鈴木 | 85 |
| 4 | マシュマロ | 洋菓子 | 田中 | 200 |
| 5 | チョコ | 洋菓子 | 佐藤 | 150 |
| 6 | アボカド | 野菜 | 鈴木 | 60 |
| 7 | カボチャ | 野菜 | 田中 | 90 |
| 8 | マグロ | 魚介 | 佐藤 | 310 |
| 9 | カツオ | 魚介 | 鈴木 | 270 |
| 10 | ハラス | 魚介 | 田中 | 180 |
| 11 | ボルト | 工具 | 佐藤 | 400 |
| 12 | ネジ | 工具 | 鈴木 | 350 |
1行目はヘッダー行で、データは2行目から12行目まで入力されています。
このデータを使って「カテゴリが魚介かつ担当者が田中の売上数を取り出す」という操作を各方法で実践します。
【操作のポイント】VLOOKUPで複数条件を扱うには、補助列・配列数式・XLOOKUPの3つのアプローチがあります。使用しているExcelのバージョンと、データ構造に合わせて最適な方法を選びましょう。
補助列を使ってVLOOKUPで複数条件抽出をする方法
最も汎用性が高く、どのバージョンのエクセルでも使える方法が補助列を使ったVLOOKUP複数条件抽出です。
補助列とは、複数の条件となる値を「&」演算子で結合した文字列を別の列に作成する列のことです。
検索値側も同様に結合することで、擬似的に複数条件の一致を実現します。
補助列の作成手順
まずE列を補助列として使用します。
E1セルに「補助列」とヘッダーを入力し、E2セルに以下の数式を入力します。
【補助列の数式（E2セル）】
=B2&”_”&C2
この数式でB列のカテゴリとC列の担当者を「_」でつないだ文字列を作成します。
E2セルには「和菓子_田中」という値が表示されます。
E2セルを選択してE12セルまでオートフィルでコピーすることで、全行に補助列の値が入ります。
補助列を使ったVLOOKUP数式の入力
補助列が完成したら、抽出用のVLOOKUP数式を別のセル（例：G2セル）に入力します。
「カテゴリが魚介かつ担当者が田中の売上数」を取り出す場合は以下のようになります。
【補助列を使ったVLOOKUP数式】
=VLOOKUP(“魚介_田中”,E2:D12,4,0)
※E列を先頭にした範囲を指定し、D列（売上数）は4列目
=VLOOKUP(“魚介_田中”,$E$2:$D$12,4,FALSE)
検索値に「魚介_田中」という結合文字列を指定し、E列から始まる範囲でVLOOKUPを実行します。
ハラス（魚介・田中）の売上数である180が返ります。
検索値をセル参照で動的にする場合は、条件をF1・F2セルに入力してから以下のように書きます。
【セル参照を使った補助列VLOOKUP】
F1セル：魚介 F2セル：田中
=VLOOKUP(F1&”_”&F2,$E$2:$D$12,4,FALSE)
F1とF2の値を変えるだけで検索条件が切り替わる、動的な複数条件検索が実現します。
補助列を使う方法の注意点
補助列を使う方法はわかりやすく安定していますが、VLOOKUPの検索範囲は補助列が先頭列である必要がある点に注意が必要です。
今回はE列を補助列にしてE列から始まる範囲を指定していますが、補助列をA列などデータの左側に配置するとVLOOKUPの範囲指定がより自然になります。
また、補助列の結合文字（今回は「_」）がデータ値の中に含まれている場合、意図しない一致が起きる可能性があるため、データに含まれない区切り文字を選ぶことをおすすめします。
【操作のポイント】補助列を使ったVLOOKUP複数条件抽出では、B列とC列を「&」で結合した補助列をE列に作成し、検索値も同じ形式で結合して指定します。検索値をセル参照にすると条件の切り替えが柔軟になります。
配列数式とCHOOSEを使ってVLOOKUPで複数条件抽出をする方法
補助列を追加したくない場合は、CHOOSE関数と配列数式を組み合わせてVLOOKUPで複数条件抽出をする方法が有効です。
この方法では、CHOOSE関数を使って仮想的な2列構造の配列を作り出し、その先頭列を複数条件の結合文字列にします。
CHOOSE関数を使った仮想補助列のしくみ
CHOOSE関数は通常「インデックス番号に応じた値を返す」関数ですが、配列として使うと複数列の仮想テーブルを生成することができます。
【CHOOSE関数を使ったVLOOKUP複数条件数式】
=VLOOKUP(“魚介_田中”,CHOOSE({1,2},B2:B12&”_”&C2:C12,D2:D12),2,0)
※Ctrl＋Shift＋Enterで配列数式として確定（Excel2019以前）
この数式のCHOOSE({1,2},…)の部分が仮想テーブルを生成しており、1列目がB列とC列を結合した文字列配列、2列目がD列の売上数になります。
VLOOKUPはその仮想テーブルの1列目（結合文字列）を検索し、2列目（売上数）を返します。
Excel 2019以前ではCtrl＋Shift＋Enterで配列数式として確定する必要があります。
数式バーに｛｝で囲まれた形で表示されれば正しく配列数式として入力されています。
Excel 365やExcel 2021では通常のEnterキーでも動作するケースがありますが、念のためCtrl＋Shift＋Enterで確定するのが安全です。
配列数式・CHOOSE方式のメリットとデメリット
この方法の最大のメリットは補助列が不要でシートを汚さない点です。
数式1つで完結するためシートがすっきり保てます。
一方でデメリットとしては、数式が複雑になりメンテナンスが難しい点と、大量データの場合に処理が重くなる可能性がある点が挙げられます。
また、Ctrl＋Shift＋Enterを忘れると正しい結果が返らないため、入力時に注意が必要です。
【操作のポイント】CHOOSE関数を使った配列数式では、CHOOSE({1,2}, 結合条件列, 返値列)という構造が核心です。Excel 2019以前ではCtrl＋Shift＋Enterで確定することを忘れずに行いましょう。
IFと配列数式を組み合わせてVLOOKUPで複数条件抽出をする方法
CHOOSE関数を使わずに、IF関数と配列を組み合わせる方法でも複数条件のVLOOKUPを実現できます。
【IFと配列を使ったVLOOKUP複数条件数式】
=VLOOKUP(“魚介”&”田中”,IF({1,0},B2:B12&C2:C12,D2:D12),2,0)
※Ctrl＋Shift＋Enterで確定
IF({1,0},…)の部分がCHOOSE({1,2},…)と同様に仮想テーブルを生成します。
{1,0}は「1のときは1列目の値、0のときは2列目の値」という配列定数で、VLOOKUPに渡す仮想2列テーブルを作り出します。
検索値の「”魚介”&”田中”」はB列とC列の結合パターンに合わせて区切り文字なしで指定しています。
この方法ではCHOOSE方式と同様に補助列が不要で、関数の構造を理解しやすい面があります。
区切り文字あり・なしの使い分け
検索値と補助列（または仮想列）の結合パターンは必ず一致させる必要があります。
補助列で「B2&”_”&C2」と区切り文字「_」を使った場合は、検索値も「”魚介_田中”」のように同じ区切り文字を使います。
区切り文字なしで「B2&C2」とした場合は検索値も「”魚介田中”」となります。
どちらでも機能しますが、区切り文字ありのほうが誤一致のリスクが低くおすすめです。
【操作のポイント】IF({1,0},…)方式もCHOOSE方式も本質的には同じ仮想テーブルを作る考え方です。検索値と結合方式を必ず統一し、区切り文字を設定することで誤一致を防ぎましょう。
XLOOKUPで複数条件に一致するデータを抽出する方法
Excel 365またはExcel 2021以降をお使いの場合は、XLOOKUP関数を使った複数条件抽出が最もスマートな方法です。
XLOOKUP関数はVLOOKUPの後継として登場した関数で、複数条件の処理が格段に簡単になっています。
【XLOOKUP関数の構文】
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
XLOOKUPで複数条件を指定する数式
XLOOKUPで複数条件を指定する場合は、検索値と検索範囲をそれぞれ配列の乗算（*）で結合する方法が定番です。
【XLOOKUPで複数条件抽出する数式】
=XLOOKUP(1,(B2:B12=”魚介”)*(C2:C12=”田中”),D2:D12)
この数式では、(B2:B12=”魚介”)*(C2:C12=”田中”)の部分が「カテゴリが魚介かつ担当者が田中」という条件を配列の掛け算で表現しています。
両方の条件が一致する行だけが1（TRUE×TRUE）となり、他の行は0（FALSE）になります。
XLOOKUPはその中から最初に「1」が現れる行のD列（売上数）を返します。
ハラス（行10）が該当し、結果として180が返ります。
この方法の優れている点は配列数式の確定（Ctrl＋Shift＋Enter）が不要である点と、補助列も必要ない点です。
XLOOKUPの見つからない場合の処理を追加する
一致するデータが見つからなかった場合に、エラー表示（#N/A）ではなく任意のメッセージを表示させることができます。
【エラー処理を追加したXLOOKUP】
=XLOOKUP(1,(B2:B12=”魚介”)*(C2:C12=”田中”),D2:D12,”該当なし”)
第4引数に「”該当なし”」を指定するだけで、一致データがない場合に「該当なし」と表示されます。
VLOOKUPの場合はIFERROR関数を別途ネストする必要があるため、XLOOKUPのほうがシンプルに記述できるのが大きなメリットです。
XLOOKUPで3条件以上の複数条件に対応する
XLOOKUPの複数条件は3つ以上にも簡単に拡張できます。
【3条件の例：カテゴリ・担当者・売上数100以上】
=XLOOKUP(1,(B2:B12=”魚介”)*(C2:C12=”田中”)*(D2:D12>=100),A2:A12,”該当なし”)
条件をさらに*(D2:D12>=100)と掛け合わせるだけで3条件AND検索が実現します。
戻り範囲をA列にすることで、条件に一致した商品名（ハラス）を返すことも可能です。
【操作のポイント】XLOOKUPの複数条件は「(条件1)*(条件2)」の形で配列を掛け合わせて1を検索するパターンが基本です。Ctrl＋Shift＋Enterは不要で、第4引数にエラー時の表示も設定できます。
VLOOKUP複数条件でよくあるエラーと対処法
VLOOKUPで複数条件抽出を行う際に発生しやすいエラーについて、原因と対処法を解説します。
#N/Aエラーが出る場合の対処法
#N/Aエラーは検索値が検索範囲に見つからない場合に発生します。
補助列方式の場合は、検索値の結合文字列と補助列の結合文字列が一致しているか確認しましょう。
たとえば補助列で「魚介_田中」と区切り文字「_」を使っているのに、検索値に「”魚介田中”」と区切り文字なしで指定していると#N/Aエラーになります。
エラーを非表示にしたい場合はIFERROR関数でラップします。
【IFERRORでエラーを処理する例】
=IFERROR(VLOOKUP(“魚介_田中”,$E$2:$D$12,4,FALSE),”該当なし”)
配列数式が正しく動作しない場合の確認ポイント
CHOOSE方式やIF方式の配列数式で結果がおかしい場合は、Ctrl＋Shift＋Enterで確定されているかを確認します。
通常のEnterで確定すると{=}の中括弧が付かず、配列計算が行われないため正しい結果が返りません。
数式バーに「{=VLOOKUP(…)}」と中括弧で囲まれて表示されていれば配列数式として正しく入力されています。
VLOOKUPの検索範囲の先頭列が検索値と一致しているか確認する
VLOOKUPの検索範囲は先頭列が検索対象になるという仕様があります。
補助列をE列に作成した場合、VLOOKUPの範囲は必ず「E列から始まる範囲」にする必要があります。
A列から範囲を指定してしまうと、A列（商品名）を検索対象として処理されるため正しく抽出できません。
【操作のポイント】#N/Aエラーは検索値と補助列の結合形式の不一致が主な原因です。区切り文字・スペースの有無を確認しましょう。配列数式はCtrl＋Shift＋Enterで確定されているかを数式バーの｛｝で確認します。
まとめ：VLOOKUPの複数条件抽出・XLOOKUPを活用してデータ集計を効率化する
本記事では、エクセルのVLOOKUP関数で複数条件に一致するデータを抽出する方法と、XLOOKUPを使った複数条件抽出について詳しく解説しました。
補助列を使う方法はどのバージョンのエクセルでも安定して使えるため、まず覚えておきたい定番テクニックです。
補助列を作りたくない場合はCHOOSE関数やIF関数と配列数式を組み合わせる方法が有効で、シートをすっきり保ちながら複数条件抽出が実現できます。
Excel 365やExcel 2021以降をお使いであれば、XLOOKUP関数を使った複数条件抽出が最もシンプルで拡張性も高くおすすめです。
条件が増えても*(掛け算)を追加するだけで対応でき、エラー処理も第4引数で1行で書けます。
今回ご紹介した3つのアプローチを状況に応じて使い分けることで、エクセルのデータ抽出・集計作業を大幅に効率化できるはずです。
VLOOKUP・XLOOKUPの複数条件抽出をマスターして、日々の業務をさらにスムーズに進めていきましょう。
