XLOOKUPで複数条件を検索｜一致件数と重複を確認する練習例

※本記事にはPRを含む場合があります。

こんにちは。『脱会社員』脱サラを応援し続けるブログ、運営者の『なかびー』です。

同じ商品コードに複数のサイズがあり、コードとサイズの両方が一致する単価を表から取り出したい。そんなとき、XLOOKUPで複数条件を指定できます。ただし、式が値を返しても、その組み合わせが元の表に一件しかないとは限りません。重複する行があるのに最初の一件を採用すると、誤った単価のまま作業が進むこともあります。

この記事では、同じ二つの条件で一致件数を数え、0件なら未登録、1件なら単価を表示、複数件なら確認のため止める方法を練習します。見つかった値の正しさまで自動保証する方法ではありません。元データの入力ミスや、返却する単価の空欄・意味のあるゼロも、担当者が別に確認します。

例の表と金額は説明用の架空データです。公式資料の仕様と表の論理から期待される結果を示しており、この記事の式を実機のExcelで計算した結果ではありません。実際のファイルに使う前に、コピーした練習用ブックで動作を確かめてください。掲載情報の確認日は2026年10月31日です。

XLOOKUPやFILTERは対応するExcelで使用します。MicrosoftはXLOOKUPをExcel 2016・2019では利用できないと案内しています。職場で利用する版や、ファイルを受け渡す相手の環境も確認しましょう。MicrosoftのXLOOKUP関数の説明には対象環境と引数があります。

商品コードとサイズの二つで探す練習表を作る

まず、結果を入れる先と、検索の対象となる元表を分けます。練習用の新しいシートで、A1に「商品コード」、B1に「サイズ」、C1に「単価」と入力し、次の五行をA2からC6に入れてください。F1からI1は順に「探すコード」「探すサイズ」「一致件数」「表示結果」です。F2とG2に調べたい組み合わせを入力し、H2とI2で件数と結果を確認します。

※以下の表は横にスクロールできます。

たとえばP101とSの組み合わせは2行目に一件あります。一方、P101とMは3行目と5行目の二件です。商品コードだけを探すと、同じP101のSとMを区別できません。サイズだけでもP101とP102のSが混ざります。両方を一つの組として照合することが、今回の出発点です。

元表の6行目には、あえてコードの空欄を用意しました。A6には「空欄」という文字を入力せず、セルを実際に空けてください。F2が未入力のままG2にSだけを入れて検索すれば、空のコードを有効な条件と取り違えるおそれがあります。そこで入力がそろう前は検索自体を保留します。元表に空欄を残してよいという判断ではなく、入力漏れをどう扱うかを練習するための行です。

表の並びとセル番地は式の一部です。A2:A6はコード、B2:B6はサイズ、C2:C6は単価で、いずれも同じ2〜6行を指します。途中の行だけを片方の範囲から外すと、同じ行同士を比べる前提が崩れます。別の表に応用するときは、列名だけでなく開始行・終了行・見出しの有無も紙に書き出して照合しましょう。

単一条件でXLOOKUPを初めて使う方は、先にXLOOKUPの基本と参照範囲の確認を読むと、検索値と返却範囲の関係をつかみやすくなります。この記事では基本式を踏まえ、二つの条件と件数を合わせて扱います。

今回の表は、P101とMの二行がどちらも正しい可能性を残しています。700が古く800が新しい、下の行が優先される、といった事情はデータに書かれていません。重複を見つけた段階では一方を消したり、並び順だけで正しい方を決めたりせず、元資料と照合するのが安全です。

二つの条件を掛け合わせ、最初の一致が返る仕組みを知る

二条件の検索では、各行について「コードが一致するか」と「サイズが一致するか」を別々に調べます。両方が真の行だけを取り出したいので、二つの比較結果を掛け合わせます。どちらも真なら1、片方または両方が偽なら0という並びになり、その並びの1をXLOOKUPで探します。

F2にP101、G2にSを入れた場合、コードの比較は行2〜6で順に真、真、偽、真、偽です。サイズの比較は真、偽、真、偽、真。各行で掛け合わせると1、0、0、0、0となり、1は2行目にだけあります。対応するC列の値は500です。

F2をP101、G2をMにすると、コード側は同じでもサイズ側が変わり、積は0、1、0、1、0になります。1が二つあることが見えるでしょう。通常の先頭からの検索ではXLOOKUPは最初に一致する行の値を返します。この表では700を返すことが想定されますが、5行目の800が存在しないという意味ではありません。

