XLOOKUPで複数条件を組み合わせて使う方法

月次の売上報告で「担当者」と「商品名」を指定して金額を引きたいのに、XLOOKUPで複数条件をうまく扱えず、毎回フィルターで目視確認していると作業が止まってしまいます。
この記事では、XLOOKUPで複数条件を扱うときの考え方と、実務でそのまま使える設定手順をわかりやすく解説します。

XLOOKUPで複数条件を扱う基本の考え方

XLOOKUPは、本来「1つの検索値で1つの範囲を探す」関数です。
複数条件で使うときは、複数の条件を1つの検索キーにまとめるという発想に変えると、一気にわかりやすくなります。

実務の現場では、「日付＋商品」「社員番号＋年月」など、2〜3個の条件を組み合わせてデータを引きたい場面がとても多いです。
そのたびにフィルターで探したり、VLOOKUPを何段もネストすると、数が増えたときにミスしやすくなります。

複数条件を1つのキーにまとめる考え方（AND条件）

複数条件をAND条件（全部一致）で探したい場合、次のように考えます。

• 条件A（例：担当者）
• 条件B（例：商品）

この2つを文字列として連結し、「担当者｜商品」のような1本のキーを作ります。
たとえば、表側のキー列に

=B2 & "｜" & C2

のような式で「佐藤｜A商品」という文字列を作り、検索側でも同じルールで

=G2 & "｜" & H2

のようにキーを作ってXLOOKUPで照合します。

このように、「複数条件 → 合体させて1条件にする」と考えると、仕組みがシンプルに見えるようになります。

実務では、キーを作るときに区切り文字（ここでは「｜」）を必ず入れておくと、「佐藤A」と「佐藤AA」のような紛らわしい重なりを避けられるケースが多いです。

XLOOKUPの基本構文と複数条件への応用

XLOOKUPの基本構文は次のとおりです（日本語Excel）。

=XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード])

XLOOKUPは指定した範囲や配列の中から検索値を探し、最初に見つかった一致行の値を返します。
一致しない場合は、#N/Aエラーか、任意のメッセージを返すよう設定できます（出典：Microsoft サポート公式サイト）(マイクロソフト サポート)

複数条件で使うときも、基本形は同じです。

=XLOOKUP(複数条件を連結した検索値,
         複数条件を連結した検索範囲,
         戻り配列,
         [見つからない場合])

という形にします。
「検索値」と「検索範囲」の作り方だけ工夫する、というイメージです。

結論（最短で複数条件検索を設定する方法）

最短で複数条件XLOOKUPを動かしたい場合は、次の流れを押さえると迷いにくくなります。

1. 検索対象の表に「キー列」を1列追加する
2. キー列に「担当者&区切り文字&商品」のような式を入れる
3. 検索側のシートでも同じルールでキーを作る
4. XLOOKUPの「検索値」と「検索範囲」に、そのキーを指定する

特に、ヘルパー列（キー列）を1本追加するだけで、複数条件の仕組みが目で見て確認しやすくなるのがポイントです。
式のトラブルも、ほとんどがキー列の値を見れば原因を追いやすくなります。

用語の意味と前提を簡単に整理

ここで、よく出てくる用語を短く整理しておきます。

• 検索値：探したい側の値（例：指定した担当者と商品）
• 検索範囲：検索値と照合する列や配列
• 戻り配列：一致したときに返してほしい列や配列
• 見つからない場合：該当がないときに返すメッセージや値

また、XLOOKUPは完全一致検索が既定になっており、あいまいな一致ではなく、値がそろっているかどうかを厳密に見ます（出典：Microsoft サポート公式サイト）(マイクロソフト サポート)
このため、複数条件でうまく動かないときは、キーの文字列が1文字でも違えば一致しません。

現場では、「見た目は同じ担当者・商品なのにヒットしない」という相談がよくあります。
多くの場合、スペース・全角半角・入力ゆらぎなどで、キー文字列が微妙にズレているのが原因です。

---

XLOOKUPで複数条件検索を設定する具体的な手順

ここからは、ヘルパー列を使うパターンを例に、複数条件XLOOKUPの実際の設定手順を見ていきます。
「売上表」から「担当者と商品を指定して金額を引く」という、よくあるシーンを想定します。

実務の感覚に近づけるため、途中でつまずきやすいポイントと再発防止のコツもセットで整理します。
作業の流れをそのまま真似していけば、日々の集計表にもすぐ応用しやすくなります。

準備と前提条件（環境ごとの確認ポイント）

まず、次のような点を確認しておくとトラブルを減らせます。

• Excelのバージョン
  • XLOOKUPは新しめのExcelで利用できる関数です
  • 利用できない場合は、INDEX/MATCHやFILTER関数など別の方法を検討します（出典：Microsoft サポート公式サイト）(マイクロソフト サポート)
