使用公式查找最后已占用单元格 求简化方案

我来帮你把这个公式简化到位！先明确你的核心需求：从另一个工作簿的独立工作表中，根据当前表$A$20的目标日期，匹配第1列的日期并返回第5列的数值；如果目标日期早于所有数据日期，或是晚到还没有对应数据，就返回最新的可用数据。

下面分两种场景给你最简洁的替代公式：

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

XLOOKUP本身自带匹配逻辑和兜底参数，能一步搞定需求，公式非常紧凑：

=XLOOKUP($A$20,'[外部工作簿.xlsx]目标工作表'!$A:$A,'[外部工作簿.xlsx]目标工作表'!$E:$E,LOOKUP(9.99999999999999E+307,'[外部工作簿.xlsx]目标工作表'!$E:$E),-1)

参数解释： #

• $A$20：你的目标日期（当前工作表的调用日期）
• 前两个区域：分别是外部表的日期列（第1列）和要返回的数值列（第5列）
• 第四个参数：当找不到匹配时返回的值，这里用LOOKUP(9.99999999999999E+307,...)取第5列最后一个非空值（也就是最新可用数据）
• 第五个参数-1：指定匹配「小于等于目标日期的最大日期」，完美覆盖“目标日期晚于现有数据时取最新”的场景；如果目标日期早于所有数据，XLOOKUP会触发第四个参数返回最新值。

方案2：兼容旧版Excel（无XLOOKUP时用） #

如果你的Excel版本不支持XLOOKUP，用INDEX+MATCH+IFERROR的简化组合也能实现：

=IFERROR(INDEX('[外部工作簿.xlsx]目标工作表'!$E:$E,MATCH($A$20,'[外部工作簿.xlsx]目标工作表'!$A:$A,1)),LOOKUP(9.99999999999999E+307,'[外部工作簿.xlsx]目标工作表'!$E:$E))

逻辑说明： #

• MATCH(...,1)：在升序的日期列中找小于等于目标日期的最大匹配行号，INDEX取对应第5列的值
• IFERROR兜底：如果目标日期早于所有数据（MATCH返回#N/A），就用LOOKUP取第5列最后一个非空值

额外优化小技巧： #

如果觉得外部工作簿的引用太长，可以给外部表的日期列和数值列定义名称（比如外部日期、外部数值），这样公式会更简洁：

=XLOOKUP($A$20,外部日期,外部数值,LOOKUP(9.99999999999999E+307,外部数值),-1)

注意：确保外部工作簿的日期列是升序排列的，这两个方案的匹配逻辑都依赖升序哦！

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