Excel公式求助：匹配Sheet2日期至Sheet1日期范围并获取Pay Rate

场景1：匹配Sheet2单个日期所在的Sheet1日期区间 #

如果需要根据Sheet2的A2日期（1/11/2025），找到Sheet1中包含该日期的区间对应的Pay Rate，可使用以下公式：

方法1：INDEX+MATCH（兼容新旧Excel版本） #

=INDEX(Sheet1!$C$2:$C$5,MATCH(TRUE,(Sheet1!$A$2:$A$5<=Sheet2!$A$2)*(Sheet1!$B$2:$B$5>=Sheet2!$A$2),0))

注：旧版Excel需按Ctrl+Shift+Enter作为数组公式执行，新版Excel直接回车即可。

方法2：XLOOKUP（仅适用于Excel 365/2021及以上版本） #

=XLOOKUP(TRUE,(Sheet1!$A$2:$A$5<=Sheet2!$A$2)*(Sheet1!$B$2:$B$5>=Sheet2!$A$2),Sheet1!$C$2:$C$5)

场景2：匹配Sheet2日期区间与Sheet1重叠的所有Pay Rate #

如果Sheet2的日期区间（1/9/2025~1/11/2025）与Sheet1多个区间重叠，需要返回所有对应的Pay Rate（逗号分隔），可使用：

=TEXTJOIN(", ",TRUE,IF((Sheet1!$A$2:$A$5<=MAX(Sheet2!$A$2:$B$2))*(Sheet1!$B$2:$B$5>=MIN(Sheet2!$A$2:$B$2)),Sheet1!$C$2:$C$5,""))

注：此公式自动处理Sheet2中Date Start和Date End顺序颠倒的情况，返回结果为1, 2。

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