Excel如何按条件提取表格倒数第二、倒数第三个匹配值

原公式逻辑说明 #

原用于提取最后一个（倒数第1个）匹配值的公式如下：

=LOOKUP(2,1/(E9:E2612=D19),F9:F2612)

核心运行逻辑：

• 表达式1/(E9:E2612=D19)会将不满足「E列单元格值等于D19」条件的位置转换为#DIV/0!错误值，满足条件的位置转换为数值1
• LOOKUP函数查找大于数组内所有有效值（最大值为1）的数值2时，会自动忽略错误值，直接定位到数组中最后一个有效数值的位置，返回对应F列的取值

各顺位匹配值提取公式 #

以下公式完全沿用原LOOKUP函数的兼容逻辑，支持所有Excel版本，输入后直接回车即可生效，无需数组三键。

• 倒数第2个匹配值

=LOOKUP(2,1/((E9:E2612=D19)*(COUNTIF(OFFSET(E9,ROW(E9:E2612)-ROW(E9)+1,0,ROWS(E9:E2612)-(ROW(E9:E2612)-ROW(E9)),1),D19)=1)),F9:F2612)

• 倒数第3个匹配值

=LOOKUP(2,1/((E9:E2612=D19)*(COUNTIF(OFFSET(E9,ROW(E9:E2612)-ROW(E9)+1,0,ROWS(E9:E2612)-(ROW(E9:E2612)-ROW(E9)),1),D19)=2)),F9:F2612)

公式逻辑说明：
逐行判断两个条件是否同时成立：

1. 当前行E列值等于D19，属于匹配项
2. 当前行下一行到数据末尾的区域内，恰好存在1个（倒数第2个时）/2个（倒数第3个时）同条件匹配值
   两个条件同时成立的位置就是目标匹配项所在位置，通过1/条件将非目标位置转为错误值后，即可用LOOKUP直接定位取值。

动态数组版本简化写法（仅支持Excel 365/2021及以上版本） #

如果使用的是支持FILTER动态数组函数的高版本Excel，可以用更简短的公式实现：

• 倒数第2个匹配值：=INDEX(FILTER(F9:F2612,E9:E2612=D19),COUNTIF(E9:E2612,D19)-1)
• 倒数第3个匹配值：=INDEX(FILTER(F9:F2612,E9:E2612=D19),COUNTIF(E9:E2612,D19)-2)

通用规律：提取倒数第k个匹配值时，只需要把公式末尾的减数改为k-1即可。

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