• データの配置
  • 「担当者」「商品」「金額」が1つの表にまとまっているか
  • 行ごとに1レコード（1明細）という形になっているか
• 入力ゆらぎ
  • 担当者名に余計なスペースが入っていないか
  • 同じ商品名は同じ表記ルールで入力されているか

現場では、バージョンの違いや入力ルールのバラつきが原因で、関数自体は合っているのに結果だけおかしいということがよくあります。
手順に入る前に、こうした前提を軽くチェックしておくと、後からの調査コストを大きく減らせます。

手順1：ヘルパー列を使って複数条件を連結する

売上表に「キー」列を1本追加します。
たとえば、A列：日付、B列：担当者、C列：商品、D列：金額という表なら、E列を「キー」列として使います。

E2セルに、次のような式を入れます。

=B2 & "｜" & C2

• B2：担当者
• C2：商品
• 「｜」：区切り文字（任意だが、空白より見分けやすい）

この式を下までコピーすると、

• 「佐藤｜A商品」
• 「田中｜B商品」

のようなキーが並びます。
複数条件を1つにまとめた識別子がこれで完成です。

経験則として、キー列はできるだけ目に見える位置に配置する方が、後からのトラブルシュートがしやすいというメリットがあります。
見えない名前付き範囲や複雑な数式だけに頼ると、引き継ぎ時に理解しづらくなることが多いです。

手順2：XLOOKUP関数を設定して結果を取得する

次に、検索側のシートで、担当者と商品を指定して金額を引くセルを用意します。

たとえば、検索シートで

• G2：担当者
• H2：商品
• I2：金額（結果を表示するセル）

という配置にしたとします。

I2セルに、次のような式を入力します。

=XLOOKUP(G2 & "｜" & H2,
         売上表!$E$2:$E$1000,
         売上表!$D$2:$D$1000,
         "該当なし")

ここでのポイントは次のとおりです。

• 検索値：G2 & "｜" & H2
  • 検索側でも同じルールでキーを作る
• 検索範囲：売上表のキー列（固定参照）
• 戻り配列：売上表の金額列（固定参照）
• 見つからない場合：”該当なし” など任意のメッセージ

同じルールでキーを作れていれば、複数条件を意識しなくても、通常のXLOOKUPと同じ感覚で扱えるようになります。

手順3：動的配列や名前付き範囲を活用する

行数が多い表や、今後明細が増えていく表では、範囲指定の管理が負担になりがちです。
その場合は、次のような工夫をしておくと、運用が楽になります。

• テーブル機能を使って、列全体をテーブル名で指定する
• 名前付き範囲に「売上キー」「売上金額」などの名前を付ける
• 動的配列関数と組み合わせて、範囲を自動拡張する

たとえば名前付き範囲を使うと、式が

=XLOOKUP(G2 & "｜" & H2,
         売上キー,
         売上金額,
         "該当なし")

のように読みやすくなります。
現場では、式を見ただけで「何をしている列か」想像できる名前を付けておくと、メンテナンスのしやすさが大きく変わります。

つまずきやすいポイントと対処法

複数条件XLOOKUPでよくあるつまずきを、「症状→原因→対処」で整理します。

症状1：常に#N/Aになる

• 原因
  • 検索値と検索範囲で、キーの作り方が揃っていない
  • 区切り文字が違う（「｜」と「-」など）
  • 入力のゆらぎ（スペース・全角半角違い）がある
• 対処
  • キー列の値を隣に表示して、目で見て一致しているか確認する
  • TRIMやCLEANなどで余分なスペースを削ることも検討する
  • 区切り文字の種類を統一する

なお、XLOOKUPなどの検索関数で検索値が見つからない場合、#N/Aエラーが返るのが一般的です（出典：Microsoft サポート公式サイト）(マイクロソフト サポート)

症状2：合計が合わない、違う行の値を拾っている気がする

• 原因
  • キー列を作る列の組み合わせが誤っている
  • 想定より少ない条件でキーを作っている（例：担当者だけ）
• 対処
  • 「本当に必要な条件は何か」を整理し直す
  • 必要なら3条件以上をキーに含める（例：日付&担当者&商品）

実務では、「とりあえず2条件で作ったが、実は3条件ないと一意に決まらない」というケースがよくあります。
「そのキーで1行に絞り込めるか」が判断基準になると考えると、条件の選び方を間違えにくくなります。

失敗を減らすためのチェックポイントと再発防止

複数条件XLOOKUPを安定して使うには、次のようなチェックを習慣にしておくと安心です。

• キー列は、表の末尾に1列まとめる
• キーの作り方を、シート内で統一する
  • 例：「担当者｜商品」「年月｜社員番号」など
• 入力ルールを簡単にメモしておき、引き継ぎ時に共有する
• 重要な表は、見出し行の近くに「キー列の意味」をコメントで残しておく

