Excel多条件查找：如何让Timesheet!C2显示$10、C3显示$20？

需求说明 #

需让Timesheet工作表的C2单元格显示$10、C3单元格显示$20，核心逻辑是依据Timesheet表的Punch-in时间，匹配Rates表中对应时间区间的Value费率值，替代原固定查询的VLOOKUP公式。

具体实现方案 #

方案1：使用XLOOKUP（Excel 365/2021及以上版本） #

在Timesheet的C2单元格输入以下公式，下拉填充至C3：

=XLOOKUP(TRUE, (Rates!B2:B4<=Timesheet!A2)*(Rates!C2:C4>=Timesheet!A2), Rates!D2:D4, "")

• 逻辑解析：通过(Rates!B2:B4<=Timesheet!A2)*(Rates!C2:C4>=Timesheet!A2)生成布尔数组，判断当前打卡时间是否落在对应费率的时间区间内，XLOOKUP定位到第一个匹配TRUE的行，返回对应的Value值。

方案2：使用INDEX+MATCH组合（兼容全版本Excel） #

如果你的Excel版本不支持XLOOKUP，可使用兼容性更强的组合公式：

=INDEX(Rates!D2:D4, MATCH(1, (Rates!B2:B4<=Timesheet!A2)*(Rates!C2:C4>=Timesheet!A2), 0))

• 逻辑解析：同样通过布尔相乘生成匹配条件，MATCH找到符合条件的行号，INDEX返回该行对应的费率值。

注意事项 #

• 确保Rates表的Start和End列是标准时间格式，而非文本格式，否则会导致时间区间判断失效。
• 若Rates表存在多个重叠的时间区间，公式会返回第一个匹配的费率值；如需处理多区间匹配场景，可根据实际需求调整逻辑。

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