如何根据指定用户单元格的值返回对应行的最后一个数据？

场景说明 #

我有如下表格结构（简化后）：

需求是：当B1（User单元格）的值改变时，D1（Latest单元格）自动填充为对应用户行的最后一个非空数据。例如User设为Colin时，Latest应显示7。

我已经能用公式=INDEX(B4:B7,MATCH(B1,A4:A7,0))提取单列值，但在处理数组、获取对应行最后一个数据时遇到困难。尝试过在INDEX/MATCH中嵌套LOOKUP、用数组公式返回第n个值，以及=MAX(ROW(A3:A7)*(--(B1=A3:A7)))均无效，希望得到解决方向指引。

解决方案 #

这里提供几种适配不同Excel版本的实用方法，你可以根据自己的软件版本选择：

方法1：INDEX + MATCH + LOOKUP（兼容多数Excel版本）

核心思路是先定位目标用户的行，再在该行内抓取最后一个非空值：

=LOOKUP(2,1/(INDEX(B4:E7,MATCH(B1,A4:A7,0),0)<>""),INDEX(B4:E7,MATCH(B1,A4:A7,0),0))

简单解释：

• INDEX(B4:E7,MATCH(B1,A4:A7,0),0)：返回目标用户对应的整行数据数组
• 1/(...<>"")：把该行的非空值转成1，空值转成错误值
• LOOKUP(2,1/..., ...)：利用LOOKUP自动忽略错误值的特性，找到最后一个有效数据

方法2：XLOOKUP + LOOKUP（Excel 365/2021及以上）

如果你的Excel支持XLOOKUP，可以简化写法，逻辑和方法1一致：

=LOOKUP(2,1/(XLOOKUP(B1,A4:A7,B4:E7)<>""),XLOOKUP(B1,A4:A7,B4:E7))

XLOOKUP会直接返回目标用户的整行数据，省去了INDEX嵌套的步骤。

方法3：动态数组 + TAKE（Excel 365专属）

借助动态数组函数的特性，一步到位提取最后一个非空值：

=TAKE(FILTER(B4:E7,A4:A7=B1),,COUNTA(FILTER(B4:E7,A4:A7=B1)))

解释：

• FILTER(B4:E7,A4:A7=B1)：筛选出目标用户的行数据
• COUNTA(...)：统计该行非空单元格的数量
• TAKE(..., , 数量)：从该行右侧提取对应数量的最后一个值

为什么你之前的尝试没生效？ #

• =MAX(ROW(A3:A7)*(--(B1=A3:A7)))：这个公式只能得到目标用户的行号，但没有关联到该行的列数据，需要结合INDEX调用该行数据后再做处理。
• 嵌套LOOKUP时如果没正确返回整行数组，就无法定位到最后一个值——关键是要先锁定目标行，再在该行内查找有效数据的末尾。

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