たとえば、チーム内の会話で

「この表、担当者と商品を連結したキーでXLOOKUPしてます」

と一言添えておくだけでも、他のメンバーが数式を読むときの負担が大きく変わります。
しくみを共有しておくこと自体が、再発防止策の1つと考えるとよいです。

---

XLOOKUPの複数条件活用例とよくある疑問

最後に、複数条件XLOOKUPが活きる代表的な場面と、よくある疑問点をまとめます。
あわせて、XLOOKUP以外の関数との使い分けの考え方も触れておきます。

経験的に、「まずXLOOKUPで単票を引く」「集計はSUMIFSやピボットテーブル」と役割を分けると、表全体の構造がシンプルになりやすいです。

代表的な活用パターン（売上表・勤怠表など）

複数条件XLOOKUPがそのまま役立つ、代表的なパターンは次のようなものです。

• 売上表で「日付＋担当者＋商品」から売上金額を引く
• 勤怠表で「年月＋社員番号」から勤務時間や残業時間を引く
• 在庫表で「倉庫＋商品コード」から在庫数を引く
• 価格表で「エリア＋プラン名」から単価を引く

たとえば在庫管理の現場では、同じ商品コードでも倉庫が違えば在庫数も違うことが多く、「倉庫＋商品コード」のような複合キーが自然な単位になります。
こうした場面では、複数条件XLOOKUPが非常に相性の良い手法です。

INDEX/MATCHやFILTER関数との使い分けの考え方（判断基準）

XLOOKUP以外にも、複数条件で使える関数はいくつかあります。
一般的な判断基準は次のようになります。

• XLOOKUP
  • 1件の値を引きたい
  • 「検索値 → 結果」というシンプルな形にしたい
• FILTER
  • 複数行をまとめて抽出したい
  • 「条件に合う一覧」を別シートに出したい
• SUMIFS / COUNTIFS
  • 集計（合計・件数）を取りたい
  • 「条件に合う合計金額」などの数値が知りたい

Microsoft公式の解説でも、XLOOKUPはVLOOKUPより柔軟に、任意の方向に検索できる機能として位置付けられています（出典：Microsoft サポート公式サイト）(マイクロソフト サポート)
複数条件かどうかに関わらず、「単一の値を引くときはXLOOKUP」「集計はIFS系」「一覧抽出はFILTER」と役割を分けると整理しやすくなります。

よくある質問

Q1：OR条件（AまたはB）のような検索はできる？

基本的なやり方はAND条件が中心ですが、OR条件を扱いたい場合は、FILTER関数で「条件1＋条件2」を組み合わせるほうがシンプルになることが多いです。
XLOOKUPでOR条件を無理に作るより、「AND条件はXLOOKUP、OR条件や複数行抽出はFILTER」と役割分担する考え方がおすすめです。

Q2：ヘルパー列を増やしたくない場合は？

数式の中で直接

=XLOOKUP(担当列 & "｜" & 商品列,
         検索側の担当 & "｜" & 検索側の商品,
         戻り配列)

のように、列同士を連結した配列を指定する方法もあります。
ただし、式が複雑になりやすく、トラブルシュートもしにくくなるため、まずはヘルパー列を使う方法から慣れると安心です。

Q3：XLOOKUPが使えないバージョンではどうすればよい？

XLOOKUPが利用できない環境では、

• INDEX/MATCHの組み合わせで複数条件を扱う
• SUMIFSで合計値だけを取得する
• 必要に応じてピボットテーブルで集計する

といった代替手段があります。
どの方法を選ぶかは、最終的に欲しい結果（単一値か、合計か、一覧か）で決めるとよいです。

XLOOKUPの複数条件の使い方についてのまとめ

・複数条件は担当者と商品などを連結し一つのキーにする
・キー作成には区切り文字を入れて値の境界を明確にしておく
・検索側と表側でまったく同じルールでキーを作成する
・ヘルパー列にキーを置くと目視確認ができて調査がしやすい
・XLOOKUPの検索値には連結したキー文字列を渡して利用する
・検索範囲にはヘルパー列全体を絶対参照で指定しておく
・戻り配列には取得したい金額などの列を対応させて指定する
・見つからない場合の引数でメッセージを設定し挙動を明確にする
・常にシャープエヌエーが出るときはキーの不一致を疑って確認する
・条件が足りないときは日付などを追加して一意なキーに調整する
・データ入力のゆらぎ対策としてスペースや表記ルールを統一する
・大きな表ではテーブルや名前付き範囲を活用し式を読みやすくする
・AND条件の単票取得はXLOOKUP集計はSUMIFSやピボットで分担する
・OR条件や複数行抽出が必要な場合はFILTER関数の利用も検討する
・バージョン制約がある場合はINDEXとMATCHなど代替手段も用意する

---