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

Excel
2026.07.22
この記事は約13分で読めます。

この記事で分かること

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

公開済みの XLOOKUP 記事では基本的な使い方を説明しました。この記事では「もう1つ条件を加えたい」「複数行を返したい」という場面を取り上げ、XLOOKUP で解く方法と FILTER で解く方法を並べて、どちらを選ぶかを説明します。

どんな場面で使うか

  • 商品コードと入出庫区分の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 入庫

この記事の数式は、すべて「検索」シートに入力する前提です。 数式の中の F2・G2 はシート名を付けていないため、「在庫ログ」シート側に貼ると空のセルを見にいってしまい、結果が変わります。

XLOOKUP が返すのは1行だけ

XLOOKUP が受け取る検索列は1つで、返すのも1行分です。複数の条件で絞りたい場合は工夫が必要になります。

ただし「1行分」は「最初の行だけ」という意味ではありません。 既定では最初に一致した行を返しますが、引数の指定で最後に一致した行を返すこともできます（方法1で説明します）。XLOOKUP にできないのは複数の行をまとめて返すことで、そこを FILTER が補います。引数の詳細は XLOOKUP 記事にあります。

なお、返す範囲を 在庫ログ!A2:D100 のように複数列で指定すれば、その行の4列分がまとめて返ります。

この記事では、次の2つを扱います。

  1. XLOOKUP の引数で & 連結する — 検索値と検索範囲の両方を連結して1つのキーにする（方法1）
  2. FILTER を使う — 複数条件を指定して、条件に合う行を取り出す（方法2・方法3）

元データに補助列を足せる場合は、「在庫ログ」シートの E2 に =A2&"|"&B2 を入れて、その列を検索列にするのが一番単純です（この式だけは「在庫ログ」側に入れます）。ただし元データが他システムの出力で列を足せないことも多いため、この記事では元データに手を加えない方法を説明します。

手順

方法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, "未登録")

同じキーが複数行あるとき、最後の行を返したい場合: 第6引数（検索モード）に -1 を指定すると、範囲の下から探して最後に一致した行を返します。

なお、以降の数式は読みやすさのために改行して載せています。セルに入れるときは1行で書いても同じです（数式の中で改行したい場合は、数式バーで Alt + Enter を押します。コードブロックをそのままコピーして貼ると、改行が行区切りとして扱われて複数のセルに分かれてしまうことがあります）。

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

サンプルデータでは 4/5 の 80 が返ります（-1 を付けない先ほどの式は 4/1 の 100 でした）。

第5引数の 0（完全一致）を省略しないでください。 "未登録", -1) と書くと -1 が第5引数の一致モード（次小値）として解釈され、エラーは出ないまま 100 が返ります。

もうひとつ注意点があります。-1 が返すのは「範囲の並び順で最後の行」であって、「日付が一番新しい行」ではありません。このサンプルでは A001 の入庫が 4/1 → 4/5 の順に並んでいるため両者が一致していますが、たとえば日付の降順に並べ替えると A001 の入庫は 4/5 → 4/1 の順になり、-1 が返すのは 4/1 の 100 に変わります。これもエラーにならず、それらしい数量が返るので気づけません。日付で選びたいときは、日付を条件に含めてください（後述の「期間で絞り込む」）。

方法2: FILTER で複数条件を指定して必要な列だけ取り出す

FILTER は第1引数に「取り出したい範囲」、第2引数に「条件」を書きます。範囲は1列でも表全体でもかまいません（表全体を渡す例は方法3で扱います）。条件どうしを *（掛け算）でつなぐと、両方を満たす行だけが残ります。

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

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

条件を掛け算でつなぐのは、Excel が TRUE を 1、FALSE を 0 として扱うためです。両方 TRUE の行だけ 1 × 1 = 1 で残り、片方でも FALSE なら 0 になって外れます。

一致する行が複数あれば、その全件が縦に並びます。サンプルデータでは「A001・入庫」が2行あるため、100 と 80 の2件が返ります（1行分しか返さない XLOOKUP との違いがここに出ます）。条件が増えて式が長くなってきたら、後述の LET で整理できます。

方法3: 一致した行を表全体（A〜D列）で取り出す

方法2が返していたのは数量（D列）だけでした。返す範囲を A2:D100 にすると、一致した行が4列分まとめて返ります。 ここでは条件を商品コードの1つだけにしています。

商品コードが F2 と一致する全行を取り出す:

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

FILTER は一致する全行をスピル（結果が自動で広がる動き）で展開します。ここが XLOOKUP との一番の違いです。

結果のイメージ:

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

サンプル例

ここからは、手順で紹介した書き方を実務でよくある形に組み立てます。データは基本説明で示した 在庫ログ シート（6行）をそのまま使い、条件は「検索」シートの F2・G2 に入力する前提です。

複数行の集計

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

F2・G2 に合う数量の合計を求める:

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

サンプルデータでは、A001 の入庫は 4/1 の 100 と 4/5 の 80 の2行なので 100 + 80 = 180 が返ります。

ただし、この例のように条件がすべて AND で済むなら SUMIFS のほうが短く書けます（=SUMIFS(在庫ログ!D2:D100, 在庫ログ!A2:A100, F2, 在庫ログ!B2:B100, G2)）。FILTER＋SUM が効いてくるのは、OR 条件を含むときや、合計だけでなく該当した行そのものも見たいときです。条件集計の関数の選び方は SUMIFS・COUNTIFS・配列数式、似た関数はどう使い分けるかで、OR 条件の書き方は FILTER関数で「条件に合う行だけ抽出」で説明しています。

期間（日付の範囲）で絞り込む

