Excel中获取重复第二大ID对应值的技术咨询

原始表格（$A$1:$B$5） #

问题描述 #

使用公式获取最大ID对应Value时正常返回B，但将LARGE参数改为2获取第二大ID对应Value时，MATCH/VLOOKUP仅返回第一个匹配的B，预期结果为D。

解决方案 #

方法1：INDEX+SMALL+IF组合（兼容多数Excel版本） #

=IFERROR(INDEX($B$2:$B$5, SMALL(IF($A$2:$A$5=LARGE($A$2:$A$5,2), ROW($A$2:$A$5)-ROW($A$2)+1), 2)), "-")

• 逻辑：先用IF筛选出所有ID等于第二大值的行位置，再用SMALL取第2个符合条件的位置，最后用INDEX返回对应Value。
• 注意：旧版Excel需按Ctrl+Shift+Enter触发数组计算，新版Excel自动支持。

方法2：XLOOKUP反向查找（适用于支持XLOOKUP的Excel版本） #

=IFERROR(XLOOKUP(LARGE($A$2:$A$5,2), $A$2:$A$5, $B$2:$B$5, "-", 0, -1), "-")

• 逻辑：XLOOKUP最后一个参数-1表示从后往前查找，直接定位到最后一个匹配17的记录，对应Value D。

方法3：AGGREGATE函数实现 #

=IFERROR(INDEX($B$2:$B$5, AGGREGATE(15, 6, (ROW($A$2:$A$5)-ROW($A$2)+1)/($A$2:$A$5=LARGE($A$2:$A$5,2)), 2)), "-")

• 逻辑：AGGREGATE的15对应SMALL功能，6忽略错误值，通过除法过滤出符合条件的行号，再取第2个位置。

问题根源 #

原公式中的MATCH和VLOOKUP默认从前往后查找，遇到第一个匹配的ID（17）就返回对应Value，无法区分重复ID的不同记录，因此需要通过数组筛选或反向查找定位到目标记录。

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