如何用Excel公式实现Tel值跨列匹配（排除同行匹配）

我在Excel中有如下数据集，包含Tel、Mob、Off、Checking列：

需求：检查每行的Tel值是否在Tel、Mob、Off三列的其他行中存在匹配（同行内的匹配不计入），最终得到Checking列所示的结果。

我尝试使用公式：

IF((COUNTIF($A$2:$A$7,A2) + COUNTIF($B$2:$B$7,A2) + COUNTIF($C$2:$C$7,A2))<2,"No match","Match")

但未得到预期结果。请问除Kutools和VBA外，还有哪些Excel公式可以实现该需求？

解决方案 #

你的原公式问题在于：没有排除当前行内的匹配（比如第4行Tel和同行Mob重复会被统计），且Tel列的计数包含了当前行自身，导致误判。以下是几种可行的公式方案：

方法1：SUMPRODUCT精准排除当前行 #

在D2单元格输入以下公式，下拉填充：

IF(SUMPRODUCT(($A$2:$C$7=A2)*(ROW($A$2:$C$7)<>ROW(A2)))>0,"Match","No match")

逻辑说明：

• $A$2:$C$7=A2：遍历三列所有单元格，标记出等于当前Tel值的位置
• ROW($A$2:$C$7)<>ROW(A2)：排除当前行的所有单元格，避免同行匹配被统计
• 两个条件相乘后用SUMPRODUCT求和，结果>0则说明其他行存在匹配，返回"Match"

方法2：COUNTIF组合修正计数 #

公式：

IF((COUNTIF($A$2:$A$7,A2)-1)+COUNTIF($B$2:$B$7,A2)+COUNTIF($C$2:$C$7,A2)>0,"Match","No match")

逻辑说明：

• COUNTIF($A$2:$A$7,A2)-1：计算Tel列中当前值的出现次数，减去当前行的1次，得到其他行的匹配数
• 直接加上Mob、Off列的匹配数，总和>0则说明存在跨行匹配

方法3：XLOOKUP（仅适用于Excel 365/2021及以上版本） #

公式：

IF(NOT(ISERROR(XLOOKUP(A2,CHOOSE({1,2,3},$A$2:$A$7,$B$2:$B$7,$C$2:$C$7),,0,1))),"Match","No match")

逻辑说明：

• CHOOSE({1,2,3},...)：将三列数据合并为一个垂直数组
• XLOOKUP(...,0,1)：精确匹配当前Tel值，1参数表示跳过第一个匹配项（即当前行的自身匹配）
• 若找到其他行的匹配则返回对应值，否则返回错误，通过ISERROR和NOT判断后输出结果

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