用动态公式替换Excel VLOOKUP：基于指定日期查询租金的需求

嘿，我来帮你把VLOOKUP替换成更灵活的动态数组公式！针对你根据指定日期（B1）查询对应时间段租金的需求，以下是几个实用的动态公式方案，适配Excel 365/2021及以后版本（支持动态数组功能）：

1. 首选：XLOOKUP函数（最直观的动态方案） #

假设你的租金变更日期在A2:A10区域，对应租金金额在B2:B10区域，B1是要查询的目标日期，直接用这个公式：

=XLOOKUP(B1, A2:A10, B2:B10, "", -1)

公式说明： #

• B1：要匹配的目标日期
• A2:A10：租金变更日期的数据源
• B2:B10：对应租金金额的数据源
• ""：如果没有匹配项时返回的内容（可自定义，比如"无数据"）
• -1：核心参数，表示查找小于等于目标日期的最大匹配值，刚好对应"最新生效的租金"的需求

如果把你的租金数据转换成Excel结构化表格（按Ctrl+T创建），比如命名为tblRentChanges，公式会更智能——后续新增租金变更记录时，公式会自动扩展范围：

=XLOOKUP(B1, tblRentChanges[租金变更日期], tblRentChanges[金额], "", -1)

2. INDEX+MATCH的动态写法 #

如果你更习惯用经典函数组合，这个动态版本也能实现需求，同样支持自动溢出：

=INDEX(B2:B10, MAX(IF(A2:A10<=B1, ROW(A2:A10)-ROW(A2)+1)))

公式说明： #

• IF(A2:A10<=B1, ROW(A2:A10)-ROW(A2)+1)：生成一个数组，符合日期条件的记录返回其在区域内的行号，不符合的返回FALSE
• MAX(...)：提取符合条件的最大行号（也就是最新的租金变更记录）
• INDEX(...)：根据行号返回对应租金金额

3. 批量生成月度租金计划表的技巧 #

如果你的右侧月度计划表是一系列日期（比如B1:B12是12个月度日期），动态数组公式可以直接批量返回结果，不用手动下拉填充：

=XLOOKUP(B1:B12, A2:A10, B2:B10, "", -1)

输入公式后，Excel会自动溢出填充所有月度对应的租金金额，完全适配动态计划表的需求。

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