Excel跨表按nmsid列匹配后比对邮箱列 匹配/不匹配公式求助

前提约定 #

为方便公式直接套用，先做如下位置约定，可根据实际表格存放位置调整公式里的工作表、列号引用：

• spreadsheet1（产品表）放在Sheet1，表头在第1行：nmsid_prod为A列，email_id_prod为B列，数据从第2行开始
• spreadsheet2（联系人表）放在Sheet2，表头在第1行：nmsid_contact为A列，email_id_contact为B列，数据从第2行开始

之前用VLOOKUP/XLOOKUP未得到正确结果的核心原因：这类查找函数默认仅返回匹配到的第一条记录值，同一个ID对应多个邮箱时，无法覆盖全量邮箱的比对逻辑，会出现漏判。用多条件计数的思路可以解决这个问题。

步骤1：逐行打比对标记 #

在Sheet1新增两列作为标记列：

• C列（Match标记）：点击C2单元格，输入以下公式后下拉填充到所有数据行

=IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,B2)>0,"Match","")

公式逻辑：统计Sheet2中「ID和当前行ID一致、邮箱和当前行邮箱一致」的记录数，大于0就说明存在ID+邮箱完全匹配的记录，标记为Match，否则留空。

• D列（Mismatch标记）：点击D2单元格，输入以下公式后下拉填充到所有数据行

=IF(AND(COUNTIF(Sheet2!A:A,A2)>0,COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,B2)=0),"Mismatch","")

公式逻辑：先判断当前行ID在Sheet2中存在，再判断当前行ID对应的所有邮箱里，没有和当前行邮箱一致的记录，两个条件同时满足就标记为Mismatch，否则留空。

步骤2：生成两类输出结果 #

输出1：ID匹配且邮箱一致的Match结果 #

365/2021及以上新版Excel（支持动态数组）

直接找一个空白区域的起始单元格（比如F2），输入以下公式即可自动生成去重后的ID列：

=UNIQUE(FILTER(A:A,C:C="Match"))

在ID列右侧列（G2）输入Match，下拉填充到和ID列齐平，就得到要求的结果表：

2019及更早旧版Excel（无动态数组功能）

• 选中Sheet1全量数据区域，点击顶部菜单栏「数据」-「筛选」
• 点击C列表头的筛选箭头，仅勾选Match选项，确定后筛选出所有标记为Match的行
• 复制筛选后A列的ID内容，粘贴到新工作表，点击「数据」-「删除重复值」，保留唯一ID
• 在ID列右侧列统一填入Match即可。

输出2：ID匹配但邮箱不一致的Mismatch结果 #

365/2021及以上新版Excel（支持动态数组）

直接找一个空白区域的起始单元格（比如I2），输入以下公式即可自动生成去重后的ID列：

=UNIQUE(FILTER(A:A,D:D="Mismatch"))

在ID列右侧列（J2）输入Mismatch，下拉填充到和ID列齐平，就得到要求的结果表：

2019及更早旧版Excel（无动态数组功能）

• 保持筛选状态，点击D列表头的筛选箭头，仅勾选Mismatch选项，确定后筛选出所有标记为Mismatch的行
• 复制筛选后A列的ID内容，粘贴到新工作表，点击「数据」-「删除重复值」，保留唯一ID
• 在ID列右侧列统一填入Mismatch即可。

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