如何在Excel同一单元格用两个VLOOKUP函数匹配楼宇对应月度租金

前置准备 #

• 先将Tab1的Startdate、Enddate列转换为Excel标准日期格式，避免文本格式无法参与日期判断
• 将Tab2的月份表头（如December 2020）统一转换为对应月份的第一天日期，比如2020/12/1，之后可以设置单元格自定义格式为[$-en-US]mmmm yyyy，即可保留原来的月份+年的展示效果，不影响使用

公式实现 #

假设参数如下：

• Tab2中待填充单元格为B2，对应A列的楼宇值为A2，对应表头月份日期为B1
• Tab1的租赁数据范围为A:E列（A为楼宇，C为起始日期，D为结束日期，E为租金）
  你可以使用嵌套的两个VLOOKUP实现需求，公式如下（如果是Excel 2019及更早版本，需要按Ctrl+Shift+Enter录入数组公式，365/2021及以后版本直接回车即可）：

=VLOOKUP(1, CHOOSE({1,2}, (VLOOKUP(A2, Tab1!A:E, 1, FALSE)=A2)*(Tab1!C:C<=EOMONTH(B$1,0))*(Tab1!D:D>=B$1), Tab1!E:E), 2, FALSE)

逻辑说明 #

1. 内层VLOOKUP：先匹配到Tab1中所有和当前楼宇一致的记录，作为后续判断的基础范围
2. 日期判断逻辑：判断当前表头月份是否落在对应租赁记录的起止日期范围内，符合条件则返回1，不符合返回0
3. 外层VLOOKUP：查找值为1，找到符合条件的记录后，返回对应的租金金额

优化补充 #

如果需要避免无匹配记录时返回#N/A错误，可以嵌套IFERROR函数，把错误值替换为0或者空值，示例：

=IFERROR(VLOOKUP(1, CHOOSE({1,2}, (VLOOKUP(A2, Tab1!A:E, 1, FALSE)=A2)*(Tab1!C:C<=EOMONTH(B$1,0))*(Tab1!D:D>=B$1), Tab1!E:E), 2, FALSE), 0)

注意事项 #

• 请确保Tab1中同一楼宇同一时间段不存在重叠的租赁记录，否则公式只会返回第一个匹配到的租金
• 公式录入后可以横向、纵向拖拽填充所有单元格，注意列号行号的绝对引用符号$不要漏

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