求高效Excel公式：返回价格更低且Include=Y的最新日期条目

需求回顾 #

对表格中每一行，需实现：

• 若当前行Include字段（C列）为Y，则查找所有满足**Include=Y且价格（D列）<当前行价格**的条目中，日期最新的那条，返回对应A列内容；无符合条件条目时返回Lowest to Date
• 若当前行Include不为Y，返回N/A

原公式问题分析 #

你提供的公式依赖行号排序（取最小行号），完全未纳入日期字段的筛选逻辑，因此当日期与行号不匹配（非降序）时，必然无法返回正确的最新日期条目。

方案1：Excel 365/2021 动态数组公式（高效简洁） #

利用XLOOKUP的降序查找特性，直接定位最新日期的条目：

=IF(C2="Y",IFERROR(XLOOKUP(MAX(IF((C$2:C$68="Y")*(D$2:D$68<D2),B$2:B$68)),B$2:B$68,A$2:A$68,"Lowest to Date",0,-1),"Lowest to Date"),"N/A")

• 核心逻辑：MAX(IF((C$2:C$68="Y")*(D$2:D$68<D2),B$2:B$68))先筛选出所有符合条件的日期，取最大值（最新日期）
• XLOOKUP按降序查找该日期，返回对应A列内容，无匹配时返回指定文本

方案2：兼容旧版Excel（无动态数组） #

有MAXIFS（Excel 2019及以后）

=IF(C2="Y",IFERROR(INDEX(A$2:A$68,MATCH(MAXIFS(B$2:B$68,C$2:C$68,"Y",D$2:D$68,"<"&D2),B$2:B$68,0)),"Lowest to Date"),"N/A")

• MAXIFS直接计算符合双条件的最大日期，再通过MATCH+INDEX定位对应A列内容

无MAXIFS（更早版本Excel）

用AGGREGATE替代MAXIFS实现条件取最大日期：

=IF(C2="Y",IFERROR(INDEX(A$2:A$68,MATCH(AGGREGATE(14,6,B$2:B$68/((C$2:C$68="Y")*(D$2:D$68<D2)),1),B$2:B$68,0)),"Lowest to Date"),"N/A")

• AGGREGATE(14,6,...)：14代表取第1大值，6忽略错误值；通过除法将不符合条件的条目转为错误值，最终得到符合条件的最大日期

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