如何用Excel公式在指定范围内返回重复匹配项的第N个相邻值？

针对你有≥5000行数据、存在重复匹配值且无法删除无用列的需求，以下几种方法可以精准提取第N个匹配项的对应值：

方法1：传统数组公式（兼容旧版Excel） #

适合没有动态数组功能的Excel版本，先筛选出所有匹配值的行号，再提取第N个行号对应的目标值：

=INDEX(I2:I5001,SMALL(IF(K2:K5001=O5,ROW(K2:K5001)-ROW(K2)+1),N))

• 替换N为你要提取的匹配项序号（比如要第二个620就填2）
• 旧版Excel需按Ctrl+Shift+Enter触发数组计算，新版Excel直接回车即可
• 必须指定精确数据范围（如I2:I5001），不要用整列引用（I:I），否则5000行数据会大幅拖慢计算速度

方法2：Excel 365/2021 动态数组函数（高效简洁） #

利用FILTER先筛选出所有匹配的目标值，再用INDEX提取第N个结果，无需数组输入，效率更高：

=INDEX(FILTER(I2:I5001,K2:K5001=O5),N)

• FILTER会自动返回所有匹配O5值的I列数据，INDEX直接定位到第N个值
• 动态数组函数处理大数据量的性能远优于传统数组公式，推荐优先使用

方法3：辅助列批量提取（适合频繁查询场景） #

如果需要多次提取不同序号的匹配项，可添加辅助列减少重复计算：

1. 在空白列（比如L列）输入公式，给每个匹配项标记序号：

   =COUNTIF($K$2:K2,K2)
   

   下拉填充后，第一个620会标1，第二个标2，以此类推
2. 用INDEX+MATCH组合提取指定序号的对应值：

   =INDEX(I2:I5001,MATCH(1,(K2:K5001=O5)*(L2:L5001=N),0))
   

• 辅助列仅需计算一次，后续查询直接匹配，是三种方法中速度最快的，适合高频查询场景

关键优化提示 #

• 所有公式都必须限定精确数据范围，避免整列引用，这对5000行数据的计算效率影响极大
• 若使用Excel 365，优先选择方法2，代码简洁且性能最优

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