XLOOKUPで商品名だけ検索すると同じ商品が複数出てきて、正しい金額が返ってこない。部署名と氏名を組み合わせたいのに、1つの条件しか指定できず手が止まる。Excelで集計表を作っていると、こういう場面にかなりの確率でぶつかります。
XLOOKUPは1つの検索値を探す関数ですが、検索範囲の作り方を工夫すれば複数条件で検索できます。AND条件なら条件同士を掛け算し、OR条件なら条件同士を足し算する。ここさえ理解すれば、「担当者が田中で、商品がAの商品コードを返す」「地域が東京または大阪の売上を探す」といった検索が可能になります。
XLOOKUPで複数条件を指定する基本は条件式を1と0に変えること
XLOOKUPで複数条件を指定するときの基本は、「条件に合う行を1、合わない行を0に変える」ことです。XLOOKUPは検索値を検索範囲から探す関数なので、複数条件に合った行だけが1になる配列を作り、その1を検索します。
たとえば、A列に担当者、B列に商品名、C列に金額があるとします。「担当者が田中、商品名がノート」の金額を返したい場合、A列だけを見ても足りません。田中さんが複数商品を扱っているかもしれませんし、ノートを複数人が扱っているかもしれないからです。
AND条件は条件式を掛け算して1を探す
会議前に急いで管理表を直しているとき、「田中さんのノートの金額だけ出したいのに、別の商品が返ってくる」と焦ることがあります。原因は、検索条件が1つだけでは行を一意に決められないからです。
AND条件とは、複数の条件をすべて満たす検索です。Excelでは、TRUEを1、FALSEを0のように扱えるため、条件式を掛け算すると、すべての条件を満たした行だけが1になります。
基本形は次の通りです。
=XLOOKUP(1,(A2:A100="田中")*(B2:B100="ノート"),C2:C100)3条件以上でも考え方は同じ
条件が2つから3つに増えると、急に難しく見えます。でも、やっていることは同じです。条件式をもう1つ掛けるだけです。
たとえば、「担当者が田中」「商品がノート」「地域が東京」の3条件で金額を返すなら、次のように書きます。
=XLOOKUP(1,(A2:A100="田中")*(B2:B100="ノート")*(D2:D100="東京"),C2:C100)ここで大切なのは、各範囲の行数をそろえることです。A2:A100、B2:B100、D2:D100、C2:C100のように、開始行と終了行を合わせます。範囲がズレると、思った値が返らなかったり、エラーになったりします。
XLOOKUPでAND条件を使う実務パターン
AND条件は、ビジネスの管理表でかなり使います。顧客名だけでは重複する。商品名だけでも重複する。日付だけでも重複する。だから、複数の条件を組み合わせて1行を特定します。
たとえば請求管理、在庫管理、広告費管理、営業案件管理では、1つの条件だけで正しい行を取れることのほうが少ないです。むしろ「月」「担当者」「案件名」「媒体名」などを組み合わせて、ようやく対象行が決まることが多いでしょう。
顧客名と商品名で単価を検索する
営業管理表でよくあるのが、「顧客名」と「商品名」の組み合わせで単価を引くケースです。同じ商品でも顧客ごとに単価が違う場合、商品名だけでは正しい単価を返せません。
たとえば、A列に顧客名、B列に商品名、C列に単価があるとします。E2に顧客名、F2に商品名を入力し、G2に単価を表示したいなら次の式です。
=XLOOKUP(1,(A2:A100=E2)*(B2:B100=F2),C2:C100,"該当なし")最後の「該当なし」は、見つからなかった場合に表示する文字です。XLOOKUPには、見つからない場合に返す値を指定できる引数があります。これを入れておくと、#N/Aが並ぶよりも表が見やすくなります。
月と担当者で売上を検索する
月次レポートでは、「月」と「担当者」で売上を取り出す場面も多いです。たとえば、A列に月、B列に担当者、C列に売上がある表で、指定した月と担当者の売上を返したい場合です。
=XLOOKUP(1,(A2:A100=E2)*(B2:B100=F2),C2:C100,"未入力")ここでつまずきやすいのは、月の形式です。元データが日付形式なのに、検索セルが文字列の「2026年5月」になっていると一致しません。見た目が同じでも、Excel内部では別のデータとして扱われることがあります。
XLOOKUPでOR条件を使うときは条件式を足し算する
OR条件は、「AまたはBに当てはまる行を探す」検索です。AND条件が掛け算なら、OR条件は足し算で考えます。
たとえば、「地域が東京または大阪の行を探す」とします。東京なら1、大阪なら1、それ以外なら0です。条件を足し算すると、東京または大阪の行が1以上になります。XLOOKUPで検索値を1にしてもよいですし、条件式全体を0より大きいかどうかで判定しても使えます。
地域が東京または大阪の担当者を検索する
たとえば、A列に地域、B列に担当者名があるとします。東京または大阪に該当する最初の担当者を返したい場合は、次のように書けます。
=XLOOKUP(1,(A2:A100="東京")+(A2:A100="大阪"),B2:B100,"該当なし")この式では、A列が東京なら1、大阪なら1、どちらでもなければ0になります。XLOOKUPは1を探して、該当する最初の行の担当者を返します。
OR条件で複数件を返したいならFILTERを使う
上司から「東京か大阪の顧客だけ一覧で出して」と言われたとき、XLOOKUPで1件だけ返しても目的に合いません。ここで無理にXLOOKUPを使い続けると、数式が複雑になるだけです。
複数件を抽出したい場合は、FILTER関数を使います。FILTERは条件に合う行を絞り込んで返す関数です。Microsoft公式でも、FILTER関数は定義した条件に基づいてデータ範囲をフィルター処理すると説明されています。
=FILTER(A2:C100,(A2:A100="東京")+(A2:A100="大阪"),"該当なし")この式なら、A列が東京または大阪の行をまとめて返せます。XLOOKUPは「1件を探す」、FILTERは「条件に合う複数件を取り出す」と覚えると迷いません。
XLOOKUPでANDとORを組み合わせる応用テクニック
実務では、ANDだけ、ORだけでは足りないことがあります。「担当者が田中で、地域が東京または大阪」「商品がAまたはBで、月が2026年5月」のように、複数条件の中にOR条件が混ざる場面です。
このときは、条件のかたまりを括弧で分けます。Excelでは計算の優先順位があるため、どの条件を先にまとめるかを明確にしないと、意図と違う結果になる可能性があります。Microsoft公式でも、Excelの計算演算子には優先順位があり、括弧で順序を変更できると説明されています。
少し難しそうに見えますが、考え方はシンプルです。ANDは掛け算、ORは足し算。そして、ORの部分を先に括弧で囲む。これだけです。
担当者が田中で地域が東京または大阪の売上を検索する
A列に担当者、B列に地域、C列に売上があるとします。「担当者が田中」かつ「地域が東京または大阪」の売上を返したい場合は、次の式です。
=XLOOKUP(1,(A2:A100="田中")*((B2:B100="東京")+(B2:B100="大阪")),C2:C100,"該当なし")ここでは、地域のOR条件を先に括弧でまとめています。東京または大阪なら1以上になります。そこに担当者が田中かどうかを掛けることで、田中さんかつ東京または大阪の行だけが検索対象になります。
OR条件を0より大きい判定にして安定させる
別列の条件をORで組むときは、足し算の結果が2以上になる場合があります。たとえば、「商品がA」または「優先フラグがあり」のような条件です。1行が両方を満たすと、足し算結果は2になります。
このとき、XLOOKUPで1だけを探すと、2になった行を見逃す可能性があります。そこで、足し算結果が0より大きいかを判定します。
=XLOOKUP(TRUE,(A2:A100="田中")*(((B2:B100="東京")+(C2:C100="優先"))>0),D2:D100,"該当なし")この式では、OR条件が1でも2でも、0より大きければTRUEになります。そこに担当者条件を掛け合わせることで、実務上の条件に近い検索ができます。
XLOOKUPで複数条件がうまくいかない原因
複数条件のXLOOKUPでエラーになると、数式全体が難しく見えて、どこを直せばいいかわからなくなります。特に#N/Aが出ると、「条件に合うデータがないのか」「式が間違っているのか」が判断しづらいです。
実務で多い原因は、範囲ズレ、表記ゆれ、日付形式の違い、余分なスペース、数値と文字列の混在です。関数そのものより、データ側の問題で止まっていることが多いんですよね。
範囲の行数がズレていると正しく検索できない
複数条件では、すべての範囲の行数をそろえる必要があります。A2:A100とB2:B99のようにズレていると、意図した判定ができません。
操作前につまずきやすいのは、数式をコピーして一部だけ範囲を直し忘れることです。返す範囲だけC2:C101になっている、検索条件の一つだけB3:B100になっている。こういうズレは、見た目では気づきにくいです。
確認するときは、検索条件の範囲と戻り範囲を横に並べて見てください。開始行と終了行が同じかをチェックします。Excelテーブルを使っている場合は、構造化参照にすると範囲ズレを減らせます。
余分なスペースや表記ゆれで一致しない
XLOOKUPは、基本的に一致した値を探します。そのため、見た目が同じでも、余分なスペースが入っていると別の値として扱われます。
たとえば、「田中」と「田中 」は違います。末尾に半角スペースがあるだけで一致しません。顧客名や商品名をコピー貼り付けしている表では、このミスがかなり起きます。
XLOOKUPで複数条件を使うときのエラー対策
複数条件のXLOOKUPでは、エラー対策まで入れておくと実務で使いやすくなります。#N/Aが表示されると、表が壊れて見えますし、見た人が「何かミスがあるのでは」と不安になります。
XLOOKUPには「見つからない場合」の引数があります。そこに「該当なし」や空白を入れるだけでも、かなり見やすくなります。さらに、複雑な式ではIFERRORを使う場面もあります。IFERRORは、数式がエラーになった場合に指定した値を返す関数です。
ただし、エラーを隠しすぎるのも危険です。本当はデータが間違っているのに、空白にしてしまうと気づけません。提出用の表では空白でもよいですが、作業中は「該当なし」と表示したほうが確認しやすいです。
見つからない場合はXLOOKUPの第4引数を使う
XLOOKUPでは、4つ目の引数に見つからない場合の表示を指定できます。複数条件でも同じです。
=XLOOKUP(1,(A2:A100=E2)*(B2:B100=F2),C2:C100,"該当なし")このように書くと、条件に合う行がない場合に「該当なし」と表示されます。#N/Aのままにするより、表を見る人に伝わりやすくなります。
実務では、「該当なし」「未登録」「確認中」など、状況に合わせて表示を変えると便利です。たとえばマスタに未登録の可能性があるなら「マスタ未登録」と出す。すると、次にやるべき行動が見えます。
IFERRORで全部まとめて処理するのは最後の手段にする
IFERRORを使えば、どんなエラーでも指定した表示にできます。たとえば次のような式です。
=IFERROR(XLOOKUP(1,(A2:A100=E2)*(B2:B100=F2),C2:C100),"確認してください")便利ですが、最初からIFERRORで包むと、原因が見えにくくなります。#N/Aなのか、#VALUE!なのか、範囲ズレなのかがわからなくなるからです。
XLOOKUPとFILTERは目的で使い分ける
XLOOKUPで複数条件を扱えるようになると、なんでもXLOOKUPでやりたくなります。でも、実務ではFILTERのほうが向いている場面もあります。
XLOOKUPは、条件に合う1件を返すのが得意です。一方、FILTERは条件に合う複数行をまとめて返すのが得意です。どちらが上という話ではなく、用途が違います。
たとえば、請求書番号から顧客名を返すならXLOOKUP。東京支店の売上一覧を全部出したいならFILTER。この切り分けをすると、数式がシンプルになります。
1件だけ返したいならXLOOKUP
XLOOKUPが向いているのは、検索結果が1件に決まる場面です。顧客コード、商品コード、社員番号、案件IDのように、条件を組み合わせると1行に絞れるデータです。
たとえば、社員番号と対象月で交通費を返すならXLOOKUPが向いています。1人1月に1件のデータがある前提なら、検索結果が1件になるからです。
この前提が崩れると危険です。同じ社員番号と月で複数行ある場合、XLOOKUPは最初の1件しか返しません。複数件の合計が必要ならSUMIFS、一覧が必要ならFILTERを使うべきです。
複数件を一覧で出したいならFILTER
FILTERが向いているのは、条件に合う行を全部見たい場面です。たとえば「担当者が田中の案件一覧」「地域が東京または大阪の売上一覧」「未対応ステータスの問い合わせ一覧」などです。
=FILTER(A2:D100,(B2:B100="田中")*(D2:D100="未対応"),"該当なし")この式なら、B列が田中、D列が未対応の行をまとめて返せます。XLOOKUPで最初の1件だけ返すより、目的に合っています。
Excelの関数は、難しい式を書けることより、目的に合う関数を選べることのほうが大切です。無理に1つの関数で全部やろうとしないほうが、結果的にミスが減ります。
XLOOKUPで複数条件を見やすく管理するコツ
業務で使うExcelは、自分だけのメモではありません。上司、同僚、経理、クライアントに共有することもあります。だから、数式の見やすさも実務スキルの一部です。
ロロメディア編集部でも、レポート自動化のシートは、あとで別担当者が触る前提で作ります。数式が少し長くなる場合は、条件セルを分けたり、補助列を作ったりして、読みやすさを優先します。
検索条件をセルに分ける
数式の中に直接「田中」「東京」「ノート」と書くと、条件を変えるたびに数式を編集する必要があります。これはミスの原因になります。
おすすめは、検索条件を別セルに置くことです。たとえばE2に担当者、F2に商品名を入力し、G2に検索結果を出します。
=XLOOKUP(1,(A2:A100=E2)*(B2:B100=F2),C2:C100,"該当なし")この形なら、E2やF2を変えるだけで検索結果が変わります。レポートや確認用シートでは、このほうが圧倒的に使いやすいです。
複雑すぎるなら補助列を使う
作業列なしで複数条件検索できるのがXLOOKUPの魅力ですが、複雑すぎる場合は補助列を使って構いません。補助列とは、検索用のキーを作るための作業列です。
たとえば、A列の顧客名とB列の商品名を結合してD列に検索キーを作ります。
=A2&"|"&B2そのうえで、検索側も同じ形にしてXLOOKUPします。
=XLOOKUP(E2&"|"&F2,D2:D100,C2:C100,"該当なし")この方法は少し古典的ですが、他の人が見ても理解しやすいです。数式を短くしたい、共有先のExcelスキルに差がある、エラー原因を追いやすくしたい。そういう場面では、補助列はむしろ実務的です。
XLOOKUPで複数条件を使うときの実務チェックリスト
複数条件のXLOOKUPは、数式を入れて終わりではありません。結果が本当に合っているか確認する必要があります。
数式を入れたあと、最低限次の確認をしてください。
- 検索条件の範囲と戻り範囲の行数がそろっているか
- 検索条件に表記ゆれがないか
- 日付や数値が文字列になっていないか
- 重複データがないか
- 最初の1件だけ返って問題ないか
- 該当なしの表示が適切か
この確認は、面倒でも最初にやったほうが早いです。後から間違いに気づくと、数式だけでなく提出済み資料の信頼まで落ちます。
重複データがある場合はXLOOKUPだけで判断しない
XLOOKUPは条件に合う最初の値を返します。つまり、同じ条件に合う行が複数あっても、全部は教えてくれません。
ここが実務でかなり危ないです。たとえば「顧客名」と「商品名」で検索しているつもりでも、同じ顧客が同じ商品を複数回買っていたら、最初の1件だけ返ります。合計金額が必要なのに、単発の金額だけ出てしまうかもしれません。
重複がありそうなデータでは、COUNTIFSで件数を確認すると安全です。
=COUNTIFS(A2:A100,E2,B2:B100,F2)この結果が1ならXLOOKUPで返して問題ありません。2以上なら、XLOOKUPではなくSUMIFSやFILTERを検討してください。
合計が欲しいならSUMIFSを使う
複数条件で「該当する金額の合計」を出したい場合、XLOOKUPではなくSUMIFSを使います。SUMIFSは複数条件で合計する関数です。
たとえば、担当者が田中、商品がノートの売上合計を出すなら次の式です。
=SUMIFS(C2:C100,A2:A100,E2,B2:B100,F2)XLOOKUPは検索、SUMIFSは集計です。ここを混同すると、レポートの数字がズレます。特に売上表では、「1件を探す」のか「合計を出す」のかを先に決めてください。
XLOOKUPの複数条件でよくある質問
ここでは、実務で聞かれやすい疑問をまとめます。関数の話は細かく見えますが、現場で困るポイントはだいたい決まっています。
急いでいるときほど、「この式でいいのか」「なぜ返らないのか」「他の関数を使うべきか」で迷いますよね。質問ごとに判断基準を持っておくと、作業が止まりにくくなります。
XLOOKUPで複数条件は正式な使い方なのか
XLOOKUPの基本構文は、検索値、検索範囲、戻り範囲を指定する形です。複数条件用の専用引数があるわけではありません。
ただし、検索範囲に条件式の配列を指定することで、複数条件検索ができます。これはExcelの配列計算を活用した使い方です。Microsoft公式でも、配列数式は1つ以上の項目に対して複数の計算を実行できるものとして説明されています。
XLOOKUPが使えないExcelではどうすればいいか
XLOOKUPは比較的新しいExcel環境で使える関数です。古いExcelでは使えない場合があります。Microsoft公式のXLOOKUPページでも、利用できるExcelのバージョンが案内されています。
使えない環境では、INDEXとMATCH、またはVLOOKUP用の補助列を使う方法があります。共有相手が古いExcelを使っている場合は、XLOOKUPを使ったファイルを渡すとエラーになる可能性があります。
Googleスプレッドシートでも同じように使えるか
GoogleスプレッドシートにもXLOOKUPが使える環境があります。ただし、Excelと完全に同じ挙動とは限らないため、共有前に確認が必要です。
特に配列の扱い、エラー表示、関数の区切り文字、ロケール設定によって動きが変わる場合があります。Excelではカンマ区切りでも、環境によってはセミコロンが必要になることもあります。
まとめ
XLOOKUPで複数条件を指定する基本は、条件式を使って検索用の配列を作ることです。AND条件は掛け算、OR条件は足し算で考えます。
担当者と商品名の両方に一致する金額を返したいなら、次の形が基本です。
=XLOOKUP(1,(A2:A100=E2)*(B2:B100=F2),C2:C100,"該当なし")東京または大阪のようなOR条件なら、次の形になります。
=XLOOKUP(1,(A2:A100="東京")+(A2:A100="大阪"),B2:B100,"該当なし")ANDとORを組み合わせる場合は、括弧で条件のかたまりを分けてください。計算の優先順位を曖昧にすると、意図しない結果になることがあります。
ただし、XLOOKUPは基本的に1件を返す関数です。複数件を一覧で出したいならFILTER、合計したいならSUMIFS、重複件数を確認したいならCOUNTIFSを使います。関数選びを間違えないことが、実務では一番大事です。
