Excel查找第3个匹配值的公式无法正常运行问题求助

第n个匹配值查找方案 #

问题诊断 #

1. 公式2替换LARGE为SMALL后与INDEX/MATCH结果一致：你公式中COLUMNS($C$2:$C$1165)的计算结果为1，SMALL取第1个最小行号对应就是第一个匹配结果，和普通INDEX/MATCH逻辑完全一致。如果需要返回第2、3个匹配值，只需将该参数修改为对应的数字即可。
2. 公式3运行异常：该公式用MAX函数取所有匹配行的最大行号，本质是只会返回最后一个匹配结果，同时行号计算逻辑存在偏移，不符合第n个匹配值的查找需求。

可直接复用的公式 #

适配Excel全版本（2019及更早版本需按Ctrl+Shift+Enter确认数组公式）

把公式末尾的n替换为你要查找的第几个匹配值的序号即可：

=IFERROR(INDEX(Consolidated!$A$2:$A$2661,SMALL(IF(Consolidated!$B$2:$B$2661=$A7,ROW(Consolidated!$B$2:$B$2661)-ROW(Consolidated!$B$2)+1,""),n)),"")

比如查找第3个匹配值，就把最后的n改成3。

适配Excel 365/2021（无需数组确认，写法更简单）

=IFERROR(INDEX(FILTER(Consolidated!$A$2:$A$2661,Consolidated!$B$2:$B$2661=$A7,""),n),"")

如果需要一次性返回所有匹配值，直接用：

=FILTER(Consolidated!$A$2:$A$2661,Consolidated!$B$2:$B$2661=$A7,"无匹配结果")

扩展提示 #

如果需要横向拖拽公式自动返回第1、2、3...个匹配值，可以把最后的n换成COLUMNS($A$1:A$1)，向右拖拽时参数会自动递增为1、2、3...，无需手动修改。

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