如何在Excel中跨工作表按姓名动态引用对应行数据？

问题背景 #

你制作工资表时，Payroll Input（表一）存储员工姓名、Earning 1、Hours、Rate、Amount数据，Payroll Summary（表二）需从表一提取数据。原公式里MATCH('TAB_TWO'!B$1, TAB_ONE!2:2, 0)固定了表一的行号，员工顺序变动就会引发引用错误，需要改为按表二的员工姓名动态定位表一对应行，再匹配表头提取数据。

解决方案 #

以下两种公式可实现需求，根据你的Excel版本选择：

1. 通用版（INDEX+双层MATCH，兼容所有Excel版本）

在表二的B2单元格输入以下公式，随后下拉、右拉填充：

=INDEX('Payroll Input'!$A:$E, MATCH($A2, 'Payroll Input'!$A:$A, 0), MATCH(B$1, 'Payroll Input'!$1:$1, 0))

• 第一个MATCH：依据表二A2的员工姓名，找到表一中该员工的所在行号
• 第二个MATCH：依据表二B1的表头，找到表一中对应列的列号
• INDEX根据行号和列号，精准返回表一中的目标数据
• 引用规则：$A2锁定姓名列，下拉时保持匹配当前员工；B$1锁定表头行，右拉时保持匹配当前数据项；'Payroll Input'!$A:$E锁定表一的整个数据区域，避免因数据新增出现范围不足问题

2. 简化版（XLOOKUP，仅支持Excel 365/2021及以上版本）

若你的Excel版本支持XLOOKUP，可使用更简洁的公式：

=XLOOKUP(B$1, 'Payroll Input'!$1:$1, XLOOKUP($A2, 'Payroll Input'!$A:$A, 'Payroll Input'!$A:$E))

• 内层XLOOKUP：根据员工姓名提取表一中该员工的整行数据
• 外层XLOOKUP：根据表头从提取到的整行数据中，筛选出对应的数据项

使用说明 #

输入公式后，向下拖拽填充所有员工行，向右拖拽填充所有数据列即可。无论表一中的员工顺序如何变动，公式都会自动匹配姓名和表头，返回正确的数据。

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