【Excel】エクセルのXLOOKUPで複数条件を検索する方法（条件に合う値を取得）

Excelで商品コードだけでは対象を絞り込めず、担当者や支店、年月などの複数条件を組み合わせて値を取得したい場面は少なくありません。

XLOOKUPは検索値と検索範囲を自由に扱える関数のため、補助列を使う方法や条件式を掛け合わせる方法を使えば、複数条件検索にも対応できます。

この記事では、1行目に見出しがある表を前提に、条件に一致する単価、売上、担当者名などを取得する数式と、実務で間違えやすいポイントを詳しく解説します。

 

XLOOKUPで複数条件を検索する基本操作

それではまず、補助列を使わずに複数条件に合う値を取得する基本的な数式について解説していきます。

A	B	C	D
支店	商品コード	月	売上
東京	A-101	4月	125000
大阪	A-101	4月	98000

 

条件式を掛け合わせる数式の作成

複数条件でXLOOKUPを使う代表的な方法は、それぞれの条件式を掛け合わせ、すべての条件を満たす行だけを1にする方法です。

たとえば、G2に支店名、H2に商品コード、I2に月を入力し、該当する売上をJ2へ表示したい場合を考えます。

売上データがA列からD列にあり、2行目から100行目まで入力されているなら、J2には次の数式を入力します。

この数式では、A列がG2と一致するか、B列がH2と一致するか、C列がI2と一致するかを個別に判定しています。

各判定は一致した行でTRUE、不一致の行でFALSEとなりますが、計算時にはTRUEが1、FALSEが0として扱われます。

そのため、3つの条件を掛け合わせた結果は、すべてが一致する行だけ1になります。

XLOOKUPの検索値を1にすることで、複数条件をすべて満たした行を検索できる仕組みです。

検索範囲には、条件式を掛け合わせた計算結果を指定します。

戻り範囲には、取得したい売上のD列を指定します。

最後の「該当なし」は、条件に合うデータがないときに表示する文字列です。

この引数を省略すると、該当データがない場合に#N/Aエラーが表示されます。

入力用セルに支店、商品コード、月を分けて配置しておけば、条件を変更するたびに結果を自動更新できます。

受注管理表、在庫一覧、支店別実績表など、条件を組み合わせて参照する業務で便利な形です。

 

検索値と検索範囲の役割

XLOOKUP関数は、検索値、検索範囲、戻り範囲の順に指定する関数です。

通常は検索値に商品コードなどを直接指定しますが、複数条件検索では検索値に1を指定する点が大きな特徴です。

検索範囲はセル範囲そのものではなく、条件判定を計算した配列になります。

数式内の(A2:A100=G2)は、A列の各セルとG2を比較して、縦方向にTRUEまたはFALSEを返します。

同様に、(B2:B100=H2)と(C2:C100=I2)も、各行について判定結果を返します。

これらを掛けると、東京、A-101、4月の3条件がそろう行だけ1となり、それ以外の行は0になります。

AND関数のように見えますが、範囲ごとに行単位で判定できることがこの書き方の利点です。

戻り範囲のD2:D100には、検索に成功した行の売上が入っています。

検索範囲で最初に見つかった1と同じ位置の値が、D列から返される仕組みです。

複数の条件に一致するデータが複数行ある場合、XLOOKUPは原則として上から最初の値を返します。

たとえば同じ支店、同じ商品、同じ月の売上が複数回記録されている場合、合計売上を出す用途には向きません。

合計したい場合はSUMIFS関数、複数行を一覧で取得したい場合はFILTER関数を検討しましょう。

また、検索対象に空白セルがあると、入力セルも空白のときに意図しない行が一致することがあります。

検索条件の入力欄には、必要な条件をすべて入力してから結果を確認する運用が安全です。

 

補助列を使った複数条件検索

続いては、複数の条件を連結した補助列を作り、わかりやすく検索する方法を確認していきます。

A	B	C	E
支店	商品コード	月	検索キー
東京	A-101	4月	東京|A-101|4月

 

連結キーを作る手順

補助列を使う方法では、元データの右側に検索用のキーを作成します。

たとえばE列を検索キーとして使うなら、E1には検索キーという見出しを入力します。

E2には、支店、商品コード、月を区切り記号付きで連結する数式を入力します。

