エクセルでデータを検索するときに「XLOOKUP関数で複数の条件を同時に指定したい」「2つ以上のキーを組み合わせて検索したい」「VLOOKUPより柔軟に複数条件検索する方法を知りたい」と感じたことはありませんか？
XLOOKUP関数はVLOOKUPの後継として登場した強力な検索関数ですが、標準では1つの検索値しか指定できません。ただし列を結合する方法や配列数式を使うことで、複数条件での検索が実現できます。
この記事では【Excel】エクセルのXLOOKUP関数で複数条件を指定する方法（2つ以上・列結合・配列数式）について解説していきます。
ポイントは
・列結合（&演算子）を使った複数条件検索の方法
・配列数式を使って補助列なしで複数条件検索する方法
・3つ以上の条件を指定する方法と注意点
です。
それでは詳しく見ていきましょう。
XLOOKUP関数の基本的な書き方とVLOOKUPとの違い
まずXLOOKUP関数の基本を確認しておきましょう。XLOOKUP関数はExcel 2021およびMicrosoft 365で使える検索関数で、VLOOKUPよりも柔軟で使いやすい特徴があります。
XLOOKUP関数の書き方=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
・検索値：検索するキーとなる値
・検索範囲：検索値を探す列（または行）の範囲
・戻り範囲：一致した行（または列）から返したい値の範囲
・見つからない場合：一致しないときに返す値（省略可）
・一致モード：0=完全一致（既定）、-1=未満、1=超過、2=ワイルドカード
・検索モード：1=先頭から（既定）、-1=末尾から、2=昇順バイナリ、-2=降順バイナリ
VLOOKUPと比べたXLOOKUP関数の主なメリットは、戻り範囲が検索列の右側に限定されない点（左側の列も参照できる）、見つからない場合の処理を関数内で指定できる点、そして複数の値を一度に返せる点などが挙げられます。
| A | B | C | D | E | |
| 1 | 担当者 | 部署 | 商品 | 地域 | 売上 |
| 2 | 田中 | 営業部 | 食品 | 東京 | 85,000 |
| 3 | 鈴木 | 総務部 | 日用品 | 大阪 | 62,000 |
| 4 | 佐藤 | 営業部 | 食品 | 大阪 | 97,000 |
| 5 | 山田 | 開発部 | 食品 | 東京 | 74,000 |
| 6 | 伊藤 | 営業部 | 日用品 | 東京 | 110,000 |
| 7 | 渡辺 | 総務部 | 食品 | 大阪 | 53,000 |
※1行目にヘッダー、2行目以降にデータが入力されている構成です
このサンプルデータで単純な1条件検索の場合、「担当者が田中の売上」を求めるにはXLOOKUP関数を次のように使います。
=XLOOKUP(“田中”,A2:A7,E2:E7,”該当なし”)
A2:A7（担当者列）から「田中」を検索し、一致した行のE2:E7（売上列）の値を返します。見つからない場合は「該当なし」と表示されます。
XLOOKUP関数で列結合（&演算子）を使った複数条件検索の方法
XLOOKUP関数で複数条件を指定する最もシンプルな方法は「検索値と検索範囲の両方で複数の列を&（アンパサンド）でつなぐ」方法です。補助列を作らずに数式内だけで完結できるため、表の構造を変更する必要がありません。
たとえば「担当者が田中、かつ商品が食品」の売上を検索する場合は次のように入力します。
=XLOOKUP(G2&H2,A2:A7&C2:C7,E2:E7,”該当なし”)
「G2&H2」が検索値（担当者と商品を結合した文字列）、「A2:A7&C2:C7」が検索範囲（担当者列と商品列を結合した配列）です。G2に「田中」、H2に「食品」と入力されていれば「田中食品」という検索値でA列とC列を結合した配列から一致する行を探し、E列の売上を返します。
fx
| G | H | I | |
| 1 | 担当者 | 商品 | 売上（検索結果） |
| 2 | 田中 | 食品 | 85,000 ← 結果 |
| 3 | 佐藤 | 食品 | 97,000 |
| 4 | 山田 | 日用品 | 該当なし |
※G列・H列の条件を変えると自動的に検索結果が更新されます。山田×日用品は該当データなしのため「該当なし」と表示
この方法では「田中食品」という結合された文字列を検索しています。もし「田中」という担当者が「食品」と「日用品」の両方を担当していた場合、最初に一致した「田中食品」の行の売上が返されます。
注意点として、&演算子で列を結合するとテキストとして扱われるため、数値や日付の条件では意図しない一致が起きることがあります。文字列の条件が中心の場合に特に向いている方法です。
XLOOKUP関数で配列数式を使って複数条件検索する方法【補助列なし】
列結合の方法とは別に、配列数式を使ってより柔軟に複数条件検索をする方法があります。この方法は検索範囲の列を直接掛け算（AND条件）または足し算（OR条件）で組み合わせるため、補助列も列結合も不要で数式内だけで完結します。
「担当者が田中、かつ商品が食品」の売上を配列数式で求める場合は次のように入力します。
=XLOOKUP(1,(A2:A7=”田中”)*(C2:C7=”食品”),E2:E7,”該当なし”)
「(A2:A7=”田中”)」はA列が田中に一致する行をTRUE/FALSEの配列で返します。「(C2:C7=”食品”)」も同様にC列が食品に一致する配列を返します。この2つを「*（掛け算）」でつなぐと、両方がTRUE（＝1）の行だけが1になり、どちらかがFALSEの行は0になります。XLOOKUP関数の検索値を「1」にすることで、この配列から「1」が最初に出現する行を見つけ、その行のE列の売上を返します。
OR条件で複数条件検索する方法
OR条件（どちらか一致）で検索したい場合は「+（足し算）」と「>0」を組み合わせます。「担当者が田中または商品が食品」の最初に一致する行の売上を求めるには次のように書きます。
=XLOOKUP(1,((A2:A7=”田中”)+(C2:C7=”食品”)>0)*1,E2:E7,”該当なし”)
「(A2:A7=”田中”)+(C2:C7=”食品”)」でOR条件を評価し、「>0」でTRUE/FALSEに変換して「*1」で数値化しています。最初に条件を満たす行の売上が返されます。
| A列（担当者） | C列（商品） | A=”田中” | C=”食品” | 掛け算（AND） | |
| 2 | 田中 | 食品 | TRUE | TRUE | 1 ← 最初に発見 |
| 3 | 鈴木 | 日用品 | FALSE | FALSE | 0 |
| 4 | 佐藤 | 食品 | FALSE | TRUE | 0 |
| 5 | 山田 | 食品 | FALSE | TRUE | 0 |
※AND条件の配列で最初に「1」が出現する行（2行目）の売上85,000円がXLOOKUPで返されます
XLOOKUP関数で3つ以上の条件を指定する方法
3つ以上の条件を指定する場合も、同じ「*（掛け算）」でつなぐ方法が使えます。「担当者が田中、かつ商品が食品、かつ地域が東京」の売上を求める場合は次のように書きます。
=XLOOKUP(1,(A2:A7=”田中”)*(C2:C7=”食品”)*(D2:D7=”東京”),E2:E7,”該当なし”)
3つの条件式を「*」でつなぐだけで、すべての条件を同時に満たす行を検索できます。条件の数が増えても同じパターンで「*(条件範囲=条件値)」を追加していくだけです。
列結合の方法でも3つ以上の条件に対応できます。「担当者、商品、地域」の3つを条件にする場合は次のように書きます。
=XLOOKUP(G2&H2&I2,A2:A7&C2:C7&D2:D7,E2:E7,”該当なし”)
G2に「田中」、H2に「食品」、I2に「東京」と入力されていれば「田中食品東京」という結合文字列で検索が行われます。
条件が多くなると列結合の方法は文字列の意図しない一致が起きるリスクが高まります。3つ以上の条件を扱う場合は配列数式の「*」でつなぐ方法の方がより安全で確実です。
XLOOKUP関数の複数条件検索に関するよくある疑問とトラブル対処法
XLOOKUP関数の複数条件検索でよくつまずくポイントと対処法を確認しておきましょう。
「#N/A」エラーが出る場合
XLOOKUP関数で「#N/A」エラーが出る場合は検索値が見つからなかったことを意味します。第4引数（見つからない場合）を指定していない場合に表示されます。
「”該当なし”」や「0」など適切な値を第4引数に指定するか、条件の文字列がデータと一致しているか確認しましょう。全角・半角・スペースの違いが原因のことが多いです。
配列数式で「#VALUE!」エラーが出る場合
配列数式で「#VALUE!」エラーが出る場合は条件範囲と戻り範囲の行数が一致していない可能性があります。すべての範囲が同じ行数になっているか確認しましょう。
またExcel 2019以前では配列数式を「Ctrl+Shift+Enter」で確定する必要があります。通常のEnterで入力してエラーになる場合は「Ctrl+Shift+Enter」で入力し直してみましょう。
XLOOKUP関数が使えないバージョンの場合
XLOOKUP関数はExcel 2021およびMicrosoft 365でのみ使えます。
Excel 2019以前をお使いの場合はXLOOKUP関数は使えません。代替手段としてINDEX+MATCH関数の配列数式か、補助列+VLOOKUP関数を使う方法が利用できます。
まとめ エクセルのXLOOKUP関数で複数条件を指定する方法（配列数式・列結合・2つ以上）
エクセルのXLOOKUP関数で複数条件を指定する方法をまとめると
・列結合（&演算子）：=XLOOKUP(G2&H2, A2:A7&C2:C7, 戻り範囲, “該当なし”)で検索値と検索範囲の両方を結合
・配列数式（AND条件）：=XLOOKUP(1,(条件範囲1=条件1)*(条件範囲2=条件2), 戻り範囲, “該当なし”)で条件式を*でつなぐ
・配列数式（OR条件）：条件式を+でつなぎ「>0」で判定してから*1で数値化
・3つ以上の条件：配列数式の*をさらに追加するだけで対応可能
・使用できるバージョン：Excel 2021・Microsoft 365のみ（それ以外はINDEX+MATCHを使用）
シンプルに書きたい場合は列結合の方法、より安全に複数条件を扱いたい場合や3つ以上の条件が必要な場合は配列数式の方法を選ぶのがおすすめです。
XLOOKUP関数の複数条件検索をマスターして、エクセルでのデータ検索・参照をさらに効率よく進めていきましょう。
コメント