在电子表格中匹配单元格条件时返回指定列范围的最新日期

源数据 #

目标需求 #

生成如下表格，自动计算每个Item对应Event=X的最新日期，无匹配项时显示None：

公式方案 #

1. 传统Excel版本（2019及更早）：数组公式 #

假设源数据范围：Date列=A2:A5，Item列=B2:B5，Event列=C2:C5；目标表格中当前Item在F2单元格，对应Latest X列公式写在G2：

=IF(MAX(IF((B$2:B$5=F2)*(C$2:C$5="X"),A$2:A$5,0))=0,"None",MAX(IF((B$2:B$5=F2)*(C$2:C$5="X"),A$2:A$5,0)))

输入完成后必须按 Ctrl+Shift+Enter 触发数组计算（Excel会自动给公式套上{}，别手动加）。

逻辑说明

• (B$2:B$5=F2)*(C$2:C$5="X")：生成布尔数组，同时满足Item匹配、Event=X的位置返回1，其余为0
• IF(...,A$2:A$5,0)：把符合条件的日期保留，不符合的替换为0
• MAX(...)：提取符合条件的日期最大值（即最新日期）
• 外层IF判断：如果MAX结果为0（无匹配项），显示None，否则显示最新日期

2. 新版Excel（365/2021及以后）：动态数组公式 #

用MAXIFS直接实现多条件最大值，无需数组快捷键：

=IF(MAXIFS(A$2:A$5,B$2:B$5,F2,C$2:C$5="X")=0,"None",MAXIFS(A$2:A$5,B$2:B$5,F2,C$2:C$5="X"))

或者用XLOOKUP从后往前查找（直接定位最新匹配项）：

=IFNA(XLOOKUP(1,(B$2:B$5=F2)*(C$2:C$5="X"),A$2:A$5,"None",0,2),"None")

逻辑说明

• MAXIFS：按「Item匹配」+「Event=X」两个条件直接求日期最大值
• XLOOKUP第6参数设为2，代表从数据末尾往前找，第一个符合条件的就是最新日期，无匹配时返回None

注意事项 #

• 确保源数据的Date列是日期格式，不是文本格式，否则MAX函数无法正确计算
• 公式里的区域引用（比如B$2:B$5）要加绝对引用符号$，下拉公式时源数据范围才不会偏移

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