XLOOKUPで複数条件を検索する方法｜2条件・3条件・複数結果も解説

こんにちは、SkillStack Lab運営者のスタックです。

ExcelのXLOOKUPで、「店舗名と商品名」「社員番号と対象年月」のように、2つ以上の条件をすべて満たすデータを検索したいことはありませんか。

結論から言うと、A列が店舗名、B列が商品名、D列が単価で、H2の店舗とI2の商品に一致する単価を求めるなら、次の式で検索できます。

=XLOOKUP(1,($A$2:$A$1000=$H$2)*($B$2:$B$1000=$I$2),$D$2:$D$1000,"該当なし")

ポイントは、それぞれの条件が一致した行をTRUE＝1、不一致をFALSE＝0として計算し、すべての条件が一致して「1」になる行をXLOOKUPで探すことです。

文字列を&で連結して検索する方法もありますが、実務ではデータ量、同じ条件に複数行が存在するか、他の担当者が数式を保守できるかまで考えて方法を選ぶことが重要です。

この記事では、元情シス・現役管理部門長の視点から、XLOOKUPで2条件・3条件を指定する数式、文字列結合との違い、複数の該当行をすべて取り出す方法、#N/A・#SPILL!対策、重くなったときの改善方法まで実務ベースで解説します。

XLOOKUPはExcel 2016・Excel 2019では利用できません。Microsoft 365やExcel 2021以降など、XLOOKUPに対応したExcel環境を使用してください。

XLOOKUPで複数条件を指定する基本式

まずは、実務で最も使いやすい配列演算を使った方法から確認しましょう。

次のような価格マスターがあるとします。

A列：店舗	B列：商品	C列：年度	D列：単価	E列：在庫
東京	商品A	2026	1,200	25
東京	商品B	2026	980	10
大阪	商品A	2026	1,150	18

H2に検索する店舗、I2に商品名を入力し、一致する単価を取得します。

2つの条件をAND検索する

=XLOOKUP(1,($A$2:$A$1000=$H$2)*($B$2:$B$1000=$I$2),$D$2:$D$1000,"該当なし")

この式では、次の2条件を判定しています。

• A列の店舗＝H2の店舗
• B列の商品＝I2の商品

ExcelではTRUEを1、FALSEを0として計算できるため、条件式同士を*で掛けると次のようになります。

店舗条件	商品条件	掛け算	結果
TRUE	TRUE	1×1	1
TRUE	FALSE	1×0	0
FALSE	TRUE	0×1	0
FALSE	FALSE	0×0	0

つまり、両方の条件が一致した行だけ「1」になります。

XLOOKUPでその「1」を検索し、同じ行にあるD列の単価を返しているという仕組みです。

XLOOKUPは既定で完全一致検索を行うため、この例では一致モードを別途「0」に指定する必要はありません。

3つの条件で検索する

「店舗＋商品＋年度」の3条件にする場合も考え方は同じです。

J2に年度が入っているなら、次の式になります。

=XLOOKUP(1,($A$2:$A$1000=$H$2)*($B$2:$B$1000=$I$2)*($C$2:$C$1000=$J$2),$D$2:$D$1000,"該当なし")

4条件でも5条件でも、AND条件であれば同じ考え方で条件式を追加できます。

ただし、数式が長くなりすぎる場合は「書けるか」ではなく「他の担当者が読めるか」も判断基準にしてください。

Excelテーブルを使うと数式を読みやすくできる

実務で長く使う表なら、元データをExcelテーブルへ変換する方法をおすすめします。

価格マスターをtblPriceというテーブル名にすると、同じ式を次のように書けます。

=XLOOKUP(1,(tblPrice[店舗]=$H$2)*(tblPrice[商品]=$I$2),tblPrice[単価],"該当なし")

$A$2:$A$1000のようなセル番地だけの式より、「店舗」「商品」「単価」という意味が見えるため、後から数式を確認しやすくなります。

また、Excelテーブルへ新しい行を追加すると参照範囲も拡張されるため、「1000行を超えたら検索対象から漏れた」という事故を防ぎやすくなります。

XLOOKUPで複数条件を&で結合する方法

