Excel中用INDEX函数多条件匹配投入-影响矩阵项目名称

适配场景 #

• 3*3规格项目优先级矩阵：Y轴为Effort（投入度）、X轴为Impact（影响度），两轴均设Low/Medium/High三个档位，共9种组合
• 数据源规则：任务表C8:C47区域存储项目名称，D8:D47为Effort下拉选值列，E8:E47为Impact下拉选值列
• 需求：矩阵对应单元格自动提取匹配档位组合的所有项目名称

原有公式问题 #

你当前使用的公式硬编码了档位判断条件$D$8:$D$47="High"、$E$8:$E$47="Low"，仅能匹配Effort=High、Impact=Low的单一组合，既无法识别Medium档位，也不能通过拖拽复用到其他矩阵单元格。

通用可复用公式 #

版本提示：Excel 2019及更早版本输入公式后需按Ctrl+Shift+Enter确认数组公式；Microsoft 365/Excel 2021及以上版本直接回车即可生效。

使用前先确认矩阵布局：

• 矩阵每行的行首单元格标注该行对应的Effort档位（High/Medium/Low）
• 矩阵每列的列首单元格标注该列对应的Impact档位（Low/Medium/High）

以矩阵首个匹配单元格（Effort=High、Impact=Low对应的单元格，假设位置为B8，该行行首Effort档位单元格为$A8，该列列首Impact档位单元格为B$7）为例，写入以下公式：

=IFERROR(INDEX($C$8:$C$47,SMALL(IF((LEN($C$8:$C$47)<>0)*($D$8:$D$47=$A8)*($E$8:$E$47=B$7),ROW($C$8:$C$47)-ROW($C$7),""),ROW(A1))),"")

使用方法 #

• 公式写入首个单元格确认生效后，直接向右拖拽覆盖同Effort行的所有Impact列，再向下拖拽覆盖全矩阵9个单元格即可，无需手动修改每个单元格的判断条件
• 若单个档位组合下存在多个匹配项目，将对应单元格向下拖拽填充若干行，即可按录入顺序返回所有符合条件的项目名称
• 无匹配项目时单元格自动显示为空，不会抛出错误值

公式逻辑说明 #

• 第一层判断用乘法逻辑同时校验三个条件：项目名称非空、Effort匹配当前行档位、Impact匹配当前列档位
• SMALL函数按从小到大的顺序提取所有符合条件的行的相对位置
• INDEX函数根据提取的位置从项目名称列返回对应值
• IFERROR函数捕获无匹配项时的错误，返回空值

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