【Excel】エクセルでXLOOKUP関数に複数条件を指定する方法（検索値を組み合わせて抽出）

いつも記事を読んでいただきありがとうございます！！！ これからもお役に立てる各情報を発信していきますので、今後ともよろしくお願いします(^^)/

Excelで商品名と担当者、部門と年月、顧客コードと区分など、複数の条件を満たすデータを検索したい場面は少なくありません。

XLOOKUP関数は本来ひとつの検索値を探す関数ですが、検索値と検索範囲を同じルールで組み合わせることで、複数条件の検索にも対応できます。

この記事では、XLOOKUP関数で複数条件を指定して、該当する行から必要な値を抽出する方法を、サンプル表と数式を使って詳しく解説します。

元データの1行目には見出しがあるものとして、以下では2行目からデータが始まる例で説明します。

関数をコピーして使える形にしながら、エラー対策や実務での注意点も確認していきましょう。

 

XLOOKUP関数で複数条件を組み合わせる方法

それではまず、XLOOKUP関数へ複数条件を指定して検索する基本の方法について解説していきます。

商品名	担当者	販売数	売上
ノートPC	田中	12	1440000
ノートPC	佐藤	8	960000
モニター	田中	15	450000

 

検索値と検索範囲の連結

複数条件を扱うときは、条件をひとまとまりの文字列として扱う考え方が便利です。

たとえば検索したい商品名がF2セル、担当者名がG2セルに入力されている場合、検索値はF2&G2と記述します。

元データ側では、商品名のA列と担当者のB列をA2:A100&B2:B100のように連結します。

この数式は、F2とG2の組み合わせに一致する行を探し、その行のD列にある売上を返します。

検索値と検索範囲の連結順序は必ずそろえることが重要です。

検索値を商品名&担当者にしたなら、検索範囲も商品名列&担当者列にします。

 

売上を抽出する基本数式

実際に売上を取り出す場合は、結果を表示したいセルを選択して数式バーに入力します。

F2にノートPC、G2に田中と入力した場合、数式の結果は1440000になります。

XLOOKUP関数は最初に見つかった一致データを返すため、同じ商品名と担当者の組み合わせが複数ある場合は、元データの上側にある値が返されます。

重複があり得る一覧では、受注番号や日付など、行を一意に特定できる条件を追加する設計が必要です。

 

区切り文字を加える連結式

データによっては、単純な連結だけでは別の組み合わせと同じ文字列になる可能性があります。

たとえばABとC、AとBCは、どちらもABCになってしまいます。

このような誤一致を避けるには、条件の間に区切り文字を入れます。

縦棒は通常の氏名や商品名に含まれにくく、結合した条件の境界を明確にできる記号です。

区切り文字はハイフンなどでも構いませんが、元データに使われない文字を選びましょう。

 

複数条件で検索値を作成する手順

続いては、条件入力欄を作成し、複数条件の検索値を実務で扱いやすくする手順を確認していきます。

F列	G列	H列
商品名	担当者	検索結果
ノートPC	田中	1440000

 

条件入力セルの準備

検索条件を直接数式へ書き込むよりも、専用の入力セルを用意するほうが表を再利用しやすくなります。

F1に商品名、G1に担当者、H1に売上と入力し、F2とG2を条件の入力欄にします。

検索する人はF2とG2だけを書き換えればよく、数式を編集する必要がありません。

商品名や担当者の表記ゆれを減らしたい場合は、データの入力規則を設定し、リストから選べるようにする方法も有効です。

検索条件と元データの表記を完全に一致させることが、正しい抽出への近道です。

 

数式を結果セルへ入力

H2セルを選択して、売上を返すXLOOKUP関数を入力します。

数式は、=XLOOKUP(F2&”|”&G2,A$2:A$100&”|”&B$2:B$100,D$2:D$100,”該当なし”)です。

データ範囲の行番号に$を付けておけば、結果用の数式を下方向へコピーしても元データの範囲はずれません。

一方、F2やG2には$を付けないことで、条件を行ごとに変えながら検索できます。

