Excel技术求助：匹配设备ID并提取最新日期对应Result值

针对你需要从《风险评估工作表》提取对应设备ID最新评估日期的Result值需求，提供以下几种适配不同Excel版本的公式方案：

前提说明 #

假设：

• 《风险评估工作表》（简称「风险表」）：A列=设备ID，C列=评估日期，D列=Result（请根据实际列号替换公式中的$D:$D）
• 《设备登记工作表》（简称「设备表」）：A列=设备ID，G列=设备状态（"YES"表示在用），Result列为需要填写的目标列（示例用I4单元格）

方案1：Excel 365/2021及以上（推荐，动态数组） #

在设备表Result列的单元格（如I4）输入以下公式，下拉自动填充：

=IF(AND(A4<>"",G4="YES"),
    XLOOKUP(1,('Risk Assessment'!$A:$A=A4)*('Risk Assessment'!$C:$C=MAXIFS('Risk Assessment'!$C:$C,'Risk Assessment'!$A:$A,A4)),'Risk Assessment'!$D:$D,"RISK NOT RECORDED"),
    "NOT IN USE"
)

逻辑说明：

1. 用MAXIFS快速筛选出当前设备ID对应的最新评估日期
2. 用XLOOKUP匹配「设备ID=当前ID」+「评估日期=最新日期」的组合，返回对应Result；无匹配时返回RISK NOT RECORDED
3. 设备未填写ID或状态非"YES"时，返回NOT IN USE

方案2：兼容旧版Excel（数组公式） #

若使用Excel 2019及更早版本，需按Ctrl+Shift+Enter输入数组公式（输入后公式会自动包裹{}）：

=IF(AND(A4<>"",G4="YES"),
    INDEX('Risk Assessment'!$D:$D,MATCH(1,('Risk Assessment'!$A:$A=A4)*('Risk Assessment'!$C:$C=MAX(IF('Risk Assessment'!$A:$A=A4,'Risk Assessment'!$C:$C))),0)),
    IFERROR("RISK NOT RECORDED","NOT IN USE")
)

逻辑说明：

1. 用MAX(IF(...))数组运算筛选当前设备的最新评估日期
2. 用MATCH定位同时满足设备ID和最新日期的行号
3. 用INDEX提取对应行的Result值

方案3：复用现有H列最新日期（高效优化） #

如果设备表H列的「下次风险评估日期」公式已正确返回最新日期，可直接复用该结果减少重复计算：

=IF(AND(A4<>"",G4="YES"),
    IF(H4="RISK NOT RECORDED",H4,INDEX('Risk Assessment'!$D:$D,MATCH(1,('Risk Assessment'!$A:$A=A4)*('Risk Assessment'!$C:$C=H4),0))),
    "NOT IN USE"
)

注意事项 #

• 替换公式中'Risk Assessment'!$D:$D为风险表实际存放Result的列（如Result在E列则改为$E:$E）
• 若风险表存在同一设备ID+同一最新日期的多行记录，公式会返回第一个匹配的Result值，可根据实际需求调整匹配逻辑
• 建议将两个工作表转换为Excel表（按Ctrl+T），公式会自动扩展到新增行，无需手动下拉填充

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