使用MATCH函数精准定位行位置 解决重复值匹配结果错误问题

问题说明 #

• 需求：匹配B列各单元格数值在A列对应的行位置
• 问题原因：A列存在重复值，原公式=MATCH(B1,$A$1:$A$19,0)默认仅返回查找区域内第一个匹配值的位置，因此重复匹配时会连续返回相同的第一个匹配行号（如连续三次返回行号2），不符合预期
• 相关参考截图：
  
  

修正公式 #

全Excel版本通用写法 #

在C1单元格输入以下公式，旧版Excel（2019及更早版本）输入完成后按Ctrl+Shift+Enter确认数组公式，365/2021及以上版本直接回车即可，之后下拉填充整列：

=SMALL(IF($A$1:$A$19=B1,ROW($A$1:$A$19),9^9),COUNTIF($B$1:B1,B1))

公式逻辑说明 #

• IF($A$1:$A$19=B1,ROW($A$1:$A$19),9^9)：遍历A列1-19行，值与当前B列单元格相等时返回对应行号，不相等则返回一个远大于最大行号的极值9^9
• COUNTIF($B$1:B1,B1)：统计从B列起始行到当前行，当前查找值累计出现的次数，第一次出现返回1、第二次返回2，以此类推
• SMALL(匹配到的行号集合, 出现次数)：从所有匹配到的行号中提取第N次出现对应的行号，和D列预期结果完全一致

注意：公式中$B$1:B1的混合引用不要写错，下拉时会自动扩展范围，才能正确统计当前值的累计出现次数。

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