もう1つよく使われるのが、条件を&で連結して1つの検索値として扱う方法です。

店舗と商品を検索する場合は、次のように書けます。

=XLOOKUP($H$2&"|"&$I$2,$A$2:$A$1000&"|"&$B$2:$B$1000,$D$2:$D$1000,"該当なし")

検索値側では「東京|商品A」、検索範囲側でも「店舗|商品」という文字列を作り、一致する行を探します。

区切り文字を入れないと誤検索することがある

単純に次のように連結するのは避けた方が安全です。

=XLOOKUP(H2&I2,A2:A1000&B2:B1000,D2:D1000)

たとえば、

• 条件1＝「A」、条件2＝「AA」
• 条件1＝「AA」、条件2＝「A」

はいずれも単純連結すると「AAA」になります。

そこで、条件同士の間へ|などの区切り文字を入れます。

すると「A|AA」と「AA|A」に分かれるため、別の検索値として判定できます。

区切り文字を入れれば絶対に衝突しないわけではありません。元データ自体に「|」が含まれる可能性がある場合は別の記号を選ぶか、文字列連結ではなく条件配列を使ってください。

検索を何度も繰り返すなら補助列も候補

同じマスターに対して大量のXLOOKUPを繰り返す場合は、検索するたびに店舗列と商品列を連結するのではなく、マスター側へ検索キー用の補助列を作る方法もあります。

たとえばF2へ次の式を入れます。

=A2&"|"&B2

そのうえで検索式を次のようにします。

=XLOOKUP($H$2&"|"&$I$2,$F$2:$F$1000,$D$2:$D$1000,"該当なし")

検索キーをマスター側で一度作っておけば、同じ文字列連結を大量の検索式で繰り返す必要を減らせます。

さらに、補助列に「店舗＋商品」という名前を付ければ、後任者にも検索ロジックを説明しやすくなります。

条件に一致するデータが複数ある場合はFILTERを使う

ここはXLOOKUPの複数条件で特に間違えやすいポイントです。

XLOOKUPで条件を2つ指定しても、同じ条件に一致する行が複数存在する場合、基本的には最初に見つかった一致を返します。

たとえば、「東京店・商品A」の販売明細が10件あり、その10件をすべて抽出したい場合はXLOOKUPではなくFILTER関数が適しています。

=FILTER($A$2:$E$1000,($A$2:$A$1000=$H$2)*($B$2:$B$1000=$I$2),"該当なし")

Excelテーブルなら次のように書けます。

=FILTER(tblPrice,(tblPrice[店舗]=$H$2)*(tblPrice[商品]=$I$2),"該当なし")

やりたいこと	第一候補
条件に一致する最初の1件を取得	XLOOKUP
条件に一致する複数行をすべて取得	FILTER
1件について単価・在庫など複数列を取得	XLOOKUP

「複数条件」と「複数結果」は分けて考えましょう。

最後に登録された一致を取得することもできる

XLOOKUPには検索方向を指定する「検索モード」があります。

同じ条件が複数あり、一番下にある最新行を取得したい場合は検索モードを-1にします。

=XLOOKUP(1,($A$2:$A$1000=$H$2)*($B$2:$B$1000=$I$2),$D$2:$D$1000,"該当なし",0,-1)

ただし、「最終行＝最新」というルールが本当に保証されているか確認してから使用してください。

XLOOKUPで複数列を一括取得する方法

XLOOKUPは戻り範囲へ複数列を指定することもできます。

たとえば、条件に一致する「単価」と「在庫」を同時に返したい場合です。

=XLOOKUP(1,(tblPrice[店舗]=$H$2)*(tblPrice[商品]=$I$2),tblPrice[[単価]:[在庫]],"該当なし")

1つの数式だけで、単価と在庫が隣接セルへ展開されます。

これは「同じ条件に一致する複数行を返している」のではなく、一致した1行から複数列を返している点に注意してください。

#SPILL!が出た場合は展開先を確認する

複数列を返す数式などでは、結果が周囲のセルへ自動展開されます。この動きを「スピル」と呼びます。

展開予定のセルに別の値が入っていると、Excelは既存データを勝手に上書きせず、#SPILL!を表示します。

