Office 2019中基于Emp ID和生效日期查找对应Rate的Excel公式问题

问题核心 #

需要从数据表中匹配指定员工ID且生效日期≤交易日期的最新生效费率（即离交易日期最近的生效日期对应的费率），原数组公式因仅限定年份，导致同一年存在多费率时无法定位正确结果。

可行公式方案 #

方案1：数组公式（需按Ctrl+Shift+Enter组合键确认输入） #

{=INDEX(Table[Rate],MATCH(MAX(IF((Table[Emp ID]=A2)*(Table[Effective Date]<=B2),Table[Effective Date])),Table[Effective Date],0))}

• 逻辑：先筛选出当前员工（A2）且生效日期不晚于交易日期（B2）的所有生效日期，取其中最大的日期（即最新生效日期），再匹配该日期对应的费率。

方案2：使用MAXIFS的非数组公式（无需组合键输入） #

=INDEX(Table[Rate],MATCH(MAXIFS(Table[Effective Date],Table[Emp ID],A2,Table[Effective Date],"<="&B2),Table[Effective Date],0))

• 逻辑：用MAXIFS直接定位符合员工ID、生效日期≤交易日期的最大生效日期，再通过MATCH+INDEX提取对应费率，操作更简便。

注意事项 #

• 确保数据表中Table[Effective Date]列是标准日期格式，避免文本格式导致匹配错误。
• 同一员工的生效日期需唯一，若存在同一天多个费率的情况，MATCH会返回第一个匹配行的费率。

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