絶対参照と相対参照を使い分けることで、オートフィル後の数式エラーを防げます。

 

オートフィルによる複数行検索

複数の条件セットをまとめて検索したい場合は、F列とG列に条件を複数行入力します。

H2に数式を入力した後、セル右下のフィルハンドルを下へドラッグしてコピーします。

各行のF列とG列を参照して、対応する売上がH列へ表示されます。

検索件数が多い場合は、返り値の列を増やして販売数、売上、受注日などを横方向に取得することもできます。

ただし、同じ式を何千行にも設定する場合は、列全体参照を避け、実際のデータ量に応じた範囲を指定することが処理速度の面で大切です。

 

三つ以上の条件を指定する数式

続いては、商品名、担当者、年月のように三つ以上の条件を使う検索方法を確認していきます。

年月	商品名	担当者	売上
2026/4	ノートPC	田中	1440000
2026/5	ノートPC	田中	1200000

 

三条件を連結する検索式

三つの条件でも、二条件の場合と同じようにすべてを連結します。

F2に年月、G2に商品名、H2に担当者を入力し、I2に売上を表示する例で考えます。

この式では、年月、商品名、担当者のすべてが一致する行だけが対象です。

条件を追加するたびに、検索値側と検索範囲側の両方へ同じ順番で加えることを忘れないでください。

 

日付と文字列の表示形式

Excelの日付はシリアル値として保存されているため、見た目が同じでも時刻を含むかどうかで一致しない場合があります。

月単位で検索したい場合は、TEXT関数で年月の形式をそろえてから連結すると安定します。

TEXT関数により、検索値側も元データ側もyyyy/mという同じ文字列に変換されます。

日付、時刻、数値、文字列が混ざる表では、連結前に表示形式を統一する発想が役立ちます。

 

数式入力画面のイメージ

三条件の数式は長くなりますが、数式バーで参照範囲を色分け表示しながら入力すると確認しやすくなります。

赤枠のI2セルを選択し、数式バーに式を入力したらEnterキーで確定します。

数式が正しく入力できれば、条件セルの値を変更するだけで結果も自動更新されます。

 

論理式を使った複数条件検索

続いては、連結ではなく論理式を使って複数条件を指定する方法を確認していきます。

部門	担当者	目標額
営業一課	田中	2000000
営業二課	田中	1800000

 

掛け算でAND条件を作る式

二つの条件をどちらも満たす行を検索する場合、比較式を掛け算で結びます。

F2がA列と一致するか、G2がB列と一致するかをそれぞれ判定し、両方が真の行だけが1になります。

TRUEは計算上1、FALSEは0として扱われるため、掛け算はAND条件として機能します。

XLOOKUP関数は1を検索し、該当行のC列にある目標額を返します。

 

足し算でOR条件を作る式

いずれかの条件に当てはまるデータを探したいときは、比較式を足し算で結びます。

ただし、二つの条件が両方とも真の場合は結果が2になるため、検索値と扱い方には注意が必要です。

比較結果の合計が0より大きいかを判定することで、いずれかに一致した行をTRUEとして扱えます。

OR条件は複数候補のうち最初の行を返すため、すべての一致データを抽出したい用途にはFILTER関数のほうが適しています。

 

連結式と論理式の使い分け

条件が完全一致の文字列やコードで構成されるなら、連結式は読みやすく、数式も理解しやすい方法です。

一方で数値の大小比較、日付の範囲指定、空白を除外する条件などを使うなら、論理式の方法が柔軟です。

たとえば売上が100万円以上かつ営業一課という条件では、比較演算子を使った論理式が適します。

XLOOKUPの複数条件検索は、検索配列に1と0の判定結果を作る考え方を理解すると応用しやすくなります。

 

エラーと表記ゆれへの対処

続いては、XLOOKUP関数で複数条件検索がうまくいかないときの原因と対処を確認していきます。

表示結果	主な原因	対処
該当なし	表記ゆれ	空白や全角半角を確認
エラー	範囲の大きさが異なる	開始行と終了行を統一

 

該当なしを任意の表示へ変更

