ExcelでXLOOKUP関数の複数条件検索を行う方法

Excelで複数条件に一致する値を検索したいときは、XLOOKUP関数に「条件の一致判定」を組み合わせるのが分かりやすい方法です。

最初に結論を書くと、よく使う形は次の2つです。

この記事では、XLOOKUP関数で複数条件検索を行う基本形、3条件以上に増やす方法、うまくいかないときの確認ポイントまでまとめて解説します。

前提: XLOOKUP関数は主に Microsoft 365 と Excel 2021 以降で使えます。古いバージョンでは、後半のFAQにあるように INDEX / MATCH や補助列を使う方法を検討してください。

基本の考え方

XLOOKUP関数は本来、1つの検索値を使って1つの検索範囲から一致する位置を探す関数です。複数条件で使うときは、各条件が一致した行だけ 1 になる配列を作り、それを検索対象にします。

たとえば「商品名が A で、月が 4月の行から売上を取り出したい」なら、次のように考えます。

TRUE は 1、FALSE は 0 として扱われるため、この方法で複数条件検索ができます。

例として、A列に商品名、B列に月、C列に売上が入っている表から、H2 の商品名と H3 の月に一致する売上を返す場合の式は次の通りです。

=XLOOKUP(1,(A2:A20=H2)*(B2:B20=H3),C2:C20)

この式では、A列とB列の両方が一致した行だけ 1 になるので、XLOOKUP がその行の C列の値を返します。

下のスクリーンショットでは、元データの表と検索条件セル、そして複数条件の XLOOKUP 式を入力した結果を確認できます。

条件が3つ以上でも考え方は同じです。掛け算する条件を増やします。

=XLOOKUP(1,(A2:A20=H2)*(B2:B20=H3)*(D2:D20=H4),C2:C20)

たとえば、商品名・月・担当者の3条件で売上を取り出すような場合に使えます。

XLOOKUP関数は、基本的には最初に見つかった1件を返します。そのため、同じ条件に一致する行が複数ある場合は、XLOOKUPではなく FILTER関数の方が向いています。

=FILTER(C2:C20,(A2:A20=H2)*(B2:B20=H3))

一致する売上をすべて一覧で取り出したいなら、この形を使ってください。

#N/A が出るときは、まず検索条件セルの値と元データの値を見比べて、余分な空白、全角半角の違い、日付や数値の表示形式の差がないかを確認してください。一致行が複数あるケースでは、XLOOKUP は先頭の1件だけ返す点にも注意が必要です。

複数条件検索では、補助列に 商品名&月 のような結合キーを作って検索する方法もあります。

既存の表をあまり触りたくないときは、今回の方法の方が向いています。

考え方は同じです。行方向でも列方向でも、条件が一致した位置だけ 1 になる配列を作れれば使えます。

XLOOKUP関数は新しいExcelで使える関数です。古いバージョンでは INDEX / MATCH や補助列を使う方法を検討してください。

XLOOKUP関数そのものの基本は、XLOOKUP関数の詳細解説(Excel) も参考になります。複数一致を一覧で返したい場合は、FILTER関数と複数条件設定【AND条件とOR条件の併用とまとめ】もあわせて確認してください。