如何在Excel中结合国家与交付日期匹配日期范围返回对应金额？

需求说明 #

以「国家」和「交付日期」为查询条件，匹配服务费表格中同一国家且交付日期处于起止日期范围内的记录，返回对应COL_5列的金额。

解决方案（分Excel版本） #

假设：

• 查询表：国家在A2单元格，交付日期在B2单元格
• 服务费表格：国家列COL_1、起始日期COL_2、结束日期COL_3、目标金额列COL_5

方案1：Excel 365/2021（支持动态数组）

使用XLOOKUP函数，直接输入公式即可：

=XLOOKUP(1,(服务费表!COL_1=A2)*(服务费表!COL_2<=B2)*(服务费表!COL_3>=B2),服务费表!COL_5,"无匹配")

• 公式逻辑：三个条件(国家匹配)*(交付日期≥起始日期)*(交付日期≤结束日期)同时满足时返回1，XLOOKUP定位第一个符合条件的记录，返回对应COL_5金额；无匹配时返回"无匹配"。

方案2：旧版Excel（不支持动态数组）

使用INDEX+MATCH组合数组公式，输入后需按Ctrl+Shift+Enter确认：

=INDEX(服务费表!COL_5,MATCH(1,(服务费表!COL_1=A2)*(服务费表!COL_2<=B2)*(服务费表!COL_3>=B2),0))

• 无匹配时会返回#N/A，可嵌套IFERROR优化显示：

  =IFERROR(INDEX(服务费表!COL_5,MATCH(1,(服务费表!COL_1=A2)*(服务费表!COL_2<=B2)*(服务费表!COL_3>=B2),0)),"无匹配")
  

注意事项 #

• 确保查询表和服务费表的日期列均为日期格式，避免因文本格式导致匹配失败
• 若服务费表存在多条符合条件的记录，上述公式默认返回第一条的金额；如需返回最后一条，可调整XLOOKUP匹配模式或改用LOOKUP函数
• 建议将服务费表格转为Excel表（按Ctrl+T），公式会自动适配新增数据，无需手动修改区域引用

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