Excel/Google Sheets多列查找返回行表头的技术求助

• 表1包含两列：A列为静态数据，B列为带下拉列表的数据，每行A、B数据一一配对。
• 表2为矩阵结构：A列数据与表1完全一致；B列及后续列对应表1B列的下拉选项；第1行表头为Level 1至Level 9，且不同类别的表2位于独立标签页（如Sales、Consultancy）。
• 需求：在Excel/Google Sheets中编写公式，根据表1每行的A、B配对数据，在对应标签页的表2中找到B值所在的列，返回该列第1行的Level表头。

用户尝试过以下公式但未达到预期效果：

=INDEX(Sales!$A$4:$J$4,1,SUMPRODUCT(($EM11&EN11=Sales!$A$4:$A$12&Sales!$A$4:$J$12)*COLUMN(Sales!$A$4:$J$4)))

=INDEX(Consultancy!$A$13:$J$29,MATCH(EN8&$EM8,(Consultancy!$A$13:$A$29=$EM8)*(SUMPRODUCT(--(Consultancy!$B$13:$J$29=EN8),COLUMN(Consultancy!$A$13:$J$29))-COLUMN(Consultancy!$A$13:$A$29)+1),0))

Excel 公式 #

新版Excel（支持动态数组） #

假设表1当前行的A值在EM11，B值在EN11，对应表2所在标签页为Sales，表2的A列范围是Sales!$A$4:$A$12，数据区域是Sales!$B$4:$J$12，表头行是Sales!$B$4:$J$4（对应Level 1-9），使用以下公式：

=XLOOKUP(EN11,OFFSET(Sales!$B$4:$J$12,MATCH(EM11,Sales!$A$4:$A$12,0)-1,0),Sales!$B$4:$J$4,"未找到")

公式说明：

1. MATCH(EM11,Sales!$A$4:$A$12,0)：定位表1A值在表2A列的行号
2. OFFSET(...)：提取表2中对应行的B-J列数据区域
3. XLOOKUP：在该行区域中匹配表1B值，返回对应Level表头

旧版Excel（无XLOOKUP） #

用INDEX+MATCH组合实现：

=INDEX(Sales!$B$4:$J$4,MATCH(EN11,INDEX(Sales!$B$4:$J$12,MATCH(EM11,Sales!$A$4:$A$12,0),0),0))

Google Sheets 公式 #

直接使用INDEX+MATCH组合：

=INDEX(Sales!$B$4:$J$4,MATCH(EN11,INDEX(Sales!$B$4:$J$12,MATCH(EM11,Sales!$A$4:$A$12,0),0),0))

如果需要动态切换标签页（标签页名称存于EP11单元格），可改用INDIRECT实现：

=INDEX(INDIRECT(EP11&"!$B$4:$J$4"),MATCH(EN11,INDEX(INDIRECT(EP11&"!$B$4:$J$12"),MATCH(EM11,INDIRECT(EP11&"!$A$4:$A$12"),0),0),0))

注意事项 #

• 确保表2A列与表1A列数据完全匹配，无空格、格式差异等问题
• 若表2表头从A列开始（Level 1在A列），只需调整公式中的列范围（如将$B$4:$J$4改为$A$4:$J$4）

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