【Excel 関数】XLOOKUP × FILTER で複数条件の参照をシンプルにする

この記事で分かること
どんな場面で使うか
基本説明
1. XLOOKUP 単独では難しい場面
手順
サンプル例
よくあるエラー
まとめ
関連記事
サンプルファイル

この記事で分かること

XLOOKUP と FILTER を組み合わせる理由と使い方
複数条件でのマスター参照の書き方
XLOOKUP だけでは難しい「条件に合う複数行の取得」を FILTER で補う方法
LET を使ってさらに読みやすくする方法

公開済みの XLOOKUP 記事では基本的な使い方を説明しました。この記事では「もう1つ条件を加えたい」「複数行を返したい」という場面に対応する組み合わせを説明します。

どんな場面で使うか

商品コードと入出庫区分の2つのキーで在庫マスターを参照したい
担当者コードだけでなく、期間（年月）も一致する行の値を取りたい
1つのキーに対して複数行ある場合に、条件に合う全行を取り出したい
VLOOKUP や XLOOKUP の単独使用では複数条件が組めずに困っている

基本説明

この記事では、次のような「在庫ログ」シートを例に使います。

A: 商品コード	B: 入出庫	C: 日付	D: 数量
A001	入庫	2025/04/01	100
A001	出庫	2025/04/02	30
A002	入庫	2025/04/01	50
A002	出庫	2025/04/03	20
A001	入庫	2025/04/05	80
A003	入庫	2025/04/01	200

検索用の条件はシート「検索」の F2（商品コード）・G2（入出庫区分）に入力します。

F: 商品CD	G: 入出庫
A001	入庫

XLOOKUP 単独では難しい場面

XLOOKUP はデフォルトで1つの検索列に対して1つの値を返します。複数条件を使いたい場合は工夫が必要です。

方法の選択肢:

補助列を作る — 検索キーを連結した列を元データに追加する（例: =A2&B2）
XLOOKUP の引数で配列を使う — 検索値・検索範囲両方に & 連結を使う
FILTER を使う — 複数条件を指定して条件に合う行を取り出す

3の FILTER 方式が最も柔軟で読みやすいため、この記事では FILTER との組み合わせを中心に説明します。

手順

方法1: XLOOKUP の引数で複数条件を使う

XLOOKUP の検索値と検索範囲をそれぞれ & で連結します。

商品コード（A列）＋入出庫区分（B列）の組み合わせで数量（D列）を参照する:

=XLOOKUP(F2 & G2, 在庫ログ!A2:A100 & 在庫ログ!B2:B100, 在庫ログ!D2:D100, "未登録")

F2 & G2: 検索値（商品コードと入出庫区分を連結）
在庫ログ!A2:A100 & 在庫ログ!B2:B100: 検索範囲（A列とB列を連結した仮想列）
在庫ログ!D2:D100: 返す値（数量）

F2 =「A001」・G2 =「入庫」のとき、結果は 100（4/1 の入庫行）です。連結キーで最初に一致した1行の数量が返ります。

注意点: & 連結では値の境界が曖昧になることがあります（例: AB+C と A+BC が同じ ABC になる）。コードの区切り文字として普通のデータに出てこない文字（| など）を間に入れると安全です。

=XLOOKUP(F2 & "|" & G2, 在庫ログ!A2:A100 & "|" & 在庫ログ!B2:B100, 在庫ログ!D2:D100, "未登録")

方法2: FILTER で条件に合う行を取り出してから値を使う

FILTER で条件に合う行を絞り込んでから、その結果の特定列を参照します。

商品コードと入出庫区分が一致する行の数量を取り出す:

=LET(
  条件, (在庫ログ!A2:A100=F2) * (在庫ログ!B2:B100=G2),
  FILTER(在庫ログ!D2:D100, 条件, "未登録")
)

LET を使って条件式を変数化し、FILTER でその条件に合う行の数量（D列）だけを直接取り出しています。一致する行が複数あればスピルで全件返ります。サンプルデータでは「A001・入庫」が2行あるため、100 と 80 の2件が縦に展開されます（最初の1行しか返さない XLOOKUP との違いがここに出ます）。

方法3: 1つのキーに対して複数行を返す

XLOOKUP は一致する最初の1行しか返せませんが、FILTER なら一致するすべての行を返せます。

商品コードが「A001」の全行を取り出す:

=FILTER(在庫ログ!A2:D100, 在庫ログ!A2:A100="A001", "データなし")

XLOOKUP では最初の1行しか取れませんが、FILTER は一致する全行をスピルで展開します。

結果のイメージ:

商品コード	入出庫	日付	数量
A001	入庫	2025/04/01	100
A001	出庫	2025/04/02	30
A001	入庫	2025/04/05	80

サンプル例

複数条件のマスター参照

在庫管理で「商品コード」と「入出庫区分」の両方が一致する行の「数量」を取り出す例です。

マスターデータ（在庫ログ シート）:

商品コード	入出庫	日付	数量
A001	入庫	2025/04/01	100
A001	出庫	2025/04/02	30
A002	入庫	2025/04/01	50

F2 = 「A001」、G2 = 「入庫」のとき、数量（100）を取り出す:

=XLOOKUP(F2 & "|" & G2,
  在庫ログ!A2:A100 & "|" & 在庫ログ!B2:B100,
  在庫ログ!D2:D100,
  "データなし")

複数行の集計

条件に合う全行を取り出して合計を求める場合は、FILTER + SUM で対応できます。

「A001」の入庫数量の合計を求める:

=SUM(FILTER(在庫ログ!D2:D100, (在庫ログ!A2:A100="A001") * (在庫ログ!B2:B100="入庫"), 0))

サンプルデータでは 100 + 80 = 180 が返ります（SUMIFS と同等ですが、配列処理で複合条件に対応しやすい）。

LET で全体をまとめる

条件が複数になると式が長くなるため、LET で読みやすくします。

=LET(
  商品CD,   F2,
  入出庫CD, G2,
  商品列,   在庫ログ!A2:A100,
  入出庫列, 在庫ログ!B2:B100,
  数量列,   在庫ログ!D2:D100,
  条件,     (商品列=商品CD) * (入出庫列=入出庫CD),
  結果,     FILTER(数量列, 条件, 0),
  SUM(結果)
)

何を検索しているのかが変数名から読み取れるようになります。F2 =「A001」・G2 =「入庫」のとき、結果は先ほどと同じ 180 です。

よくあるエラー

XLOOKUP で & 連結後に一致しない

全角・半角の違いやスペースの混入が原因のことが多いです。一致しない場合は TRIM・SUBSTITUTE で前処理してから連結することを検討してください。また区切り文字（|）を使うことで誤一致を防げます。

FILTER で #CALC! エラーが出る

条件に一致する行がゼロの場合です。第3引数を指定してください。

スピルで複数行が出て困る

FILTER は一致する全行を返すため、1行だけ欲しい場合は INDEX で最初の行だけを取り出します。

=INDEX(FILTER(在庫ログ!A2:D100, 在庫ログ!A2:A100="A001", ""), 1)

まとめ

場面	方法
2つのキーで1行参照	`XLOOKUP(A&B, 範囲A&範囲B, 返す列)`
複数条件で複数行を全て取り出す	`FILTER(範囲, 条件1 * 条件2)`
複数行の集計	`SUM(FILTER(...))`
式が長くなったら	`LET` で変数化して整理