入力後、E2の右下にあるフィルハンドルを下へドラッグし、データ最終行まで数式をコピーします。

これにより、東京、A-101、4月という3条件は、東京|A-101|4月という1つの検索キーになります。

区切り記号を入れることで、異なる値の組み合わせが偶然同じ文字列になるリスクを下げられます。

たとえば「AB」と「C」という組み合わせと、「A」と「BC」という組み合わせを単純につなぐと、どちらもABCになります。

そのため、複数条件を連結するときは、元データに含まれにくい縦棒やアンダースコアを区切りとして使うのが実用的です。

日付をキーに使う場合は、表示形式ではなく内部のシリアル値の違いに注意が必要です。

日付の表記を統一したいなら、TEXT関数で年月日を固定形式の文字列に変換してから連結します。

補助列は、数式の意味を後から確認しやすく、関数に不慣れな人が引き継ぐファイルでも扱いやすい方法です。

列を増やせる設計であれば、まず補助列方式を選ぶとトラブルを見つけやすくなります。

 

連結キーを使うXLOOKUPの入力

補助列を作成したら、検索する側でも同じ規則で検索キーを作ります。

G2に支店、H2に商品コード、I2に月を入力し、J2で売上を表示する場合を考えます。

J2には、次のようにXLOOKUP関数を入力します。

検索値はG2、H2、I2を縦棒でつないだ文字列です。

検索範囲は元データ側で作ったE列の検索キーであり、戻り範囲は取得したい売上のD列です。

補助列を使うと、XLOOKUPの構造が通常の1条件検索とほぼ同じになるため、数式を読み解きやすくなります。

複数の検索結果を別の行へコピーする場合、元データの検索範囲と戻り範囲は絶対参照にすることが重要です。

$E$2:$E$100のようにドル記号を付けると、数式を下へコピーしても検索対象がずれません。

一方、G2、H2、I2は行ごとに検索条件を変えたい場合が多いため、通常は相対参照のままにします。

データ件数が増える可能性がある場合は、範囲を広めに指定するか、元データをテーブル化する方法もあります。

テーブル化すると新しい行を追加しても検索範囲が自動で広がり、数式を修正する頻度を減らせます。

検索キー列を非表示にすれば、利用者には見せずに計算用の列として管理することも可能です。

ただし非表示列でも値の誤りは結果に影響するため、データ更新時にはキー列の数式が最終行までコピーされているか確認しましょう。

 

条件式とXLOOKUPを組み合わせる検索例

続いては、補助列を増やさず、条件式を利用して検索条件を柔軟に追加する方法を確認していきます。

A	B	C	D
部署	担当者	年度	予算
営業	田中	2026	500000

 

三つ以上の条件を追加する数式

条件式を使う方法では、検索条件を増やすときに、判定式をさらに追加して掛け合わせます。

たとえば、部署、担当者、年度の3条件で予算を取得するなら、次の数式を利用できます。

4条件に増やしたい場合は、4つ目の条件式を末尾へ追加するだけです。

条件がすべて満たされる行だけが1になるため、検索値の1は変わりません。

条件を増やす場合でも、各条件範囲の行数を同じに保つことが最も大切です。

条件式を直接記述する方式は、検索キー列を追加できない共有帳票や、列構成を変えたくない場合にも適しています。

一方で、数式が長くなるため、条件が多い場合は改行を使って数式バーで確認すると見やすくなります。

数式内の各条件を選択してF9キーを押すと、計算結果の配列を一時的に確認できます。

ただしF9で確認した後にEscキーを押さずEnterキーを押すと、数式が結果の値に置き換わることがあります。

検証後はEscキーで戻る操作を意識しましょう。

 

空白条件と部分一致を扱う方法

検索条件の一部を空白にしたとき、すべての値を対象にしたいケースもあります。

たとえばG2の部署が空白なら部署を絞り込まず、H2とI2だけで検索したい場合があります。

この場合は、入力セルが空白かどうかを判定する条件を加えます。

G2が空白なら、(G2=””)の結果がTRUEとなり、部署の条件はすべての行で通過します。

G2に部署名が入っているときは、A列との一致判定が必要になります。

任意条件を作れる一方で、同じ条件に複数行が該当しやすくなるため、先頭の値だけでよいか確認が必要です。

文字列の一部を条件にしたい場合は、ワイルドカードを使う方法があります。

