如何在Excel中为Sheet2的每个ID查找小于DATE2（DATE3）的最近DATE1

嘿，这个需求在Excel里完全可以通过函数组合实现，我分两种场景给你拆解，不管你用的是新版还是旧版Excel都能适配：

如果你用的是这两个新版本，推荐两种简单的写法：

方法1：用XLOOKUP精准匹配 #

假设Sheet2的ID列是A列，DATE2（DATE3）列是B列，我们要在C列输出结果。在C2单元格输入公式：

=XLOOKUP(1,(Sheet1!$A:$A=A2)*(Sheet1!$B:$B<B2),Sheet1!$B:$B,"无匹配",0,-1)

然后下拉填充就行。

• 公式逻辑：(Sheet1!$A:$A=A2) 匹配和当前行相同的ID，(Sheet1!$B:$B<B2) 筛选出Sheet1中小于当前DATE2的DATE1；两个条件相乘得到布尔数组，XLOOKUP的最后一个参数-1是反向查找，会从符合条件的记录里找最后一条（也就是日期最大的那条），如果没找到匹配项就返回“无匹配”。

方法2：用MAXIFS直接取最大值 #

这个写法更直观，直接提取满足条件的最大日期：

=IF(MAXIFS(Sheet1!$B:$B,Sheet1!$A:$A,A2,Sheet1!$B:$B,"<"&B2)=0,"无匹配",MAXIFS(Sheet1!$B:$B,Sheet1!$A:$A,A2,Sheet1!$B:$B,"<"&B2))

• 公式逻辑：MAXIFS直接筛选出同ID且DATE1小于DATE2的最大日期；加IF判断是因为如果没有符合条件的记录，MAXIFS会返回0，我们把它改成“无匹配”更友好。

如果你的Excel版本比较老（比如2019及以前），就用数组公式来实现：
在C2单元格输入公式，然后按Ctrl+Shift+Enter完成数组输入（不要直接按回车），再下拉填充：

=IF(MAX(IF((Sheet1!$A$2:$A$1000=A2)*(Sheet1!$B$2:$B$1000<B2),Sheet1!$B$2:$B$1000,""))=0,"无匹配",MAX(IF((Sheet1!$A$2:$A$1000=A2)*(Sheet1!$B$2:$B$1000<B2),Sheet1!$B$2:$B$1000,"")))

• 注意：把公式里的Sheet1!$A$2:$A$1000和Sheet1!$B$2:$B$1000改成你实际的数据范围，别用整列，不然会卡顿。
• 公式逻辑：用IF嵌套筛选出符合条件的DATE1，再用MAX取最大值，最后用IF处理无匹配的情况。

额外注意事项 #

• 确保Sheet1和Sheet2里的日期列都是日期格式，别是文本格式，不然函数会识别错误。
• 如果数据量特别大，旧版的数组公式尽量缩小数据范围，提升运算速度。
• 要是有多个相同ID且相同DATE1的记录，以上方法都能正确提取到最大的那个日期。

内容的提问来源于stack exchange，提问作者konichiwa