仕組みを確かめる基本形は次の式です。これは件数の警告を付ける前の説明用です。

=XLOOKUP(1,($A$2:$A$6=F2)*($B$2:$B$6=G2),$C$2:$C$6,"未登録",0,1)

検索値の1は、二つの比較結果がともに真の行を表します。検索配列は比較結果の積、戻り配列はC2:C6です。見つからない場合に表示する文字は「未登録」、一致方法の0は完全一致、検索方法の1は先頭からの検索を明示しています。引数の意味はMicrosoftの公式説明でも確認できます。

この式をそのまま業務で使う前に、一つの課題が残ります。P101/Mのように二件ある場合、式は重複を警告せず、検索順に応じた一件を返します。検索方法を逆向きに変えても、二件のどちらが正しいかは決まりません。「値が出た」と「根拠を確認した」は別の段階です。

また、二条件を単純に文字列としてつなぐ方法は、区切り方によって異なる組み合わせが同じ文字列になることがあります。今回の式は二つの列をそれぞれ比較するので、その連結の衝突を避けられます。ただし、入力値の表記ゆれやデータそのものの誤りまで修正するものではありません。

比較の積を追いやすくするため、実際の練習シートに一時的な確認列を設けてもよいでしょう。D列に「コード一致」、E列に「サイズ一致」を置き、各行の値がF2とG2にそれぞれ合っているかを目でたどります。この確認列は必須ではありません。判断の途中が見えなくなったときに、一致条件が同じ行にそろっているかを分解するための補助です。表を共有するときは、補助列を隠したまま理由を伝えない運用にせず、必要に応じて役割を記録します。

P101/Sのときは、P101というコード自体は2・3・5行目にあります。その中からサイズSまで一致する2行目だけが候補です。P101/Mでは同じコードの候補のうち3・5行目が残ります。「各条件を満たす行がそれぞれどこかにある」だけでは足りず、「同じ行で両方を満たす」必要がある点を意識してください。コードが2行目、サイズMが5行目にあるという別々の事実を、一行の一致にしてはいけません。

比較の意味を見失ったときは、いきなり長い式を編集せず、一行ずつ「コードは合うか」「サイズは合うか」を表に書いてみてください。P101/Sなら2行目だけ、P101/Mなら3行目と5行目という結果に戻れるかが、後の件数計算の基準になります。

一致件数を先に数え、未入力と未登録を分ける

結果の単価を表示する前に、二つの条件に一致した行が何件あるかをH2で数えます。F2かG2のどちらかが空欄なら、件数を計算せず「入力待ち」にします。両方の入力がそろったときだけ、先ほどの条件の積をSUMPRODUCTで合計します。

=IF(OR(F2="",G2=""),"入力待ち",SUMPRODUCT(($A$2:$A$6=F2)*($B$2:$B$6=G2)))

ORは二つのセルのうち少なくとも一方が空かを調べます。空ならIFの最初の結果を返し、両方に入力があればSUMPRODUCTに進みます。SUMPRODUCTは対応する配列の積を扱う関数です。ここでは各行の比較の積を合計するため、両方が一致した行の数になります。配列の大きさをそろえることや列全体を参照するときの注意は、MicrosoftのSUMPRODUCTの説明を確認してください。

期待する値を練習表で追ってみます。P101/Sでは積が1、0、0、0、0なので合計1。P101/Mでは0、1、0、1、0なので合計2です。P102/Mはコードとサイズが同時に合う行がなく、合計0になります。入力の片方が空のときは、たとえ元表に空欄の行があっても、件数ではなく「入力待ち」です。

件数0は「元表の指定範囲に、入力した二条件が同時に合う行がない」という意味です。商品が存在しないと断定したり、新規登録を自動で指示したりする数字ではありません。入力の余分な空白、コードの型、元表への追加漏れ、指定範囲の外側なども確認してから、登録が必要かを決めます。

「入力待ち」と0を分ける理由は、調べる準備が整っていない状態と、調べたが見つからなかった状態では、次の行動が違うからです。入力待ちでは不足しているF2またはG2を埋めます。0なら入力と参照範囲を確認し、必要なら元表の管理者に照会します。どちらも同じ表示にすると、入力途中の行を未登録として処理しかねません。

条件の範囲を一方だけA2:A7に広げ、もう一方をB2:B6のままにすることは避けましょう。SUMPRODUCTで扱う配列の寸法が合わなくなるだけでなく、仮に式を組み替えて表示が出ても、件数と後の検索が違う対象を見ていれば判定が信用できません。範囲を変えるときは件数側と検索側を一組として点検します。