XLOOKUP関数の四つ目の引数には、見つからない場合に表示する値を指定できます。

空欄にしたい場合は、二重引用符を指定します。

検索フォームでエラー表示を見せたくない場合には便利ですが、入力漏れと検索失敗を区別しにくくなることがあります。

通常は、該当なしやデータ未登録など、原因を判断できるメッセージを指定するとよいでしょう。

 

空白文字と全角半角の確認

見た目が同じでも、セルの先頭や末尾に空白があると検索結果は一致しません。

コピーしたデータには、通常の半角スペース以外に改行や特殊な空白が含まれることもあります。

TRIM関数で前後の余分な空白を整える方法が有効です。

また、半角カタカナ、全角英数字、旧字体などの表記ゆれはTRIM関数だけでは解決しません。

検索元と検索条件の入力ルールを統一することが、根本的な再発防止になります。

 

検索範囲と戻り範囲の行数

XLOOKUP関数では、検索範囲と戻り範囲の大きさをそろえる必要があります。

検索範囲がA2:A100とB2:B100なのに、戻り範囲だけD2:D99になっていると正しく処理できません。

表の途中に空行があっても範囲の行数が同じなら問題ありませんが、範囲指定のずれには注意が必要です。

元データをExcelテーブルとして管理すれば、追加行に合わせて参照範囲を自動拡張できます。

テーブル名が売上一覧なら、構造化参照を使った数式も作成できます。

 

複数条件検索を使う実務例

続いては、複数条件のXLOOKUP関数を実際の管理表で活用する場面を確認していきます。

顧客コード	商品コード	単価
C001	P100	12000
C001	P200	8500

 

顧客別単価の取得

顧客ごとに同じ商品の単価が異なる場合、顧客コードと商品コードを組み合わせて単価表から取得できます。

見積書や受注入力表で単価を自動表示できれば、入力ミスの削減につながります。

F2に顧客コード、G2に商品コードを入力する構成です。

顧客コードと商品コードは名称より表記が安定しやすいため、マスタ検索の条件として適しています。

 

担当者別月次実績の参照

月次の販売実績表では、年月と担当者を条件にして目標額や達成率を取得できます。

月を切り替えるだけで担当者ごとのデータを表示するダッシュボードにも応用可能です。

年月を日付として持つ場合は、月初日で統一するか、TEXT関数で年月表示をそろえる方法を選びます。

データの粒度をそろえてから検索条件を設計することが重要です。

 

在庫表と発注表の照合

倉庫と商品コードを条件にすれば、複数倉庫にまたがる在庫表から対象商品の在庫数を取り出せます。

発注予定表の倉庫コードと商品コードをキーにして、在庫数や発注点を参照する運用です。

在庫数が空欄の場合と、在庫がゼロの場合は意味が違うため、見つからない場合の表示を空欄にしないほうが判断しやすいケースがあります。

検索結果のゼロと未登録を区別する表示は、発注判断の精度にも関わります。

 

まとめ エクセルで複数条件を指定するXLOOKUP関数

エクセルでXLOOKUP関数に複数条件を指定する場合は、検索値と検索範囲を同じ順序で連結する方法が基本です。

商品名と担当者の二条件なら、検索値を商品名&担当者、検索範囲を商品名列&担当者列として作成します。

条件の境界が曖昧になる可能性を避けるため、縦棒などの区切り文字を間に入れて連結する方法が実務では安心です。

三条件以上でも同じ考え方で条件を追加でき、年月、商品名、担当者のような複合キーを使った抽出にも対応できます。

数値の比較や日付範囲のような条件を含める場合は、比較式を掛け算で結ぶ論理式も活用できます。

検索できないときは、空白、全角半角、日付形式、検索範囲と戻り範囲の行数を順番に確認しましょう。

入力規則やExcelテーブルを組み合わせれば、表記ゆれや範囲漏れを減らし、検索式を長く安定して使えます。

XLOOKUP関数の複数条件検索を身につけて、商品管理、顧客別単価、担当者別実績、在庫照合などの作業を効率化していきましょう。