Excel Index+Match返回重复值问题求解（多站点同油价场景）

问题背景 #

公司使用Excel查找目标加油区域内的最低价燃油，现有公式可正常计算价格，但存在一个问题：当多个站点燃油价格相同时，站点名称列会重复返回同一个站点（例如Courcelle、lac Megantic、St-Gedeon三个站点价格均为1,4505时，I、K、M列三次返回Courcelle）。

当前使用的公式 #

价格计算（H、J、L列）

• H3：=Small(C3:G3;Countif(C3:G3;0)+1)
• J3：=Small(C3:G3;Countif(C3:G3;0)+2)
• L3：=Small(C3:G3;Countif(C3:G3;0)+3)

站点名称匹配（I、K、M列）

• I3：=index($C$1:$G$1;Match(H3;C3:G3;0))
• K3：=index($C$1:$G$1;Match(J3;C3:G3;0))
• M3：=index($C$1:$G$1;Match(L3;C3:G3;0))

需求目标（二选一即可） #

• 需求1：返回最低价站点，若该名称已在I/K/M列的前序单元格中出现，则跳过并查找下一个符合条件的站点；
• 需求2：先返回所有最低价对应的站点，再依次查找下一档价格的站点。

方案1：实现需求1（跳过已出现的站点） #

替换I、K、M列的原有公式，通过数组逻辑过滤已返回的站点：

• I3（第一个站点）：
  保留原公式即可：=INDEX($C$1:$G$1, MATCH(H3, C3:G3, 0))
• K3（第二个站点）：

  =INDEX($C$1:$G$1, MATCH(1, (C3:G3=J3)*($C$1:$G$1<>I3), 0))
  

• M3（第三个站点）：

  =INDEX($C$1:$G$1, MATCH(1, (C3:G3=L3)*($C$1:$G$1<>I3)*($C$1:$G$1<>K3), 0))
  

注意：Excel 365/2021版本直接回车即可；旧版本需按Ctrl+Shift+Enter作为数组公式执行。

方案2：实现需求2（先返回全部同价站点） #

先调整价格列公式，确保同价时返回相同价格，再用站点公式依次匹配：

调整价格列公式（H、J、L列）

• H3：=SMALL(C3:G3, COUNTIF(C3:G3, 0)+1)
• J3：

  =IF(COUNTIF(C3:G3, H3)>=2, H3, SMALL(C3:G3, COUNTIF(C3:G3, 0)+2))
  

• L3：

  =IF(COUNTIF(C3:G3, H3)>=3, H3, IF(COUNTIF(C3:G3, H3)=1, SMALL(C3:G3, COUNTIF(C3:G3, 0)+2), SMALL(C3:G3, COUNTIF(C3:G3, 0)+3)))
  

站点名称匹配公式（I、K、M列）

与方案1的站点公式完全一致：

• I3：=INDEX($C$1:$G$1, MATCH(H3, C3:G3, 0))
• K3：=INDEX($C$1:$G$1, MATCH(1, (C3:G3=J3)*($C$1:$G$1<>I3), 0))
• M3：=INDEX($C$1:$G$1, MATCH(1, (C3:G3=L3)*($C$1:$G$1<>I3)*($C$1:$G$1<>K3), 0))

注意：数组公式执行方式同方案1。

内容的提问来源于stack exchange，提问作者Marc-Andre pouliot