入力セルが数式で空文字を返す場合も、ここでは空欄扱いです。一方、見た目が空に見えてもスペースが入っていれば同じとは限りません。使うデータに応じて、何を未入力として扱うかを別途決めてください。空白を一括削除する前には、元の値が識別子として使われていないか確認しましょう。

検索条件がそろっているのに0件となった場合、入力と元表を同じ目線で点検します。例えばF2のコードに余分な空白がないか、G2のサイズに別の表記が使われていないか、確認対象の行が2〜6行の範囲内にあるかを一つずつ見ます。件数0という結果自体を消して先へ進むのではなく、何を確認し、元データのどこを直したかを残すと、次の担当者も経緯をたどれます。

実際の表で検索値が文字列と数値の混在する識別子の場合、表示が似ていても比較の結果が期待どおりとは限りません。この練習はP101やSなど文字のコードを前提にしています。数値型へ勝手に変換すれば先頭のゼロが失われることもあるため、コードの定義を確かめ、変換前のデータを残してから、代表例で照合してください。式の件数だけを見て表記の正規化が完了したとは判断できません。

一件のときだけ単価を表示し、範囲のずれを防ぐ

H2の件数を使い、I2では表示を分岐します。入力待ちならそのまま待ち、件数0なら「未登録」、2以上なら「重複」とします。残る1件のときだけXLOOKUPでC列から単価を返します。

=IF(OR(F2="",G2=""),"入力待ち",IF(H2=0,"未登録",IF(H2>1,"重複",XLOOKUP(1,($A$2:$A$6=F2)*($B$2:$B$6=G2),$C$2:$C$6,"未登録",0,1))))

H2は上の件数式を入れたセルです。F2/G2、H2、I2をまとめて下へコピーする場合は、F3/G3とH3が次の行の入力と件数に対応します。A・B・Cの参照範囲に付けたドル記号は、コピーしても元表の位置を固定するためのものです。

例えばP101/Sのとき、H2は1になり、I2は500を返す想定です。P101/MならH2が2となり、「重複」の表示で止まります。P102/Mなら0で「未登録」、空欄/Sなら「入力待ち」です。I2の式にも入力待ちの判定を入れているので、H2に残っている値だけを見て検索する流れを避けられます。ただし、H2とI2に別々の行の入力を参照させると意味がありません。コピー後には各行の対応を確認しましょう。

大切なのは、H2の条件とI2の検索条件が同一であることです。H2がA2:A6とB2:B6を数えているのに、I2がA2:A7とB2:B7を検索すれば、H2が1でもI2側では二件目を含む可能性があります。逆に件数が2でも検索対象から一件が抜けていると、調査対象と表示の対応が崩れます。

元表に7行目を追加する場合は、H2のAとB、I2のAとBとCの五つの範囲すべてについて、開始・終了行が同じか確認します。手入力の固定範囲には、行の追加時に範囲を更新し忘れるリスクがあります。テーブルの構造化参照など別の設計を採用する場合も、件数と検索の対象が一致するかを、追加した行を使って確認してください。

一致が1件でも、C列の単価が妥当とは限りません。返却元が空欄なら、この式は「単価未入力」という専用の状態を判定していません。検索結果の表示だけで空欄と数値のゼロを同じものとして扱わず、該当するCセルを見て、単価が入力されているか、0に業務上の意味があるかを確認します。必要なら、単価欄の状態を別セルで管理する設計を検討しましょう。

H2に数値以外を手入力して「1件」と見せることはしません。この連携は、H2がF2とG2に対する件数式であることを前提にしています。共有ファイルで入力欄と式欄が混在するなら、どこを入力しどこを変更しないかを見出しや説明に示します。式を保護する設定の要否は、共同編集や修正の手順を確認してから決めてください。

複数行にコピーする前には、F3とG3が別の組み合わせを受け取り、H3がその行の入力を数え、I3が同じH3を読むことを確認します。H3が誤ってH2を参照していれば、別の商品について前の行の件数を使い、重複の警告が出ない可能性があります。逆に元表までずれていれば、数える行が変わります。入力側は行に合わせて動き、元表側は固定されているかを、コピー先の式で点検します。

同じ高さの範囲でも先頭行が異なると対応が崩れる点にも注意します。A2:A6とB2:B6に対してC3:C7を指定すると、比較で見つけた元行と、返される単価の行が一行ずれます。すべてが五行だから安全とはいえません。練習表のP101/Sなら、2行目の条件に対してC2の500を確認する、という具体的な行番号で点検してください。

