如何用Excel公式实现跨工作表按年份+姓名匹配查询对应代码？

问题描述 #

我有一个包含多工作表的Excel文件，主表支持用户输入年份与姓名，需创建公式返回该年份下对应姓名的专属代码。但数据结构并非普通二维表格：不同年份的姓名、代码无固定对应关系，Sheet2中每两列对应一个年份（左列姓名、右列代码），年份列之间隔一列空列，每行对应一位人员。示例表格如下：

我曾尝试结合使用=INDIRECT()与其他公式，但需要INDIRECT()返回单元格引用而非单元格内的值，特此寻求可行的公式方案。

解决方案 #

假设主表中：

• 年份输入单元格为 A1
• 姓名输入单元格为 B1

方案1：适用于Excel 365/2021（动态数组）

使用XLOOKUP结合OFFSET定位目标列：

=XLOOKUP(B1,OFFSET(Sheet2!$A$1,,MATCH(A1,Sheet2!$1:$1,0)-1,COUNTA(OFFSET(Sheet2!$A$1,,MATCH(A1,Sheet2!$1:$1,0)-1,100)),1),OFFSET(Sheet2!$A$1,,MATCH(A1,Sheet2!$1:$1,0),COUNTA(OFFSET(Sheet2!$A$1,,MATCH(A1,Sheet2!$1:$1,0)-1,100)),1),"未找到")

公式解析：

1. MATCH(A1,Sheet2!$1:$1,0)：定位目标年份在Sheet2表头的列位置
2. OFFSET(...,MATCH(...)-1,100,1)：提取该年份对应的姓名列（100为预估最大行数，可根据实际数据调整）
3. OFFSET(...,MATCH(...),100,1)：提取该年份对应的代码列
4. XLOOKUP：在姓名列匹配输入的姓名，返回对应代码，无匹配时显示"未找到"

方案2：兼容旧版Excel（无动态数组）

使用INDEX+MATCH组合实现：

=IFERROR(INDEX(Sheet2!$1:$100,MATCH(B1,OFFSET(Sheet2!$A$1,,MATCH(A1,Sheet2!$1:$1,0)-1,100,1),0),MATCH(A1,Sheet2!$1:$1,0)),"未找到")

公式解析：

1. 内层MATCH(A1,Sheet2!$1:$1,0)：找到目标年份的代码列位置
2. OFFSET(...,MATCH(...)-1,100,1)：提取目标年份的姓名列
3. 内层MATCH(B1,...)：找到目标姓名在姓名列中的行号
4. 外层INDEX：根据行号和列号定位代码单元格，IFERROR处理无匹配的情况

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