Excel公式需求：匹配日期区间获取对应低值日期的项目
2026-7-25
根据ID匹配数据集并生成Excel公式解决方案
需求说明
针对相同ID，按以下规则匹配两个数据集，用Excel公式填充数据集2的Item列：
- 若数据集2的「修改日期（Date Modified）」小于该ID在数据集1中的最小日期，取最小日期对应的Item；
- 若修改日期处于该ID在数据集1中的某两个日期区间内，取区间低值日期对应的Item；
- 若修改日期大于该ID在数据集1中的最大日期，取最大日期对应的Item。
数据集1
| ID | Date | Item |
|---|---|---|
| 1 | 5/10/2022 | Apple |
| 1 | 1/10/2022 | Orange |
| 1 | 10/10/2022 | Strawberry |
| 2 | 1/10/2022 | Kiwi |
数据集2（待填充Item列）
| ID | Date Modified | Item |
|---|---|---|
| 1 | 30/9/2022 | |
| 1 | 4/10/2022 | |
| 1 | 9/10/2022 | |
| 1 | 11/10/2022 | |
| 2 | 9/9/2022 | |
| 2 | 10/10/2022 |
预期结果
| ID | Date Modified | Item |
|---|---|---|
| 1 | 30/9/2022 | Orange |
| 1 | 4/10/2022 | Orange |
| 1 | 9/10/2022 | Apple |
| 1 | 11/10/2022 | Strawberry |
| 2 | 9/9/2022 | Kiwi |
| 2 | 10/10/2022 | Kiwi |
Excel公式实现
适用于Excel 365/2021（动态数组公式）
假设数据集1位于A2:C5，数据集2位于E2:G7（G列为待填充列）。在G2单元格输入以下公式，按回车自动填充整列：
=XLOOKUP(MAX(FILTER($B$2:$B$5,$A$2:$A$5=E2,$B$2:$B$5<=F2)),FILTER($B$2:$B$5,$A$2:$A$5=E2),FILTER($C$2:$C$5,$A$2:$A$5=E2))
逻辑解析：
FILTER($B$2:$B$5,$A$2:$A$5=E2,$B$2:$B$5<=F2)：筛选当前ID下，日期≤修改日期的所有数据集1日期；MAX(...)：取上述筛选结果的最大值，即符合规则的目标日期；XLOOKUP根据目标日期匹配对应的Item。
适用于旧版Excel（数组公式）
若使用不支持动态数组的旧版Excel，在G2单元格输入以下公式，按Ctrl+Shift+Enter完成数组输入后下拉填充：
=INDEX($C$2:$C$5,MATCH(MAX(IF($A$2:$A$5=E2,IF($B$2:$B$5<=F2,$B$2:$B$5))),IF($A$2:$A$5=E2,$B$2:$B$5),0))
逻辑解析：
- 嵌套
IF筛选当前ID下日期≤修改日期的所有日期； MAX(...)取筛选结果的最大值；MATCH定位该日期在当前ID日期列的位置，INDEX取出对应Item。
内容的提问来源于stack exchange，提问作者poppp
相关产品推荐
相关产品推荐
