使用Index Match与通配符的Excel条件格式配置问题咨询

正确的条件格式公式 #

针对表1的整行高亮需求，你可以使用以下公式作为条件格式规则（假设表1数据从第2行开始，表头在第1行）：

=$B2>SUMPRODUCT((COUNTIF($A2,"*"&'Table 2'!$B:$B&"*")>0)*('Table 2'!$D:$D=$C2)*'Table 2'!$C:$C)

公式说明 #

• COUNTIF($A2,"*"&'Table 2'!$B:$B&"*")>0：判断表1当前行的文件名是否包含表2中对应的用户编号，适配用户编号在文件名任意位置的情况
• 'Table 2'!$D:$D=$C2：匹配表1的文件存储路径与表2的规则路径
• SUMPRODUCT(...)：提取同时满足上述两个匹配条件的最高阈值金额，因唯一匹配一条规则，结果即为对应阈值
• $B2>...：判断当前行支付金额是否超出对应阈值

条件格式设置步骤 #

1. 选中表1中需要应用格式的所有数据行（例如A2:CXX，XX为最后一行数据的行号）
2. 打开「条件格式」→「新建规则」→「使用公式确定要设置格式的单元格」
3. 粘贴上述公式，设置高亮格式（比如填充红色）
4. 确认应用规则

针对固定格式文件名的优化版本 #

如果你的文件名格式固定为前缀_用户编号_后缀，可以用更精准的提取方式提升效率，公式调整为：

=$B2>SUMPRODUCT(('Table 2'!$B:$B=--MID($A2,FIND("_",$A2)+1,6))*('Table 2'!$D:$D=$C2)*'Table 2'!$C:$C)

其中--MID($A2,FIND("_",$A2)+1,6)会从第一个下划线后提取6位用户编号，并转换为数值匹配表2的用户编号列。

原公式失效原因 #

你的原公式存在两个核心问题：

1. 使用了MATCH($C2,'Table 2'!$D:$D,1)的近似匹配模式（第三个参数为1），导致路径匹配结果不准确
2. 用户编号匹配逻辑错误，固定指向'Table 2'!$D$1（表2第一行的路径），而非动态匹配对应的用户编号，因此只会用表2第一行的阈值判断所有行

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