如何在Excel中提取匹配另一表格双值组合的对应数据行

前提约定 #

• 总表放在工作表Sheet1，表头占第1行，数据从第2行开始，其中物种字段在第6列（F列）、酶名称字段在第9列（I列）
• 参考表放在工作表Sheet2，表头占第1行（A列表头填「物种」、B列表头填「酶名称」，和总表对应字段的表头完全一致），参考数据从第2行开始

方法1：高级筛选（最适合批量提取匹配整行） #

操作步骤：

1. 打开Sheet1，点击顶部菜单栏「数据」选项卡，找到「排序和筛选」组里的「高级」按钮
2. 在弹出的高级筛选对话框里：
   • 「方式」选择「将筛选结果复制到其他位置」
   • 「列表区域」选择Sheet1的整个数据区域（包含表头，比如Sheet1!$A$1:$K$10000，可根据实际数据行数调整）
   • 「条件区域」选择Sheet2的整个参考数据区域（必须包含和总表一致的表头，比如Sheet2!$A$1:$B$1000，根据实际参考数据行数调整）
   • 「复制到」选择Sheet1或者新工作表里的空白区域左上角单元格（比如新工作表Sheet3的$A$1）
3. 点击确定，系统会自动把所有同时匹配参考表物种+酶名组合的整行数据提取到你指定的位置

方法2：公式法（适合需要动态更新匹配结果的场景） #

Excel 2021/365版本 #

在新工作表的A1单元格输入公式：

=FILTER(Sheet1!A2:K10000,COUNTIFS(Sheet2!A:A,Sheet1!F2:F10000,Sheet2!B:B,Sheet1!I2:I10000)>0,"无匹配结果")

公式说明：

• Sheet1!A2:K10000是总表的所有数据区域
• COUNTIFS部分用来判断总表当前行的物种+酶名组合是否在参考表中存在，存在则返回大于0的数值
• 筛选后直接返回所有符合条件的整行数据

低版本Excel #

可以通过辅助列实现：

1. 在总表的空白列（比如L列）第2行输入公式=F2&"|"&I2，下拉生成所有行的「物种+酶名」组合键
2. 在参考表的空白列（比如C列）第2行输入公式=A2&"|"&B2，下拉生成所有参考组合键
3. 回到总表，再新增一列（比如M列）第2行输入公式=IF(COUNTIF(Sheet2!C:C,L2)>0,"匹配","不匹配")，下拉填充
4. 筛选M列的「匹配」值，就能得到所有符合条件的整行数据

注意事项 #

• 匹配前请先检查两个表的物种、酶名称段有没有前后空格、大小写差异，如果有可以先用TRIM()、UPPER()等函数先做数据清洗，避免匹配失败
• 如果参考表有重复的组合，高级筛选不会重复提取对应总表行，如需保留重复匹配结果可选择公式法

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