• 数式セルを選択して予定されるスピル範囲を確認する
• 展開先に値・数式・見えにくい文字がないか確認する
• 不要な値を削除するか別の場所へ移動する
• 結合セルがある場合は解除も検討する

スピルする動的配列数式は、Excelテーブルそのものの内部では使用できません。検索元データをテーブル化するのは有効ですが、スピル結果を返す数式はテーブル外の通常セルへ配置してください。

XLOOKUPの複数条件で起きるエラーと対策

数式の構造が正しくても、元データの状態によって検索できないことがあります。

エラー・症状	確認するポイント
#N/A	該当データがない・数値と文字列の違い・余分な空白
#VALUE!	条件範囲と戻り範囲のサイズ不一致など
#REF!	参照していた列・セルの削除など
#SPILL!	展開先の障害物・スピルできない位置
別の行を返す	同条件の重複・連結検索キーの重複

「見つからない場合」は第4引数で指定する

XLOOKUPには、検索値が見つからなかった場合に表示する内容を指定する引数があります。

=XLOOKUP(1,(条件1)*(条件2),戻り範囲,"該当なし")

空白にしたい場合は次のようにします。

=XLOOKUP(1,(条件1)*(条件2),戻り範囲,"")

ただし、第4引数は「一致する値が見つからなかった場合」への対策です。

参照そのものが壊れた#REF!や、範囲サイズが不正な#VALUE!まで隠してくれる機能ではありません。

何でもIFERRORで空白にすると、本来修正すべきデータ異常まで見えなくなるため注意してください。

見た目が同じでも数値と文字列は違う

「データがあるはずなのに一致しない」ときは、社員番号や商品コードのデータ型を確認してください。

見た目が同じ「1001」でも、Excelでは数値の1001と文字列の「1001」が異なる状態になっていることがあります。

基幹システムやCSVから取り込んだデータでは、先頭や末尾の不要な空白も確認しましょう。

#N/Aの原因を詳しく確認したい方は、VLOOKUP・XLOOKUPで#N/Aが出る原因と対処法も参考にしてください。

XLOOKUPの複数条件でExcelが重いときの改善策

複数条件XLOOKUPを使っただけで、必ずExcelが重くなるわけではありません。

一方、条件配列を大量のセルへコピーし、しかも対象範囲を必要以上に広くしている場合は、再計算するセル数が増えて動作が遅くなることがあります。

A:Aのような全列参照を避ける

次のような式は分かりやすく見えますが、配列計算で列全体を対象にするためおすすめしません。

=XLOOKUP(1,(A:A=H2)*(B:B=I2),D:D,"該当なし")

Excelの1列には100万行以上あるため、必要なデータだけを対象にします。

=XLOOKUP(1,($A$2:$A$10000=$H$2)*($B$2:$B$10000=$I$2),$D$2:$D$10000,"該当なし")

さらに管理しやすくするなら、先ほど紹介したExcelテーブルの構造化参照を使います。

重いときは補助列を使う方が分かりやすい場合もある

「1セルに全部書く方が高度」という考え方は捨てて構いません。

検索キー、データ型変換、エラーチェックなどを補助列へ分けた方が、計算内容を追いやすくなり、トラブル時の原因特定も簡単になる場合があります。

XLOOKUP・FILTER・Power Queryの使い分け

高度な数式を書けることと、XLOOKUPが最適であることは別です。

やりたいこと	第一候補
2～3条件から1件の値を参照する	XLOOKUP
条件に合う複数行を一覧で抽出する	FILTER
毎月同じ2つのデータを結合する	Power Query
CSVや複数ファイルを繰り返し整形・結合する	Power Query
単発の確認・分析を柔軟に行う	Excel関数
複数人で権限・履歴・承認まで管理する	SaaSも比較

たとえば、毎月「商品マスター」と「売上CSV」をXLOOKUPで何万行も照合する作業を繰り返しているなら、Power Queryで2つの表をマージする方法も検討できます。

Power Queryはデータの取得・整形・結合手順を残せるため、翌月はデータを入れ替えて更新する運用に向いています。

具体的な使い方は、Power Queryの実務的な使い方で解説しています。

