如何使用INDEX/MATCH在Excel分类汇总区域按员工和企业查询总工时

问题根因 #

你原有公式无法运行的核心原因有2个：

1. 分类汇总行的员工列（Sheet1的A列）本身为空，直接匹配Sheet2的员工姓名无法命中对应行
2. 公式引用错误：按你给出的Sheet2示例，员工字段在A列、客户字段在B列，原公式误用Sheet2!B1作为员工匹配值，同时工作表名的单引号引用格式错误，正确写法为'Sheet1'!而非'Sheet1!

适配方案 #

方案1：适用于Excel 365/2021及以上版本（支持动态数组） #

直接使用SCAN函数隐性补全分类汇总行的员工字段，再做双条件匹配：

=INDEX('Sheet1'!C:C,XLOOKUP(1,('Sheet1'!B:B=Sheet2!B2&" Total")*(SCAN("",'Sheet1'!A:A,LAMBDA(a,v,IF(v<>"",v,a)))=Sheet2!A2),ROW('Sheet1'!C:C),,0))

公式逻辑：SCAN函数遍历Sheet1的A列，将空值自动填充为上方最近的非空员工名，相当于给每一行分类汇总行补全了所属员工，再同时匹配客户汇总名和员工两个条件即可。

方案2：适用于2019及更早的旧版Excel #

使用LOOKUP实现空员工列的填充，输入完成后需要按Ctrl+Shift+Enter触发数组计算：

=INDEX('Sheet1'!C1:C45,MATCH(1,('Sheet1'!B1:B45=Sheet2!B2&" Total")*(LOOKUP(ROW('Sheet1'!A1:A45),IF('Sheet1'!A1:A45<>"",ROW('Sheet1'!A1:A45)),'Sheet1'!A1:A45)=Sheet2!A2),0))

公式逻辑：LOOKUP会匹配当前行号之前最近的A列非空行的员工名，作为当前行的所属员工，再做双条件匹配。

注意事项 #

• 确保Sheet1的分类汇总生成规则是先按员工分组、再按客户分组，才能保证每个客户汇总行对应正确的所属员工
• 可根据你的Sheet1实际数据行数，调整公式中的行范围避免漏匹配
• 如果你的Sheet2员工、客户字段的列位置和示例不同，需要对应调整Sheet2!A2、Sheet2!B2的引用位置

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