Excel使用INDEX MATCH无重复提取第1/2/3大/小分值对应条目问题咨询

问题核心 #

原公式使用MATCH匹配LARGE/SMALL返回的分值时，相同分值只会返回第一个匹配到的位置，导致条目重复。

解决方案 #

适用Excel 365/2021及以上版本 #

直接用SORTBY+INDEX一次性输出结果，公式更简洁：

• 获取前3高对应条目（输入第一个公式后自动溢出3个结果）：
  =INDEX(SORTBY(D6:D14,E6:E14,-1,ROW(D6:D14),1),SEQUENCE(3))
• 获取后3低对应条目（输入第一个公式后自动溢出3个结果）：
  =INDEX(SORTBY(D6:D14,E6:E14,1,ROW(D6:D14),1),SEQUENCE(3))
  排序逻辑说明：先按分值降序/升序排列，相同分值按原始行号升序排列，保证相同分值的条目按原表出现顺序输出无重复

适用所有Excel版本的兼容公式 #

需要用辅助判断逻辑跳过已经匹配过的条目，假设你要把前3高的结果放在F1、F2、F3单元格，后3低放在G1、G2、G3单元格：

前N高条目公式（按从上到下顺序输入到对应单元格）

• 第1高（F1单元格）：
  =INDEX(D6:D14,MATCH(LARGE(E6:E14,1),E6:E14,0))
• 第2高（F2单元格，数组公式，输入后按Ctrl+Shift+Enter回车确认）：
  =INDEX(D6:D14,MATCH(1,(E6:E14=LARGE(E6:E14,2))*(COUNTIF(F$1:F1,D6:D14)=0),0))
• 第3高（F3单元格，数组公式，输入后按Ctrl+Shift+Enter回车确认）：
  =INDEX(D6:D14,MATCH(1,(E6:E14=LARGE(E6:E14,3))*(COUNTIF(F$1:F2,D6:D14)=0),0))

后N低条目公式（按从上到下顺序输入到对应单元格）

• 第1低（G1单元格）：
  =INDEX(D6:D14,MATCH(SMALL(E6:E14,1),E6:E14,0))
• 第2低（G2单元格，数组公式，输入后按Ctrl+Shift+Enter回车确认）：
  =INDEX(D6:D14,MATCH(1,(E6:E14=SMALL(E6:E14,2))*(COUNTIF(G$1:G1,D6:D14)=0),0))
• 第3低（G3单元格，数组公式，输入后按Ctrl+Shift+Enter回车确认）：
  =INDEX(D6:D14,MATCH(1,(E6:E14=SMALL(E6:E14,3))*(COUNTIF(G$1:G2,D6:D14)=0),0))
  公式逻辑说明：用COUNTIF判断当前匹配到的条目是否已经出现在上方的结果列，已经出现的就跳过，直到匹配到第一个未出现的同分值条目，保证结果无重复，符合按原表顺序输出RED→GREEN→WHITE的要求

验证结果 #

用你提供的测试数据运行以上公式，输出结果完全符合预期：

• 前3高：RED、GREEN、WHITE
• 后3低：BLUE、GRAY、CYAN

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