日付も条件の1つとして掛け合わせられます。「以上」と「以下」を別々の条件として書き、* でつなぎます。

F2 の商品コードについて、4/1〜4/3 の行だけを取り出す:

=FILTER(在庫ログ!A2:D100,
  (在庫ログ!A2:A100=F2)
  * (在庫ログ!C2:C100>=DATE(2025,4,1))
  * (在庫ログ!C2:C100<=DATE(2025,4,3)),
  "データなし")

サンプルデータでは次の2行が返ります。4/5 の入庫（80）は範囲の外なので外れます。

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

日付をセルで指定したい場合は DATE(2025,4,1) の部分をそのセル参照に置き換えます。文字列として "2025/4/1" と書くと比較できないことがあるので、日付はセルか DATE 関数で渡すほうが確実です。

LET で全体をまとめる

条件が複数になると式が長くなるため、LET で読みやすくします。LET は =LET(変数名, 値, 変数名, 値, …, 最後に返す式) の形で、変数名と値のペアを並べ、いちばん最後の引数が結果になります。変数名のルールや対応バージョンは LET関数の使い方で説明しています。

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

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

よくあるエラー

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

全角・半角の違いやスペースの混入が原因のことが多いです。原因によって使う関数が違い、TRIM が消せるのは半角スペースだけなので、全角が混じっているときは先に ASC を通します。見分け方と直し方は VLOOKUPが一致しない原因と直し方 にまとめてあります。あわせて区切り文字（|）を使うと誤一致を防げます。

検索欄を空にすると 0 が返る／0 が大量に並ぶ

条件を入れ替えようとして F2・G2 を消すと、"未登録" ではなく 0 が返ります。FILTER のほうは 0 がずらりと縦に並びます。

検索範囲を A2:A100 と実データ（7行目まで）より広く取っているため、空の F2・G2 が、範囲内の空行と「一致してしまう」のが原因です。エラーではないので警告も出ません。空欄のまま結果を見たいときは、次のように先に弾いておくと安全です。

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

0 が縦に並ぶ FILTER 側も、同じ形で包めば止まります。

=IF(F2="", "", FILTER(在庫ログ!A2:D100, (在庫ログ!A2:A100=F2) * (在庫ログ!B2:B100=G2), "データなし"))

#SPILL! エラーが出る

結果が広がろうとした先に別の値が入っているときに出ます。画面上は空に見えても広がれないことがあり、代表的なのは次の4つです。

  • 結合セルがある
  • 数式をテーブル（Ctrl + T で変換した表）の中に置いた。テーブル内のセルはスピルできないので、テーブルの外に出してください
  • スペースが1個だけ入っている
  • "" を返す数式が入っている。すぐ上で紹介した =IF(F2="", "", XLOOKUP(...)) のような式は、画面では空に見えても値が入っています。その位置に別の数式を広げることはできません

この記事の数式は、結果の形が4通りあります。置き場所を空けるべき向きが変わるので、分けて覚えてください。

数式 結果の形 空けておく向き
方法1 / SUM(FILTER(...)) / LET でまとめた例 1セル なし
方法2（返す範囲が D2:D100 の1列） 縦（行数は条件しだい） 下
1行×4列を返す式（返す範囲を A2:D100 にした XLOOKUP、後述の INDEX(FILTER(...), 1)） 横に4列 右
方法3 / 期間で絞り込む例（返す範囲が A2:D100） 縦にも横にも 下と右

テーブルの外で、下と右が空いているセルを選んでおけば、まず引っかかりません。

数式のセルを選ぶと点線の枠が出ますが、これは結果が広がろうとした範囲であって、じゃまをしているセルではありません。原因のセルを知るには、セル横の警告アイコン（!）をクリックして「妨げているセルの選択」を選びます。

FILTER で #CALC! エラーが出る

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

日付が 45748 のような数字で表示される

スピルで広がった結果は、参照元セルの表示形式を引き継ぎません。「在庫ログ」側で 2025/04/01 と表示されていても、貼り付け先の書式が「標準」のままだと、日付はシリアル値（4/1 なら 45748、4/2 なら 45749）で出ます。エラーではないので警告は出ず、数式を間違えたように見えます。日付が出ている列だけを選んで、[ホーム]タブの表示形式を「短い日付形式」にしてください。数量の列まで一緒に変えると、100 が 1900/4/9 のような日付になってしまいます。

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

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

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

これは縦のスピルが止まるだけで、結果は1行×4列（商品コード・入出庫・日付・数量）です。横には広がったままなので、1セルに収めたい場合は列番号も指定します。次は数量（4列目）だけを取り出す例です。

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

まとめ

場面 方法
2つのキーで1行参照 XLOOKUP の検索値と検索範囲を、区切り文字を挟んで連結する
複数条件で複数行を全て取り出す FILTER(範囲, 条件1 * 条件2)
期間で絞り込む (日付列>=開始) * (日付列<=終了) を条件に掛ける
複数行の集計 SUM(FILTER(...))（AND 条件だけなら SUMIFS）
式が長くなったら LET で変数化して整理

1行目の具体形は =XLOOKUP(F2 & "|" & G2, 在庫ログ!A2:A100 & "|" & 在庫ログ!B2:B100, 在庫ログ!D2:D100, "未登録") です。区切り文字を挟むのは、AB+C と A+BC がどちらも ABC になってしまう誤一致を防ぐためです。

XLOOKUP は1行参照、FILTER は複数行の取り出しと覚えておくと使い分けやすいです。条件が増えて式が長くなったら LET で整理します。

対応バージョン: Microsoft 365 または Excel 2021 以降

関連記事

サンプルファイル

記事中で使用しているサンプルデータはこちらからダウンロードできます。

タイトルとURLをコピーしました