エクセルで商品コードと色、担当者と月、部署と社員番号のように、複数の条件を満たす行から値を取り出したい場面は少なくありません。
XLOOKUP関数は本来ひとつの検索値を探す関数ですが、条件ごとの判定結果を掛け合わせることで、複数条件検索にも対応できます。
検索値と検索範囲の作り方をそろえることが、複数列から正しい値を抽出するための重要なポイントです。
複数条件の基本形
=XLOOKUP(1,(条件範囲1=条件値1)*(条件範囲2=条件値2),返す範囲,”該当なし”)
この記事では、複数列の条件を使ったXLOOKUPの数式、複数の戻り値を抽出する方法、エラーを避ける確認方法まで順に解説します。
エクセルでXLOOKUPを複数条件で使う数式
それではまず、2つの条件を掛け合わせて目的の値を取り出す基本的な方法について解説していきます。
| 商品コード | 色 | 商品名 | 単価 |
|---|---|---|---|
| A001 | 赤 | ノート | 120 |
| A001 | 青 | ノート | 130 |
| B002 | 赤 | ペン | 180 |
| B002 | 黒 | ペん | 190 |
ここでは1行目が見出しで、A列に商品コード、B列に色、C列に商品名、D列に単価が入力されているものとします。
条件判定を掛け合わせる仕組み
XLOOKUPでは、検索範囲に直接文字列の列を指定するだけでなく、条件式の結果を検索範囲として使えます。
たとえば商品コードがA001で、色が青である行を探す場合は、A2:A5=A001という判定と、B2:B5=青という判定を同時に行います。
実際には検索値を入力したセルを使い、F2に商品コード、G2に色が入っている場合、次の数式を入力します。
=XLOOKUP(1,(A2:A5=F2)*(B2:B5=G2),D2:D5,”該当なし”)
各条件式は一致した行でTRUE、不一致の行でFALSEを返します。
計算時にはTRUEが1、FALSEが0として扱われるため、両方の条件を満たす行だけが1×1で1になります。
XLOOKUPの検索値を1にする理由は、両方の条件が一致した行の判定結果だけが1になるためです。
片方の条件でも一致しない行は0になり、検索対象から外れます。
この考え方を理解すると、商品コードと色だけでなく、日付、店舗、担当者などの条件にも応用できます。
単価を返す基本数式
次に、検索条件を入力するセルと戻り値の列を明確にして、単価を取り出す数式を確認していきます。
F2へA001、G2へ青を入力し、H2へ次の式を入力してください。
=XLOOKUP(1,(A2:A5=F2)*(B2:B5=G2),D2:D5,”該当なし”)
結果として、A001かつ青の行にある単価130がH2へ表示されます。
最後の該当なしは、条件に合うデータがないときに表示する文字列です。
この引数を省略すると該当データがない場合にエラー表示となるため、実務用の一覧では指定しておくと見やすくなります。
数値として空白を返したい場合は、該当なしの部分を””に変更する方法もあります。
ただし、空白を返す設定ではデータが存在しないことに気付きにくい場合もあるため、用途に合わせて使い分けましょう。
範囲の行数をそろえる注意点
続いて、複数条件のXLOOKUPで起こりやすい範囲指定の不一致を確認していきます。
A2:A5とB2:B5のように、条件範囲は必ず同じ開始行と終了行にそろえます。
返す範囲であるD2:D5も、検索範囲と同じ4行にそろえる必要があります。
A2:A100とB2:B99のように行数が異なると、数式は正しく計算できません。
データが増える可能性がある表では、範囲をテーブル化して列名で参照すると、行の追加にも対応しやすくなります。
たとえばテーブル名を商品表にした場合、商品表[商品コード]のような構造化参照も利用できます。
数式を作った後は、条件を変えて複数の組み合わせを検索し、意図した行の値が返るか確認することが大切です。
【操作のポイント】条件範囲と返す範囲は、先頭行と末尾行を必ず統一します。
複数列の値をまとめて抽出する設定
続いては、ひとつの検索結果から商品名や単価など、複数列の値をまとめて取り出す設定を確認していきます。
| 検索条件 | 入力値 | 抽出列 |
|---|---|---|
| 商品コード | A001 | 商品名 |
| 色 | 青 | 単価 |
XLOOKUPの返す範囲には、1列だけでなく横に連続する複数列を指定できます。
横方向の返す範囲指定
まずは、商品名と単価を一度に表示する返す範囲の指定について解説していきます。
検索条件がF2とG2にあり、商品名と単価がC列からD列にある場合は、返す範囲をC2:D5とします。
=XLOOKUP(1,(A2:A5=F2)*(B2:B5=G2),C2:D5,”該当なし”)
この数式をH2へ入力すると、H2には商品名、I2には単価が自動的に展開されます。
このように数式の結果が隣のセルへ広がる動作を、動的配列によるスピルと呼びます。
返す範囲をC列からD列までにすると、検索で見つかった行の2列分の情報を同時に取得できます。
あらかじめH2の右側に文字や数式が入っているとスピルできないため、結果が広がるセルは空けておきましょう。
抽出先の見出しと結果の配置
次に、検索結果が何を表すか分かりやすくするための配置を確認していきます。
H1に商品名、I1に単価という見出しを用意してから、H2にXLOOKUPの式を入力すると、表として読みやすくなります。
検索条件の入力欄には背景色を付け、結果欄には別の罫線を設定すると、入力場所と計算結果を区別しやすくなります。
複数の担当者が使うファイルでは、入力セルだけを薄い黄色にするような運用も便利です。
検索対象のデータに空白が含まれると、空白そのものが戻り値として表示されることがあります。
これは検索失敗ではなく、該当行のセルが空欄である状態です。
該当なしの文字列と空欄を区別したい場合は、元データの入力漏れも確認しましょう。
複数列抽出でのスピルエラー
続いては、複数列の抽出時に表示されるスピルエラーへの対処を確認していきます。
H2に数式を入力したのに結果が広がらない場合、表示先のH2:I2付近に値、数式、結合セルが存在していないか調べます。
スピル範囲内にひとつでも入力済みのセルがあると、XLOOKUPは結果を展開できません。
不要な入力を削除するか、数式を空いている位置へ移動すると解決できます。
また、Excelテーブルの内部では動的配列の結果をそのまま展開しにくいことがあります。
複数列の結果を出す数式は、通常のセル範囲に配置するほうが扱いやすいでしょう。
【操作のポイント】複数列を返すときは、結果が横へ広がる先のセルを空白にします。
三つ以上の検索条件を指定する方法
続いては、3条件以上を使ってXLOOKUPで検索する方法を確認していきます。
| 日付 | 店舗 | 担当者 | 売上 |
|---|---|---|---|
| 4月1日 | 東京店 | 田中 | 50000 |
| 4月1日 | 大阪店 | 田中 | 42000 |
| 4月2日 | 東京店 | 佐藤 | 68000 |
2条件の式に条件式を追加するだけで、日付、店舗、担当者のような3条件検索も実現できます。
三条件を掛け合わせる数式
それではまず、3つの条件をすべて満たす売上を取り出す数式について解説していきます。
A列が日付、B列が店舗、C列が担当者、D列が売上であり、F2からH2に検索条件を入力する例です。
=XLOOKUP(1,(A2:A4=F2)*(B2:B4=G2)*(C2:C4=H2),D2:D4,”該当なし”)
日付、店舗、担当者のすべてが一致した行だけ、3つの判定がすべて1となります。
条件を追加するときは、括弧で囲んだ条件式を*(アスタリスク)で続けるだけです。
条件が多くなるほど、入力セルの見出しを明確にし、列の意味を取り違えないようにする必要があります。
日付を条件にする場合は、見た目が同じでも片方が文字列、もう片方が日付データになっていると一致しないことがあります。
表示形式ではなく、セルに保存された値の種類も確認しましょう。
Excel画面での数式入力
続いては、数式バーを使って3条件のXLOOKUPを入力する操作画面を確認していきます。
F2からH2へ条件を入力し、結果を表示したいJ2を選択して数式バーへ式を貼り付けます。
赤枠で示した3つの入力セルは、検索する条件を変更する場所です。
J2の結果を確認できたら、別の店舗や担当者へ入力値を変え、検索結果が連動するか試してみましょう。
数式を下へコピーする場合は、検索範囲を絶対参照にする必要があります。
=XLOOKUP(1,($A$2:$A$4=F2)*($B$2:$B$4=G2)*($C$2:$C$4=H2),$D$2:$D$4,”該当なし”)
数式をコピーする際の絶対参照
続いては、複数行の検索表へ数式をコピーする際の参照方法を確認していきます。
検索元データの範囲は、数式をコピーしても動かないように$記号で固定します。
一方でF2、G2、H2のような検索条件セルは、行ごとに条件を変えるなら相対参照のままにします。
検索範囲だけを絶対参照にし、条件入力セルは用途に応じて固定することが、コピー時の基本です。
F2からH2を横方向に展開する表なら、列だけを固定するF$2のような混合参照も役立ちます。
【操作のポイント】三条件検索では日付のデータ型と、数式コピー時の絶対参照を確認します。
文字列結合を使った複数条件検索
続いては、複数の条件をひとつの文字列に結合して検索する方法を確認していきます。
| 社員番号 | 部署 | 氏名 | 内線 |
|---|---|---|---|
| 1001 | 営業 | 山田 | 301 |
| 1001 | 開発 | 鈴木 | 512 |
| 1002 | 営業 | 佐々木 | 302 |
掛け算を使う方法が標準的ですが、文字列の連結を利用する数式も、条件の組み合わせを視覚的に理解しやすい方法です。
アンパサンドで条件を連結する数式
それではまず、社員番号と部署を連結した検索値を作る数式について解説していきます。
=XLOOKUP(F2&G2,A2:A4&B2:B4,D2:D4,”該当なし”)
F2が1001、G2が開発なら、検索値は1001開発となります。
検索範囲側でもA列とB列を同じ順番で結合するため、1001開発という文字列を検索できます。
検索値の結合順序と、検索範囲の結合順序は必ず一致させます。
この数式はMicrosoft 365やExcel 2021以降など、動的配列に対応するExcelで使いやすい構成です。
古いバージョンでは配列数式として扱われる場合があるため、利用しているExcelの機能も確認しましょう。
区切り文字を加える考え方
次に、連結した文字列が偶然同じになることを避ける区切り文字の考え方を確認していきます。
たとえば12と345という条件と、123と45という条件は、単純に連結するとどちらも12345になります。
このような重複を避けるには、条件の間へ区切り文字を入れます。
=XLOOKUP(F2&”|”&G2,A2:A4&”|”&B2:B4,D2:D4,”該当なし”)
縦棒を挟むことで、12|345と123|45を異なる値として判定できます。
区切り文字には、元データに含まれにくい文字を選ぶと安全です。
ただし、元の文字列に同じ記号が含まれる可能性も考え、データのルールを確認したうえで採用しましょう。
結合方式と掛け算方式の使い分け
続いては、文字列結合方式と掛け算方式の違いを確認していきます。
条件式を掛け合わせる方式は、各条件が何を判定しているか分かりやすく、数値、日付、文字列が混在する表でも扱いやすい方法です。
文字列結合方式は、検索キーそのものを組み立てる感覚で数式を読みやすくしたいときに向いています。
通常は掛け算方式を基本にし、連結キーが業務上すでに定義されている場合に文字列結合方式を検討すると整理しやすいでしょう。
どちらの方式でも、重複する組み合わせが存在する場合は、最初に見つかった行だけが返されます。
【操作のポイント】文字列を連結する場合は、条件の順番と区切り文字の有無を統一します。
複数一致とエラー表示の確認方法
続いては、同じ条件に複数の行が一致するケースと、該当なしを確認する方法について解説していきます。
| 注文番号 | 商品コード | 数量 |
|---|---|---|
| 10001 | A001 | 3 |
| 10001 | A001 | 5 |
| 10002 | B002 | 2 |
複数条件検索では、条件が一意であるかどうかによって、期待する結果が変わります。
最初の一致だけが返る動作
それではまず、XLOOKUPが複数の一致を見つけた場合の動作について解説していきます。
XLOOKUPは、上から順に検索して最初に見つかった一致を返します。
注文番号10001と商品コードA001を条件に数量を検索すると、最初の行にある3が返ります。
XLOOKUPは複数一致を一覧として返す関数ではなく、原則として最初の一致を返す関数です。
注文データのように同じ組み合わせが複数行あり得る表では、注文番号だけではなく明細番号も条件に加えるなど、一意になる条件を設定します。
データの並び順が変わると返る値も変わる可能性があるため、重複を放置したまま集計に使うことは避けましょう。
FILTER関数との組み合わせ
次に、複数一致したすべての行を抽出したい場合のFILTER関数について確認していきます。
複数条件に合う明細を一覧表示したい場合は、XLOOKUPよりFILTER関数が適しています。
=FILTER(A2:C4,(A2:A4=F2)*(B2:B4=G2),”該当なし”)
この式では、A列からC列のうち、F2とG2で指定した条件に一致する行をすべて表示します。
XLOOKUPはひとつの代表値を取り出す用途、FILTERは該当する複数行を抽出する用途と考えると選びやすくなります。
件数や合計値が必要な場合は、FILTERの結果をSUM関数やROWS関数と組み合わせる方法もあります。
目的が値の参照なのか、対象データの洗い出しなのかを先に決めることが大切です。
該当なしとエラーの原因
続いては、該当なしやエラーが表示されたときに確認したい項目を解説していきます。
まず、検索条件のセルに余分なスペースが入っていないか確認します。
見た目では分からない全角スペース、半角スペース、改行が含まれていると、一致しない原因になります。
文字列の前後にある不要な空白は、TRIM関数やCLEAN関数で整えると検索精度が上がります。
数値が文字列として保存されているケースも、条件不一致の原因です。
必要に応じてVALUE関数で数値化するか、セルのエラー表示から数値へ変換します。
さらに、検索範囲と返す範囲のサイズ、日付形式、絶対参照の位置も確認しましょう。
【操作のポイント】同じ条件が複数ある場合は、XLOOKUPでなくFILTER関数の利用も検討します。
まとめ エクセルのXLOOKUPで複数条件を検索する方法
エクセルのXLOOKUPで複数条件を検索する場合は、各条件式を掛け合わせ、すべて一致する行だけを1にする数式が基本です。
=XLOOKUP(1,(条件範囲1=条件値1)*(条件範囲2=条件値2),返す範囲,”該当なし”)という形を覚えておくと、商品コードと色、日付と店舗、担当者と部署など、さまざまな表で活用できます。
検索範囲と返す範囲の行数をそろえ、条件入力セルと元データの型を一致させることが正確な抽出につながります。
複数列の値を返すときは、返す範囲を横方向に指定し、スピル先のセルを空けておきましょう。
3条件以上でも条件式を追加すれば対応でき、数式をコピーする場合は検索範囲を絶対参照にすることが重要です。
また、同じ条件に複数行が一致する表では、XLOOKUPは最初の一致だけを返します。
すべての該当行を確認したいときはFILTER関数を使い、目的に合った関数を選びましょう。
まずは小さなサンプル表で数式を試し、検索条件を変えながら結果を確認すると、実務のファイルにも安心して応用できます。