商品コードに「A-」を含むデータを探す場合は、検索値を”*A-*”のように指定し、XLOOKUPの一致モードにワイルドカード一致を指定します。

ただし複数条件の条件式では、SEARCH関数やCOUNTIF関数を使った判定のほうが式を組み立てやすい場合があります。

部分一致は、完全一致よりも意図しないデータを拾う可能性があります。

支店名や担当者名のように似た文字列が存在する列では、完全一致を基本にするのが安全です。

空白を条件から外す場合も、検索結果が複数あるならXLOOKUPではなくFILTER関数へ切り替える判断が求められます。

 

複数条件検索のエラー対処と関数の使い分け

続いては、XLOOKUPで複数条件を検索したときに表示されるエラーと、用途別の関数の使い分けを確認していきます。

確認項目	主な原因	対処
該当なし	条件の表記違い	空白や全角半角を確認
#VALUE!	範囲サイズの不一致	開始行と終了行を統一

 

該当なしと範囲不一致の確認

複数条件のXLOOKUPで結果が返らない場合は、最初に条件セルと元データの表記を確認します。

見た目が同じでも、セルの末尾に半角スペースが入っていたり、全角と半角が混在していたりすると一致しません。

担当者名を別システムから貼り付けた場合には、前後の不要な空白が含まれることがあります。

TRIM関数で余分な空白を削除し、必要に応じてASC関数やJIS関数で文字種を整える方法が役立ちます。

日付は表示形式が同じでも、片方が文字列で片方が日付データなら一致しないため注意が必要です。

また、検索範囲と戻り範囲の大きさが異なると、正しく検索できません。

たとえば条件範囲をA2:A100にしたなら、B列やC列の条件範囲、戻り範囲も原則として2行目から100行目までにそろえます。

A2:A100に対してD2:D99を指定するようなずれがあると、エラーまたは誤った結果の原因になります。

数式を編集した際に一部の範囲だけ変更してしまうことも多いため、数式バーで各参照範囲を順番に確認しましょう。

「該当なし」という引数を設定しておけば、#N/Aエラーを利用者に見せず、検索条件が見つからない状態をわかりやすく伝えられます。

ただし本来あるはずのデータが該当なしになる場合は、エラーを隠すだけで終わらせず、条件とデータ型を確認することが必要です。

 

SUMIFSとFILTER関数の選択

XLOOKUPは、複数条件に一致するデータから1つの値を取り出す用途に適した関数です。

しかし、目的によっては別の関数のほうが自然に処理できます。

同じ条件に一致する売上をすべて合計したいなら、SUMIFS関数を使います。

SUMIFSは、東京支店かつA-101かつ4月のように、複数条件に一致する数値を合算します。

毎日の売上明細のように同じ条件が複数行あるデータでは、XLOOKUPよりSUMIFSのほうが目的に合います。

一方、一致したすべての明細を表示したいなら、FILTER関数が便利です。

FILTER関数は複数の該当行を一覧表示できるため、検索結果を確認する作業に向いています。

商品マスタや社員マスタのように、条件の組み合わせが一意である表では、XLOOKUPが使いやすい選択です。

売上明細や出勤記録のように、一意ではない表では、SUMIFSやFILTERを使うと集計目的に合った結果を得られます。

関数を選ぶ前に、条件に一致する行が必ず1件なのか、複数件あり得るのかを確認しましょう。

 

まとめ エクセルのXLOOKUPで複数条件を検索する方法

エクセルのXLOOKUPで複数条件を検索するには、各条件式を掛け合わせて一致行だけを1にする方法が便利です。

=XLOOKUP(1,(条件範囲1=条件1)*(条件範囲2=条件2),戻り範囲,”該当なし”)という形を覚えると、支店、商品、日付、担当者などを組み合わせた検索に応用できます。

補助列を使える表なら、条件を連結した検索キーを作る方法も、見通しがよく管理しやすい選択です。

検索範囲と戻り範囲の行数をそろえ、空白、日付形式、全角半角、重複データを確認すると、検索エラーを減らせます。

一致する値が複数ある場合は、XLOOKUPが最初の1件を返すことも押さえておきましょう。

合計を求めるならSUMIFS、すべての該当データを表示するならFILTER関数を使うと、目的に合った表を作成できます。