countif函数比较两列不同_Excel 对比两列数据，提取相同和不同

对比两列数据，找相同项和不同项是实际工作中经常遇到的需求。今天和大家分享3个公式，分别是：

1. 提取两列数据相同的数据

2. 提取左侧列有，右侧列没有的数据

3. 提取左侧列没有，右侧列有的数据

我们的示例数据如下，是豆瓣 TOP 20 和 IMDB TOP 20 电影的名称：

公式1：提取两列都有的数据

通用公式如下：

=INDEX(左侧列,SMALL(IF(COUNTIF(右侧列,左侧列数据区域)>0,ROW(左侧列数据区域),大于总数据的一个数字), ROW(A1)))&""

上述公式是数组公式，使用时需要以三键输入(Ctrl+Shift+Enter)。

本例中的公式如下：

=INDEX(B:B,SMALL(IF(COUNTIF(C:C,$B$3:$B$22)>0,ROW($B$3:$B$22),1000), ROW(A1)))&""

公式2：提取仅在左侧列的数据

通用公式如下：

=INDEX(左侧列,SMALL(IF(COUNTIF(右侧列,左侧列数据区域)=0,ROW(左侧列数据区域),大于总数据的一个数字), ROW(A1)))&""

上述公式是数组公式，使用时需要以三键输入(Ctrl+Shift+Enter)。与第一个公式的差异在 COUNTIF(右侧列,左侧列数据区域)=0处，在这里是等于0(=0)。

本例中的公式如下：

=INDEX(B:B,SMALL(IF(COUNTIF(C:C,$B$3:$B$22)=0,ROW($B$3:$B$22),1000), ROW(A1)))&""

在E3单元格输入后，向下复制。

公式3：提取仅在右侧列的数据

通用公式如下：

=INDEX(右侧列,SMALL(IF(COUNTIF(左侧列,右侧列数据区域)=0,ROW(右侧列数据区域),大于总数据的一个数字), ROW(A1)))&""

上述公式是数组公式，使用时需要以三键输入(Ctrl+Shift+Enter)。与第二个公式的差异是对应的左右列和区域相反。

本例中的公式如下：

=INDEX(C:C,SMALL(IF(COUNTIF(B:B,$C$3:$C$22)=0,ROW($C$3:$C$22),1000), ROW(A1)))&""

在E3单元格输入后，向下复制。

最后

大家可能已经发现了，今天的公式与昨天的模糊搜索公式是类似的。正如一位读者评论的一样，这个是一个万金油公式，用于一对多查询的场景。公式的核心是SMALL函数的第一个IF函数参数的判断，通过不同的判断公式，可以返回不同的结果。

今天的分享就到这里，祝大家一天好心情。

更多查找公式

▶ 使用 INDEX + MATCH 函数按条件查找

▶ VLOOKUP 函数区间查找，看这篇就明白了

▶ 学会这 4 种查找公式，单条件查找不在话下

▶ Excel 多条件查找公式(数字类)

▶ Excel 使用关键词搜索并返回多个匹配的数据(模糊搜索)

        觉得有用 ↓↓↓