件数1は「指定した範囲で、二つの条件に合った行が一行ある」という確認です。最新の価格、取引先との合意、税込み・税抜き、期間など、表に含まれていない条件は検証していません。さらに必要な条件がある場合は、先にどの列を追加し何を一意にするのかを決めます。見かけ上の一致だけで正式な単価表として扱わないようにしましょう。

重複したら候補行を並べ、元資料へ戻る

P101/Mは件数2なので、I2は「重複」で止まります。ここで必要なのは、どちらの単価を自動的に選ぶかではなく、二行が何を表しているかを調べることです。練習表では3行目が700、5行目が800と異なります。片方が入力ミスかもしれませんし、適用期間や取引先が異なるのに必要な列が省かれているのかもしれません。いずれも表だけからは決められません。

対応するExcelでは、FILTERで同じ二条件に合う行を一覧にする方法があります。結果を展開できる空き領域を確保し、たとえばK2に次の式を入力します。H2と同じF2/G2を使い、元表のA2:C6を候補として返す例です。

=FILTER($A$2:$C$6,($A$2:$A$6=F2)*($B$2:$B$6=G2),"該当なし")

P101/Mなら、3行目と5行目に対応する二行が返ることを想定しています。FILTERは複数行・複数列に結果が広がるため、K2の右や下に既存データがあると展開を妨げます。空き領域を用意し、対象範囲の行数をそろえてください。該当行がないときの第3引数は「該当なし」を表示するためのもので、該当行の単価セルが空欄かどうかの確認とは別です。MicrosoftのFILTER関数には、複数条件と空結果の扱いが示されています。

FILTERの結果は、元表の内容を調査するための写しとして読みます。練習表には適用日や承認者を入れていないので、700と800のどちらを使うかはここでは判断できません。実務では、商品台帳の管理者、注文書、承認済み価格表など、その業務で正本と決めた資料を確認し、必要な条件が欠けていないかを調べます。元表を直す担当者が別にいる場合は、値だけでなくコード・サイズ・該当する行と疑問点を伝えると確認しやすくなります。

たとえば「同じ商品とサイズだが、販売期間が異なる二行」と判明した場合、単価を使う日付も照合条件に含める必要があるかもしれません。その条件が確定するまでは、単に最後の行を採用してはいけません。期間の重なりや境界日の決め方は、この練習表の範囲外です。ルールを設計してから別の例でテストします。

一方、3行目が誤入力で5行目が正しいと管理者が確認した場合でも、いきなり元データを消す前に、修正権限や履歴の残し方を確認します。記録を残す必要がある表では、削除よりも訂正日や理由を記録できる方法が適する場合があります。これは一般的な作業上の整理であり、特定の組織の手続きに代わるものではありません。

担当者へ確認するときは「P101/Mが二件あります」だけで終えず、対象の表の版、3行目と5行目、それぞれの単価700・800、単価を使う予定の取引や日付、どの資料を正本として確認したいかを添えると、調査の行き先を共有できます。ここで示した値は架空例です。実際のファイルに個人情報や取引先情報がある場合は、社内の共有ルールに沿って必要な範囲だけを伝えます。

FILTERの結果が出ない場合にも、原因を一つに決めつけないでください。該当なしという文字なら今回の式の第3引数が返った可能性を確認し、展開できないエラーなら結果先にある値を確認します。比較対象にエラー値が含まれている場合は、条件の配列がエラーになる可能性もあります。エラーをまとめて「未登録」に置き換えると、元表の問題を見落とすことがあります。実際のエラー表示と条件、元データを分けて調べましょう。

重複行が同額でも注意は必要です。表示される単価だけを見れば影響がないように見えても、別の列には異なる取引先や期間があるかもしれません。「同じ値だから一件として扱う」と決める前に、何を一件と数えたいのか確認します。今回のH2は行数を数えます。同額の二行も2です。

全行を取り出すFILTER自体の使い方や、スピル範囲の準備はFILTERで条件に合う行を抽出する方法でも解説しています。ここでは、件数が複数のときに調査へ戻る手段として利用します。検索結果を見た後に元表を修正したら、件数・候補一覧・単価の三つを改めて照合してください。

五つの入力で結果を点検し、運用前に記録する

式を貼り付けて最初の値が出た時点では、他の分岐はまだ確認できていません。練習表では、次の五つの入力を順番に試し、入力欄・件数・結果・確認先を一行ずつ記録します。以下は先ほどの表から導いた期待値です。使用するExcel環境で実際に入力した結果は、その環境で別途確認してください。

