使用嵌套INDEX/MATCH公式动态提取Excel表格匹配数据

场景1：纵向源表（月份为纵向合并单元格） #

假设源表命名为源数据，结构如下：

• A列：PID
• B:C列：合并的Month（仅每个合并区域的首个B单元格有月份值，其余行B/C为空）
• D列：Metric
• E列：Value

方法1：辅助列简化逻辑 #

1. 在源表新增辅助列（如F列），输入公式自动获取每行对应的月份值（适配合并单元格空值）：

   =LOOKUP("座",$B$2:B2)
   

   下拉填充至所有行，确保每行都能读取到所属的月份值。

2. 目标表Result列（以D2单元格为例）输入INDEX/MATCH数组公式：

   =INDEX(源数据!E:E,MATCH(1,(源数据!F:F=A2)*(源数据!A:A=B2)*(源数据!D:D=C2),0))
   

   • Excel 365/2021版本直接回车生效；旧版本需按Ctrl+Shift+Enter触发数组计算。
   • 下拉公式即可自动适配每行的Month、PID、Metric匹配条件。

方法2：无辅助列直接整合逻辑 #

无需新增辅助列，将月份提取逻辑嵌入MATCH函数：

=INDEX(源数据!E:E,MATCH(1,(LOOKUP("座",OFFSET(源数据!B$1,ROW(源数据!B:B)-1,0,1,1))=A2)*(源数据!A:A=B2)*(源数据!D:D=C2),0))

• OFFSET 函数为每行生成从B1到当前行B列的范围，LOOKUP 从中提取最近的非空月份值，实现动态适配。

场景2：横向源表（月份为横向合并标题列） #

假设源表第一行是合并的月份标题（如B1:C1合并为"2024-01"，D1:E1合并为"2024-02"），结构如下：

• A列：PID
• B列：Metric
• C列：2024-01的Value（对应B1:C1合并月份）
• E列：2024-02的Value（对应D1:E1合并月份）

目标表Result列公式：

=INDEX(源数据!$A:$XFD,MATCH(1,(源数据!$A:$A=B2)*(源数据!$B:$B=C2),0),MATCH(A2,源数据!$1:$1,0)+1)

• MATCH(A2,源数据!$1:$1,0) 定位目标月份合并列的起始列号，+1 指向对应的Value列。
• 下拉公式即可自动适配不同月份、PID和Metric的组合。

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