两列双向查找差异值：实现Col1与Col2互斥值识别需求

需求说明 #

双向识别两列中的互斥值：查找Col1中存在但Col2中不存在的值，以及Col2中存在但Col1中不存在的值，规则如下：

• 针对Col1的每一行值，若在Col2中找到匹配项，则在Col3中写入0；若未找到，则保留原数值或标记错误/NA均可。
• 针对Col2的每一行值，若在Col1中找到匹配项，则在Col4中写入0；若未找到，则保留原数值或标记错误/NA均可。
• 若Col1中某值重复出现多次，而Col2中仅出现一次，则第一个匹配项写0，其余未匹配项标记错误/保留原值。

示例数据集 #

预期结果 #

解决方案 #

VLOOKUP无法处理“重复值仅匹配一次”的规则，这里用IF+COUNTIF的组合公式来实现：

计算Col3（匹配Col1到Col2） #

在Col3的首个数据单元格（比如C2）输入以下公式，下拉填充至所有行：

=IF(AND(A2<>"",COUNTIF($B$2:$B$6,A2)>COUNTIF($C$1:C1,0)),0,A2)

公式逻辑：

• 先排除Col1为空的行
• 统计Col2中当前Col1值的总出现次数，再统计当前行之前Col3已经写入0的次数（即已匹配的次数）
• 当总匹配次数大于已使用次数时，写入0，否则保留原数值

计算Col4（匹配Col2到Col1） #

在Col4的首个数据单元格（比如D2）输入以下公式，下拉填充至所有行：

=IF(AND(B2<>"",COUNTIF($A$2:$A$6,B2)>COUNTIF($D$1:D1,0)),0,B2)

逻辑和Col3完全一致，仅调换了Col1和Col2的引用范围。

如果需要标记错误而非保留原值，只需把公式末尾的A2/B2替换为"error"或NA()，例如：

=IF(AND(A2<>"",COUNTIF($B$2:$B$6,A2)>COUNTIF($C$1:C1,0)),0,"error")

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