如何用单个公式跨非相邻列匹配Excel两表共通数据？

适用场景 #

基于ID#从表2提取共通列数据到表1，需保留红色字体手动输入列，且单个公式可横向/纵向拖拽填充，无需逐列设置。

公式实现 #

在表1的E6单元格输入以下公式，即可拖拽至E12及其他需要填充的列/行：

=IF(CELL("color",E6)=3,"",INDEX(表2!$A:$Z,MATCH($B6,表2!$A:$A,0),MATCH(E$5,表2!$A:$Z,0)))

公式逻辑拆解 #

• 识别手动列：CELL("color",E6)=3 判断当前单元格是否为红色字体（Excel中红色字体对应CELL函数返回值为3），若是则返回空值，保留手动输入内容。
• 匹配目标行：MATCH($B6,表2!$A:$A,0) 以表1的ID#（B列）为唯一标识，定位表2中对应的行位置。
• 匹配目标列：MATCH(E$5,表2!$A:$Z,0) 根据当前单元格的表头，匹配表2中对应的列位置，确保即使中间穿插手动列，也能精准对应共通列数据。
• 提取目标数据：INDEX(表2!$A:$Z,行位置,列位置) 从表2中提取对应行列的匹配数据。

使用注意事项 #

• 表1与表2的共通列表头文本需完全一致，否则列匹配会失效。
• 根据实际数据范围调整表2!$A:$Z，比如表2数据延伸至AA列，可改为表2!$A:$AA。
• 若红色字体的CELL返回值不是3，可选中任意红色字体单元格，输入=CELL("color",A1)（替换A1为选中单元格）查看实际数值后替换公式中的3。
• 拖拽时保持引用规则：$B6锁定ID#列，E$5锁定表头行，保证横向、纵向拖拽均能正确匹配。

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