Excel中基于ID与日期返回对应状态的优化公式需求

问题背景 #

现有两个Excel工作表：

• Status Check（表1）：A列为目标日期，B列为ID，需在C2:C4区域返回对应状态
• Status History（表2）：包含ID、生效日期、状态三类数据（列位置可根据实际调整）

当前方案通过FILTER+TRANSPOSE+SORT在辅助列展开对应ID的生效日期与状态，再用IFS判断匹配结果，但大文件场景下计算资源占用过高，需仅在C列使用的无辅助列简洁公式。

解决方案 #

适用于Excel 365/2021（动态数组版本） #

在C2单元格输入以下公式，下拉填充或自动溢出至C4：

=XLOOKUP(1, (Status History!$A:$A=B2)*(Status History!$B:$B=A2), Status History!$C:$C, "无匹配")

若需求为匹配小于等于目标日期的最新生效状态（而非完全相等的日期），可调整为：

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

适用于旧版Excel（无动态数组支持） #

在C2单元格输入以下数组公式（按Ctrl+Shift+Enter确认后下拉填充至C4）：

=INDEX(Status History!$C:$C, MATCH(1, (Status History!$A:$A=B2)*(Status History!$B:$B=A2), 0))

如需处理无匹配的情况，嵌套IFERROR即可：

=IFERROR(INDEX(Status History!$C:$C, MATCH(1, (Status History!$A:$A=B2)*(Status History!$B:$B=A2), 0)), "无匹配")

公式说明 #

• XLOOKUP/INDEX+MATCH通过双重条件（ID匹配、日期匹配）直接定位目标状态，无需辅助列展开数据，大幅降低计算资源消耗
• 公式中的列引用需根据表2实际列位置调整（比如表2的ID在B列、生效日期在C列、状态在D列，对应修改引用即可）
• 针对“最新生效状态”的需求，第二个XLOOKUP公式通过-1参数实现反向查找，返回符合条件的最后一条记录

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