多单元格匹配+日期区间条件的Excel Lookup公式求助

适用场景 #

需要在另一工作表中匹配B列&A列的组合值，同时验证C1的日期是否落在对应行的AR列（起始日期）与AS列（结束日期）区间内，返回AU列的数值，且公式可向下复制自动适配每行的查找值。

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

直接使用动态数组函数，无需手动按数组快捷键：

=XLOOKUP(TRUE, (数据!AL:AL=B2&A2)*(数据!AR:AR<=$C$1)*(数据!AS:AS>=$C$1), 数据!AU:AU, "未找到")

参数说明 #

• B2&A2：相对引用当前行的B、A列值，向下复制时会自动变为B3&A3、B4&A4，适配每行的查找组合
• $C$1：绝对引用固定日期单元格，避免复制公式时日期引用偏移
• (数据!AL:AL=B2&A2)*(数据!AR:AR<=$C$1)*(数据!AS:AS>=$C$1)：同时满足三个条件的布尔判断：
  1. 数据工作表AL列的值等于当前行的B&A组合
  2. 数据工作表AR列的日期 ≤ C1的日期
  3. 数据工作表AS列的日期 ≥ C1的日期
• 数据!AU:AU：返回匹配行对应的AU列数值
• "未找到"：无匹配结果时的自定义返回值，可根据需求修改

方案2：INDEX+MATCH（兼容所有Excel版本） #

如果使用旧版Excel（2019及以下），用数组公式实现：

=INDEX(数据!AU:AU, MATCH(1, (数据!AL:AL=B2&A2)*(数据!AR:AR<=$C$1)*(数据!AS:AS>=$C$1), 0))

注意事项 #

• 旧版Excel中输入公式后需按 Ctrl+Shift+Enter 触发数组计算（Excel 365/2021 直接回车即可）
• 引用规则与XLOOKUP一致：B2&A2用相对引用，$C$1用绝对引用

原公式失效原因排查 #

你的旧公式无法复制、不自动调整，大概率是以下问题：

• 错误地给B2&A2加了绝对引用（如$B$2&$A$2），导致复制后始终引用第一行的组合值
• 没有固定C1的引用（用了C1而非$C$1），复制后日期引用会偏移到C2、C3等
• 未使用数组逻辑同时组合多个条件，导致只匹配单一条件

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