ExcelのVLOOKUP関数は、商品コードや社員番号などをキーにして値を検索する際に便利な関数です。
ただし、部署と商品名、日付と担当者のように、2つ以上の条件がそろったときだけ一致する値を取り出したい場面では、通常のVLOOKUPだけでは対応しにくいことがあります。
このような場合は、検索用の補助列を作成して条件を結合する方法、CHOOSE関数で仮想的な検索表を作る方法、複数件を抽出できるFILTER関数を使う方法を使い分けるのが実用的です。
複数条件でVLOOKUPを使うポイントです。
・複数の条件を1つの検索キーとして扱います。
・検索値と検索表側の値を同じ順序で結合します。
・完全一致を指定するため、最後の引数はFALSEまたは0にします。
サンプルデータは1行目に見出しがあるものとして説明します。
それでは、VLOOKUPで複数条件を検索し、一致する値や複数の結果を取り出す方法を確認していきましょう。
VLOOKUPで複数条件を検索する基本方法
それではまず、補助列を使ってVLOOKUPで複数条件を検索する方法について解説していきます。
| 部署 | 商品名 | 担当者 | 売上 |
|---|---|---|---|
| 東京 | ノート | 田中 | 12000 |
| 大阪 | ノート | 佐藤 | 9800 |
| 東京 | ペン | 鈴木 | 6500 |
| 大阪 | ペン | 高橋 | 7200 |
たとえば、部署が東京で商品名がノートである行の売上を検索したいケースを考えます。
部署だけでは東京のデータが複数あり、商品名だけでもノートのデータが複数あるため、片方の条件だけを検索値にすると正しい行を特定できません。
補助列による検索キーの作成
まずは、元の一覧の右側に補助列を追加し、複数条件を結合した検索キーを作成します。
この例ではE列を補助列として、E1セルには検索キーという見出しを入力します。
E2セルには次の数式を入力します。
=A2&”|”&B2
この数式は、A列の部署とB列の商品名を縦棒でつなげ、東京|ノートのような一意性を持ちやすい文字列を作る式です。
条件の間に区切り文字を入れるのは、異なる組み合わせが偶然同じ文字列になることを避けるためです。
たとえば、ABとCを単純に結合したABCと、AとBCを結合したABCは区別できません。
区切り文字を使えばAB|CとA|BCになり、検索キーを安全に判定できます。
数式を入力した後は、E2セル右下のフィルハンドルを下方向へドラッグし、最終行までオートフィルします。
各行に東京|ノート、大阪|ノート、東京|ペンのような検索キーが表示されれば準備完了です。
補助列の条件の並び順は、後で入力する検索値の並び順と必ずそろえることが重要です。
VLOOKUP数式による一致する値の取得
続いて、検索条件を入力するセルを用意します。
たとえばH2セルに東京、I2セルにノートを入力し、J2セルに売上を表示する構成にします。
J2セルには次の数式を入力します。
=VLOOKUP(H2&”|”&I2,$E$2:$G$5,3,FALSE)
ここでは、E列からG列までを検索範囲にしています。
検索キーはE列、担当者はF列、売上はG列に配置する必要があります。
VLOOKUP関数は指定範囲の左端列から検索する仕様のため、補助列を検索範囲のいちばん左に置くことが欠かせません。
第1引数のH2&”|”&I2は、検索側でも東京|ノートという文字列を作る部分です。
第2引数の$E$2:$G$5は検索対象の表であり、コピーしても範囲が動かないよう絶対参照にしています。
第3引数の3は、検索範囲の左から3列目にある売上を返す指定です。
最後のFALSEは完全一致の指定であり、複数条件検索では原則として省略しないほうが安心です。
検索範囲の並び替えとエラー処理
VLOOKUPは検索キーが表の左端にないと機能しないため、元データの列順によっては補助列を先頭側へ移動する必要があります。
元表の構成を変えたくない場合は、別シートに検索用の一覧を作る方法も便利です。
検索条件に該当するデータがないと、VLOOKUPは#N/Aエラーを返します。
利用者にエラーを見せたくない場合は、IFERROR関数で表示内容を整えましょう。
=IFERROR(VLOOKUP(H2&”|”&I2,$E$2:$G$5,3,FALSE),”該当なし”)
この式では、検索できたときは売上を表示し、検索できなかったときだけ該当なしと表示します。
空白の条件セルを含むまま検索すると意図しない検索キーになる場合もあるため、入力規則のリストを設定しておくとミスを抑えられます。
【操作のポイント】補助列は検索範囲の左端に置き、検索側と表側で同じ区切り文字と条件順を使いましょう。
補助列なしで複数条件を検索する方法
続いては、元の表に補助列を追加せず、CHOOSE関数を組み合わせて検索する方法を確認していきます。
| A列 | B列 | C列 | D列 |
|---|---|---|---|
| 部署 | 商品名 | 担当者 | 売上 |
| 東京 | ノート | 田中 | 12000 |
| 大阪 | ノート | 佐藤 | 9800 |
| 東京 | ペン | 鈴木 | 6500 |
補助列が使えない共有ファイルや、データ構造を変更したくない帳票では、数式内で一時的な検索表を作る方法が役立ちます。
CHOOSE関数による仮想検索表
CHOOSE関数は、指定した番号に応じて複数の値や配列から値を選ぶ関数です。
VLOOKUPの検索範囲にCHOOSE関数を使うと、実際には存在しない補助列を数式の中だけで作成できます。
検索条件がH2に東京、I2にノートとして、売上を返す式は次のようになります。
=VLOOKUP(H2&”|”&I2,CHOOSE({1,2},$A$2:$A$5&”|”&$B$2:$B$5,$D$2:$D$5),2,FALSE)
CHOOSE({1,2},配列1,配列2)の部分は、左に結合した検索キー、右に売上を置いた2列の仮想表を作ります。
VLOOKUPはその仮想表の1列目を検索し、2列目にある売上を返す仕組みです。
ワークシート上には補助列が表示されない一方、数式は長くなりやすいため、メンテナンスする人がいるブックでは説明を残すとよいでしょう。
Microsoft 365やExcel 2021以降では通常どおりEnterで確定できます。
古いExcelでは配列数式としての扱いが必要になるケースがあるため、利用しているバージョンを確認してください。
列番号を変更する場合の考え方
担当者と売上を同時に取り出したい場合は、CHOOSE関数の仮想表に必要な列を追加します。
担当者を返す場合は、2列目にC列を指定します。
=VLOOKUP(H2&”|”&I2,CHOOSE({1,2},$A$2:$A$5&”|”&$B$2:$B$5,$C$2:$C$5),2,FALSE)
売上を返す場合は、2列目をD列に置き換えます。
担当者、売上、数量のように複数の項目を返したいときは、CHOOSE({1,2,3,4},検索キー,担当者,売上,数量)のように列を増やせます。
その場合、VLOOKUPの列番号も返したい項目に合わせて2、3、4と変更します。
列番号は元データの列番号ではなく、CHOOSEで作った仮想表の中での位置で数える点に注意しましょう。
補助列方式との使い分け
補助列方式は数式が短く、誰が見ても検索キーの内容を確認しやすい点が長所です。
大量のデータを扱う場合も、あらかじめ検索キーを計算しておけるため、数式の見通しを保ちやすくなります。
一方、CHOOSE関数の方式は元データを変更せずに済み、提出用の一覧や他部署管理のデータを参照するときに向いています。
ただし、検索条件や戻り値が増えるほど数式が複雑になるため、必要以上に多くの条件を1セルの数式へ詰め込まないことも大切です。
検索式を横方向へコピーする場合は、検索条件のセル参照と検索範囲の絶対参照を確認しましょう。
【操作のポイント】補助列を追加できないときはCHOOSEで検索キー列を仮想的に作り、列番号は仮想表を基準に数えます。
FILTER関数による複数抽出の方法
続いては、同じ条件に一致するデータを複数行まとめて抽出できるFILTER関数を確認していきます。
| 部署 | 商品名 | 担当者 | 売上 |
|---|---|---|---|
| 東京 | ノート | 田中 | 12000 |
| 東京 | ノート | 山本 | 15400 |
| 東京 | ペン | 鈴木 | 6500 |
| 大阪 | ノート | 佐藤 | 9800 |
VLOOKUPは基本的に最初に見つかった1件だけを返す関数です。
東京かつノートのように一致する行が複数ある場合、すべての該当行を一覧で表示したいならFILTER関数が適しています。
複数条件を掛け合わせる数式
H2セルに東京、I2セルにノートを入力し、抽出結果を表示したいJ2セルに次の式を入力します。
=FILTER(A2:D5,(A2:A5=H2)*(B2:B5=I2),”該当なし”)
第1引数A2:D5は返したいデータ範囲です。
第2引数では、部署がH2と一致するかどうか、商品名がI2と一致するかどうかを判定しています。
アスタリスクは掛け算として働き、両方の条件がTRUEの行だけが1となります。
したがって、条件1と条件2を両方満たすAND条件として複数行を抽出できます。
第3引数の該当なしは、一致データがない場合の表示です。
FILTER関数の結果はJ2セルから下方向および右方向へ自動で展開されるため、展開先のセルは空けておきましょう。
OR条件と数値条件の組み合わせ
東京または大阪のデータを抽出したい場合は、条件式を足し算でつなぎます。
=FILTER(A2:D5,(A2:A5=”東京”)+(A2:A5=”大阪”),”該当なし”)
足し算は、どちらかの判定がTRUEなら1以上になるため、OR条件として機能します。
売上が10000以上で、かつ東京支店という条件では、次のように比較式を掛け合わせます。
=FILTER(A2:D5,(A2:A5=”東京”)*(D2:D5>=10000),”該当なし”)
文字列、日付、数値を混在させる場合は、列ごとの表示形式も確認してください。
特に日付は見た目が同じでも内部のシリアル値や時刻情報の違いで一致しないことがあります。
日付条件では開始日以上かつ終了日以下という比較式を使うと、期間を安定して指定できます。
スピル範囲と並べ替えの活用
FILTER関数で出力された範囲はスピル範囲と呼ばれ、元となる数式は先頭セルだけに入っています。
スピル範囲の途中を直接編集しようとするとエラーになるため、修正は元データまたは先頭セルの数式で行います。
抽出した結果を売上順に並べたい場合は、SORT関数を外側に組み合わせます。
=SORT(FILTER(A2:D5,(A2:A5=H2)*(B2:B5=I2),”該当なし”),4,-1)
この式では、FILTERで取り出した表を4列目の売上を基準に降順で並べ替えます。
複数件の検索結果を報告書や確認用リストへ出したい場合に、手作業のフィルターよりも更新しやすい方法です。
【操作のポイント】複数件をすべて表示する場合はFILTERを使い、抽出結果が展開されるセル範囲を空白に保ちましょう。
一致しない原因とエラーの確認
続いては、複数条件の検索で一致しない場合に確認したい原因を解説していきます。
| 表示 | 主な原因 | 確認内容 |
|---|---|---|
| #N/A | 該当データなし | 条件値と検索キー |
| #VALUE! | 配列や参照の不整合 | 範囲の行数 |
| #SPILL! | 展開先に値がある | 空白セルの確保 |
空白と全角半角の違い
見た目が同じ文字列でも、末尾の空白や全角半角の違いがあると完全一致になりません。
たとえば東京の後ろにスペースが含まれていると、東京という検索値では一致しない状態になります。
不要な空白を取り除くにはTRIM関数を使えます。
=TRIM(A2)&”|”&TRIM(B2)
全角の英数字や記号が混じるデータでは、入力規則で候補を選択させる仕組みにすると表記ゆれを減らせます。
検索できないときは数式を疑う前に、検索値と元データを数式バーで見比べることが近道です。
数値と文字列の形式差
商品コード00123のようなデータは、文字列として保存されている場合と数値として保存されている場合があります。
数値の123と文字列の00123は同じものではないため、VLOOKUPの完全一致では一致しません。
先頭のゼロが必要なコードは、元データと検索値の両方を文字列として扱うのが基本です。
数値化が必要な場合はVALUE関数、文字列として整える場合はTEXT関数を使い、形式をそろえます。
日付も同様で、表示形式だけを変えても内部データがそろわないことがあります。
参照範囲と絶対参照の確認
数式をコピーした後に検索範囲がずれてしまうと、別の表を検索している状態になります。
検索範囲は$A$2:$D$100のように絶対参照にするのが安全です。
一方で、検索条件のH2やI2は、下方向へコピーするならH2やI2のように行番号を変化させる必要があります。
条件を横方向へコピーする場合は、$H2のような複合参照を使うと設計しやすくなります。
【操作のポイント】エラー時は空白、文字列と数値、検索範囲のずれを順番に確認すると原因を絞り込めます。
VLOOKUP以外の検索関数の使い分け
続いては、VLOOKUP以外の関数を含めた複数条件検索の使い分けを確認していきます。
| 関数 | 向いている用途 | 特徴 |
|---|---|---|
| VLOOKUP | 1件の値を取得 | 補助列と相性がよい |
| XLOOKUP | 柔軟な1件検索 | 左右どちらにも返せる |
| INDEX MATCH | 旧版も含む高度な検索 | 列挿入に強い |
| FILTER | 複数行の抽出 | 結果を一覧表示できる |
XLOOKUP関数による複数条件検索
Microsoft 365などで使えるXLOOKUP関数は、VLOOKUPよりも柔軟に検索できます。
補助列なしで複数条件を指定する場合は、検索配列に条件式を使います。
=XLOOKUP(1,(A2:A5=H2)*(B2:B5=I2),D2:D5,”該当なし”)
条件を掛け合わせた結果は、両方一致する行だけ1になります。
XLOOKUPはその1を検索し、D列の売上を返します。
戻り値の列が検索列より左にあっても検索できるため、列順の制約を避けたいときに便利です。
INDEX MATCH関数による検索
古いExcelでも複数条件検索を実現したい場合は、INDEX関数とMATCH関数の組み合わせが候補になります。
売上を返す基本的な考え方は、MATCHで一致行の位置を求め、INDEXでその位置の値を取り出す方法です。
=INDEX(D2:D5,MATCH(1,(A2:A5=H2)*(B2:B5=I2),0))
Excelのバージョンによっては、配列数式としてCtrlとShiftとEnterで確定する必要があります。
現在のExcelではXLOOKUPやFILTERのほうが読みやすい場面も多いものの、既存ブックの数式を理解するために知っておくと役立ちます。
目的別の関数選択
一致する最初の1件だけを返し、Excelの幅広いバージョンで使いたいなら補助列とVLOOKUPの組み合わせが堅実です。
列の並び順に左右されたくない場合はXLOOKUP、複数件を一覧で取り出す場合はFILTERを選びましょう。
データがテーブル形式なら、構造化参照を使うと範囲の追加にも対応しやすくなります。
関数を複雑にしすぎるより、利用するExcelのバージョンと、結果を1件だけ返すのか複数件返すのかを最初に決めることが重要です。
【操作のポイント】1件の値にはVLOOKUPやXLOOKUP、複数行の一覧にはFILTERを選ぶと数式を整理しやすくなります。
まとめ エクセルのVLOOKUPで複数条件を検索する方法
エクセルのVLOOKUPで複数条件を検索するには、部署と商品名のような条件を結合し、検索用のキーとして扱う方法が基本です。
補助列を使う方法は構造が分かりやすく、検索範囲の左端にキーを置けばVLOOKUPで安定して完全一致検索を行えます。
元表を変更できない場合はCHOOSE関数で仮想表を作成し、補助列なしで複数条件検索を実現できます。
同じ条件に一致する行をすべて表示したいときは、最初の1件しか返さないVLOOKUPではなくFILTER関数を使うのが適切です。
検索結果が出ないときは、空白、全角半角、文字列と数値の形式、絶対参照の範囲を確認しましょう。
目的に合う関数と検索方法を選び、複数条件のデータ管理を正確かつ効率的に進めていきましょう。
