INDEX/MATCH匹配前缀相同文本的多值列表失效问题排查

问题原因拆解 #

1. #REF!错误：你的MATCH引用范围是'GR-AP'!$D$1:$D$20000，覆盖了大量行，但INDEX仅限定到行4。当MATCH找到的匹配行号大于4时，INDEX无法定位对应行，直接抛出#REF!错误。
2. 返回0：把INDEX范围扩展到行5后，若行5的B列是空单元格，Excel会将空值默认显示为0，这是Excel对空单元格的数值化处理逻辑。
3. 匹配错误返回GR-TEST：核心原因是匹配列存在重复值，MATCH默认返回第一个匹配项的行号。如果新增的GR-TEST所在行的D列值，和你目标的GR-IT-CREDIT的D列值完全一致，MATCH会优先找到GR-TEST的行，导致返回错误的B列值。另外，MATCH范围包含表头行（D1），如果G6的值和表头文本巧合匹配，也会出现错误。

修正后的公式写法 #

基础精确匹配公式（避免错误） #

直接引用整列并添加错误处理，确保覆盖所有数据行，同时避免错误显示：

=IFERROR(INDEX('GR-AP'!$B:$B, MATCH(G6, 'GR-AP'!$D:$D, 0)), "")

如果需要限定数据范围（排除表头，提升效率）：

=IFERROR(INDEX('GR-AP'!$B$2:$B$20000, MATCH(G6, 'GR-AP'!$D$2:$D$20000, 0)), "")

针对重复值的优化（取最后一个匹配项） #

如果你的D列存在重复的匹配值，需要返回最后一个对应项的B列值，可使用LOOKUP：

=IFERROR(LOOKUP(2,1/('GR-AP'!$D:$D=G6),'GR-AP'!$B:$B), "")

使用Excel 365/2021的话，XLOOKUP更直观，支持从后往前匹配：

=XLOOKUP(G6, 'GR-AP'!$D:$D, 'GR-AP'!$B:$B, "", 0, 2)

额外优化建议 #

• 转为结构化表格：选中第一个工作表的数据区域，按Ctrl+T转为Excel表格，公式会自动适配数据范围的增减，无需手动调整行号：

  =IFERROR(INDEX(Table1[Application Group], MATCH(G6, Table1[Configuration Items], 0)), "")
  

• 统一文本格式：检查D列和G列的单元格格式，确保都是文本格式，避免因格式差异（比如一个是文本型、一个是数值型）导致匹配失败。
• 排查重复值：用COUNTIF('GR-AP'!$D:$D, G6)统计匹配值的出现次数，若结果大于1，需明确匹配规则（取第一个/最后一个/特定条件的项）。

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