関数、Power Query、VBA、Pythonなどのどれを使うべきか判断したい方は、Excel自動化の例7選と手段の使い分けも確認してください。

XLOOKUPを基礎から学び直したい場合

今回の数式をそのままコピーするだけでも検索できますが、勤務先のファイルでは列やデータ型、検索条件が異なります。

自分で数式を修正できるようになりたい方は、XLOOKUPの引数、完全一致、検索モード、絶対参照、エラーの読み方まで一度体系的に整理しておくと応用しやすくなります。

Udemyの「Excel VLOOKUP・HLOOKUP・XLOOKUPの基本からINDEX-MATCH関数までを学ぶ短期集中コース」では、VLOOKUP・HLOOKUP・XLOOKUPに加え、INDEX-MATCH、検索モード、データの一括取得、LOOKUP系関数で発生するエラーの解決方法まで扱っています。

＼ 関数を基礎から整理する ／

講座内容、価格、キャンペーン、対応環境などは変更される場合があります。受講前にUdemyの講座ページで最新情報を確認してください。

Excel関数全体をどの順番で覚えるべきか迷っている方は、Excel関数を暗記せず理解する学習方法も参考にしてください。

XLOOKUPの複数条件に関するよくある質問

XLOOKUPで2つの条件を指定できますか？

できます。たとえば=XLOOKUP(1,(A2:A1000=H2)*(B2:B1000=I2),D2:D1000,"該当なし")のように、条件式を掛け合わせて両方が一致する行を検索できます。

3つ以上の条件も指定できますか？

できます。3つ目の条件式をさらに*で掛け合わせます。ただし条件が増え、数式が読みづらくなる場合は補助列やPower Queryも検討してください。

複数条件に一致するすべての行を取得できますか？

XLOOKUPは基本的に最初に見つかった一致を返します。条件に一致する複数行をすべて一覧として取得したい場合はFILTER関数が適しています。

XLOOKUPで複数列をまとめて返せますか？

対応したExcelでは可能です。戻り範囲へ複数列を指定すると、1件の一致行から氏名と部署、単価と在庫など複数項目をまとめて返せます。

XLOOKUPで#N/Aが出るのはなぜですか？

該当データがないほか、数値と文字列の違い、余分な空白などでも一致しないことがあります。まず検索値とマスター側のデータが同じ形式になっているか確認してください。

XLOOKUPはExcel 2019でも使えますか？

Excel 2016およびExcel 2019ではXLOOKUPを利用できません。Microsoft 365、Excel 2021以降など、XLOOKUPに対応したExcel環境を使用してください。

複数条件XLOOKUPが重い場合はどうすればよいですか？

A:Aのような全列参照を避け、実際のデータ範囲やExcelテーブルを参照してください。同じ検索を大量に繰り返す場合は補助列、毎月同じデータ結合を行う場合はPower Queryも候補です。

まとめ｜XLOOKUPの複数条件は目的に合わせて使い分ける

XLOOKUPで2つの条件から1件を検索する基本式は次のとおりです。

=XLOOKUP(1,($A$2:$A$1000=$H$2)*($B$2:$B$1000=$I$2),$D$2:$D$1000,"該当なし")

• AND条件は条件式を*で掛け合わせる
• 3条件以上も同じ考え方で追加できる
• &で連結する場合は区切り文字を使う
• 複数の一致行を全部取得するならFILTERを使う
• 1件から複数列を返すならXLOOKUPで一括取得できる
• #N/Aはデータ型や空白も確認する
• 重い場合は全列参照を避け、テーブルや補助列を使う

高度な数式を書くことではなく、必要な結果を最も簡単で保守しやすい方法で取得することが実務では重要です。

一時的な検索ならXLOOKUP、複数行の抽出ならFILTER、毎月同じデータ結合ならPower Queryというように使い分けてください。

さらにVBA、Power Query、Python、SaaSまで含めて「今の作業に何を使うべきか」を判断したい方は、次の記事へ進んでください。

＼ 業務に合う方法を選ぶ ／

参考：Microsoft公式「XLOOKUP関数」／Microsoft公式「FILTER関数」