XLOOKUP関数で複数の条件を指定するには、検索値と検索範囲をそれぞれ「&」でつなぎます。支店がE2、商品がF2なら =XLOOKUP(E2&F2,A2:A7&B2:B7,C2:C7) です。「〜以上」のような条件を含むときは、条件どうしを掛け算して「1」を探す =XLOOKUP(1,(条件1)*(条件2),戻り範囲) を使います。
【無料】XLOOKUPの複数条件を試せる練習用Excelファイル
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
2つの条件で検索する：検索値と検索範囲を「&」でつなぐ
XLOOKUPの検索値と検索範囲には、本来1つずつしか指定できません。そこで「支店」と「商品」を&でつなぎ、「大阪りんご」のような1つの文字列にしてから探します。検索範囲の側も同じように、支店の列と商品の列を1行ずつつないでおきます。
画像をタップすると拡大
サンプルの「まず試す」シートでは、A2:C7が元の表、E2が支店、F2が商品です。G2に次の式を入れると、大阪のりんごの売上「150」が表示されます。
第4引数の "該当なし" は、条件に合う行がないときの表示です。省略すると #N/A エラーになります。空白や0を表示したい場合はXLOOKUPで見つからない場合の表示を変える方法をご覧ください。
3つ以上の条件も「&」を増やすだけ
条件が3つ、4つと増えても考え方は同じです。検索値と検索範囲に、同じ順番で&を1つずつ足していきます。「応用」シートでは、支店・商品・月の3つで売上を探しています。
検索値と検索範囲は、必ず同じ順番でつなぎます。検索値が「支店&商品&月」なのに検索範囲が「商品&支店&月」だと、文字列が一致せず見つかりません。
サンプルの「まず試す」シートで、F2を「みかん」に書き換えてみてください。G2が「90」に変われば成功です。
「〜以上」「〜以下」を含む条件は掛け算で指定する
&でつなぐ方法は「完全に一致する」条件にしか使えません。「売上が155以上」のような比較を含むときは、条件ごとにTRUE／FALSEを作り、掛け算します。TRUEは1、FALSEは0として計算されるので、すべての条件を満たす行だけが1になります。その「1」をXLOOKUPで探すのがポイントです。
画像をタップすると拡大
「〜以下」なら <=、「〜より大きい」なら > に変えるだけです。条件を3つ以上にしたいときは、*(条件3) のように掛け算を続けます。
OR条件（どれかに当てはまる）は足し算で指定する
「商品がみかん、または売上が150以上」のように、どれか1つでも当てはまればよい場合は、条件を足し算します。両方当てはまる行は2になるので、>0 でTRUE／FALSEに戻し、TRUEを探します。
別シートの表から複数条件で検索する
元の表が別のシートにある場合も、式の形は変わりません。範囲の前に「シート名!」が付くだけです。式の入力中に元の表のシート見出しをクリックして範囲を選べば、シート名は自動で入ります。
条件に合うデータが複数あるときはFILTER関数
XLOOKUPが返すのは、上から探して最初に見つかった1件だけです。「応用」シートでは、大阪のりんごは4月の150と5月の160の2件ありますが、XLOOKUPで支店と商品だけを条件にすると150しか出てきません。当てはまるものをすべて取り出したいときは、FILTER関数を使います。
FILTER関数の条件の書き方はFILTER関数の複数条件（AND・OR）、3つ以上の条件はFILTER関数で3つ以上の複数条件を指定する方法で詳しく解説しています。
うまく検索できないときの確認ポイント
&でつなぐ方法には、別々の値が偶然同じ文字列になってしまう落とし穴があります。たとえば「A1」と「23」も「A12」と「3」も、つなぐとどちらも「A123」です。この場合、正しい行とは違う行の値が返ってきます。品番や数字のコードをつなぐときは、間に「|」などの区切り文字をはさみます。
| 症状 | よくある原因 | 対処 |
|---|---|---|
| データはあるのに「該当なし」 | 前後の余分なスペース、全角と半角の違い | TRIM関数でスペースを削除する、全角・半角をそろえる |
| 数字のコードが一致しない | &でつなぐと数値は文字列になる。「003」（文字列）と 3（数値）は別物 | 表示形式ではなく、値そのものの形をそろえる |
| 違う行の値が返る | つないだ文字列が偶然同じになっている | 上の式のように「|」をはさむ |
| #VALUE! エラー | 検索範囲と戻り範囲の行数が違う | A2:A7 と C2:C7 のように同じ行数にする |
| #NAME? エラー | XLOOKUPがないバージョン（Excel 2019以前） | 下のINDEX＋MATCHを使う |
スペースの削除は空白（スペース）を削除する方法、全角・半角は全角⇔半角を一括変換する方法、先頭の0の扱いは先頭の0が消えるときの対処法が参考になります。
XLOOKUPが使えない場合はINDEX＋MATCH
XLOOKUP関数が使えるのは、Microsoft 365、Excel 2021、Excel 2024、Web版のExcelです。Excel 2019以前では #NAME? エラーになるため、INDEX関数とMATCH関数を組み合わせます。サンプルの「まず試す」シートのG5に入っています。
INDEX＋MATCHでの複数条件は、INDEX＋MATCHで複数条件検索する方法で3条件の例まで詳しく解説しています。
自分の表に使う前に、サンプルで動きを確かめておくと安心です。
まとめ
| やりたいこと | 数式の形 |
|---|---|
| 2つの条件 | =XLOOKUP(E2&F2,A2:A7&B2:B7,C2:C7) |
| 3つ以上の条件 | =XLOOKUP(G2&H2&I2,A2:A9&B2:B9&C2:C9,D2:D9) |
| 「〜以上」などを含む | =XLOOKUP(1,(条件1)*(条件2),戻り範囲) |
| どれかに当てはまる（OR） | =XLOOKUP(TRUE,((条件1)+(条件2))>0,戻り範囲) |
| 当てはまるものをすべて | =FILTER(戻り範囲,(条件1)*(条件2)) |
| XLOOKUPがない | =INDEX(戻り範囲,MATCH(1,INDEX((条件1)*(条件2),0),0)) |
XLOOKUPとIF関数を組み合わせて表示を切り替えたい場合はXLOOKUPとIFの組み合わせ、文字の一部で検索したい場合はXLOOKUPの部分一致も参考にしてください。関数の仕様はMicrosoftのXLOOKUP関数の説明でも確認できます。
コメント