表の期待と異なったら、まずF2とG2を読み直し、次にH2の参照範囲と行数、I2の参照範囲とH2の参照先を確認します。P101/Mで700が直接出る場合は、件数による分岐を省いた基本式を入力していないか調べます。P102/Sで500が出る場合は、返却範囲の開始行が検索範囲と同じか、どの入力セルを参照しているかを順に確認します。原因を決めつけず、式の参照先と元表の実際の並びを照合してください。

次に、練習用のコピーでC4を空欄にした場合を考えます。P102/Sの一致行は一件のままなので、H2の件数だけでは単価の空欄を検出できません。返却セルの表示がどうなるかも含めて、利用環境で確認し、C4が空であるという元の状態と区別して記録します。C4を数値0に変えた場合も同様です。0が有効な単価なのか、入力漏れの代替なのかは式だけでは分かりません。

記録票には「入力」「期待件数」「実際の件数」「期待する表示」「実際の表示」「元行」「元セルの状態」「確認者または確認日」を分けて書きます。期待と実際を同じ欄にすると、試していない結果を確認済みと誤認しやすくなります。未実施なら空欄や未確認と記し、実際に使用するExcelで試した後に観測を埋めます。この記事の期待値をそのまま実測欄に転記してはいけません。

例えばP101/Mの行では、期待件数2、期待表示「重複」、元行3と5までは練習表から準備できます。Excelで入力後、実際のH2とI2を別欄に記入し、候補を調べる操作では3行目と5行目の両方が見えたかを記録します。万一片方しか出なければ、その場で正しい単価を決めるのではなく、FILTERの範囲、条件、展開先、元表の値へ戻ります。

コピーした表のC4を空欄にするテストと、C4を0にするテストは別の状態として記録します。もとの練習表のC4が550だったことも残しておけば、例を変えたまま他の行の期待値を使う混乱を避けられます。テストが終わったら練習用コピーを元の値に戻すか、状態ごとに別のコピーを用意してください。正式な価格表にテスト値を書き込む必要はありません。

比較用の二つのコピーでは、それぞれF2/G2にP102/Sを入れた場合を考えます。元の2〜6行を参照するなら、どちらも一致行は4行目の一件です。異なるのはC4の状態であり、片方は空、もう片方は数値の0です。H2が同じ1でも、価格を採用するかどうかの判断は同じになりません。空欄のコピーは入力・承認状況の確認へ、0のコピーは0がその取引で意味を持つかの確認へ進めます。I2の実際の表示は、各コピーのExcelで観測したうえで別に記録します。

表を更新する担当者に引き継ぐときは、数式だけを渡すのではなく、どの表を元表とするか、対象範囲がどこまでか、重複の連絡先、空欄と0の判断を誰が行うかも併記します。元表の行を増やしたときは、H側とI側の範囲を確認し、重複する例と一件の例で再照合することも作業に含めます。元表のデータを直した人と、結果を利用する人が異なる場合には、修正後の表の版を共有してから再検査すると、修正前後を混同しにくくなります。

最後に、練習表の7行目へ新しい行を追加したと仮定します。固定範囲が2〜6行のままなら、その行は件数にも検索にも含まれません。両方を7行目まで拡張した後は、同一の開始行と終了行になっていることを確認し、追加行に一致する入力と、既存行だけに一致する入力の両方を試します。単に式をコピーできたかではなく、探す母集団が期待どおりかを点検する手順です。

たとえば練習用コピーの7行目に、P102、S、600を追加した場合を考えます。範囲を両式とも7行目まで広げれば、P102/Sに一致するのは4行目と7行目の二件となり、「重複」で止まることが期待されます。範囲を広げる前は追加した7行目を対象に含めていません。この差を記録しておけば、式の表示だけで新しい行まで照合できたと思い込むことを避けられます。追加例の600も架空の値です。実際の計算結果は練習用コピーで確認してください。

現場の表へ移す際は、実データを直接書き換える前にコピーで試し、コードとサイズの列、検索範囲、結果を置く位置、入力が空のとき、同じ組み合わせが複数あるときの扱いを決めてください。共有先がXLOOKUPとFILTERに対応しているか、更新する人が範囲と確認手順を引き継げるかも大切です。

式が返すのは、決めた範囲と条件に対する計算結果です。二条件の一致件数を先に出し、1件のときだけ単価を表示することで、複数候補を一件に見せてしまう問題に気づきやすくなります。見つからないときは入力と範囲へ、重複したときは候補行と元資料へ戻り、単価欄の空欄や0も別に確認しましょう。