Excel 公式：对比两列数据，提取相同和不同
对比两列数据，找相同项和不同项是实际工作中经常遇到的需求。今天和大家分享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函数参数的判断，通过不同的判断公式，可以返回不同的结果。
转载注明：文章转载自「懒人Excel - www.lanrenexcel.com」
=INDEX(A:A, SMALL(IF(COUNTIF(V:V, $A$3:$A$1171)=0, ROW($V$3:$V$931),5000), ROW(A1)))&""
老板请问为什么是N/A 三件结束了有大括号
对比的两列区域的长度需要一致，你的公式里A列到1171行，V列到931行，不一致。
如果两列值行数（个数）不同呢？公式需要做什么改变吗？
数据区域，按照行数多的那一列输入区域，应该没问题
=INDEX(B:B,SMALL(if(COUNTIF(C:C,$B$2:$B$7)>0,ROW($B2:$B7,1000),ROW(A1)))&""用三键输入后显示公式错误呢
公式中ROW函数多写了一个参数，1000应该是IF函数的第三个参数
没反应：=INDEX(B:B,SMALL(IF(COUNTIF(C:C,$B$3:$B$24)>0,ROW($B$3:$B$24),1000),ROW(A1)))&""
注意这是数组公式，需要三键结束
你这个SMALL最后的参数害得大家好惨啊。
=INDEX(B:B,SMALL(IF(COUNTIF(C:C,$B$3:$B$22)>0,ROW($B$3:$B$22),1000), ROW(A1)))"ROW(A1)=1,即SMALL变成了永远只反馈第一个最小行号值。此处实际跑起来就全是肖申克的救赎。单独跑SMALL部分就会发现全是行号3"
正确示例应为：=INDEX(B:B,SMALL(IF(COUNTIF(C:C,$B$3:$B$22)>0,ROW($B$3:$B$22),1000),ROW($B$3:$B$22)))),这样INDEX才会正确依次序引用B列中的3,6,10,11,19行的内容。
感觉这是个bug？似乎数组公式效果没有对SMALL函数生效，它是以整数组公式中的IF函数给出的值作为引用参数。
不是的，示例中写的没错。
文章中写了，输入第一个公式后，需要向下复制。按照Excel公式复制的原理，其中相对引用的单元格，行列将按照复制方向增加。因此，向下复制，行号将递增，也就A1→A2，以此类推。每个公式引用就是不同的内容了。
{=INDEX(A:A,SMALL(IF(COUNTIF(B:B,$A$2:$A$22)>0,ROW($A$2:$A$22),1000),ROW(A1)))&""}，请问哪里不对
数据是在A和B列吗？
为什么搞了以后没有反应
公式是数组公式，使用时需要以三键输入（Ctrl+Shift+Enter）。
是按这三个键以后再把公式编辑进去吗，还是什么意思呢？因为编辑之后，文件里显示就是公式本身，没有变成数字，按了这三个键，也没有反应
公式输入结束时，与直接按回车键不同，按三键结束。完成后，显示的公式最外城有大括号，表示正确三键结束。
这个函数嵌套还是没太懂，能出一个讲解视频吗
后期计划推出公式原理课程，欢迎关注。
这个场景在使用PQ后就简单多了，哈哈
是的，PQ也能实现此需求。不过每个方法的适用场景不同，按照自己的需要选择合适的方法。
这个函数都无法使用，显示输入函数过多
会不会是少输入了括号的原因
提取两列数据相同的数据，并在相同数据的左侧那行显示，该如何操作
这个相对简单一些。直接使用COUNTIF函数统计在另一列出现的次数，如果大于0，就是表示相同，IF函数显示即可。
请问，输入公式=INDEX(D1:D6,SMALL(IF(COUNTIF(E1:E6,$B$1:$B$6)>0,ROW($B$1:$B$6),1000),ROW(A1)))&""以ctrl+shift+enter结束出现的是#REF是什么原因？
INDEX 函数的第一个参数与 COUNTIF 函数第二个参数的引用列不一致。
老板，没有讲解呀
感谢反馈，后续会加上
写的很详细，点赞
可以留下素材 练习吗
以后的文章争取分享对应的素材，谢谢建议
你好，提取仅在左侧列的 第一遍输入之后 往下拉 出现#N/A 是什么意思呢？
公式需要以三键（ctrl+shift+enter）输入
您好，麻烦问下，我按照上面的函数公式，得出来的全是同样的结果，是什么情况呀？
注意公式需要三键（ctrl+shift+回车）结束
是三键结束，按照你上面的例子，返回的结果全部是肖申克救赎
贴一下你的公式
=INDEX(D:D,SMALL(IF(COUNTIF(F:F,$D$2:$D$381)>0,ROW($D$2:$D$381),1000), ROW(A1)))&""
数据在DF列，也是全部都是一样的数据😢
我带入自后发现最后的双引号是全角双引号，改成半角双引号可以正常显示，如下图：
很好
非常好
用格式条件
三件输入是什么意思呢
三键输入时数组公式的输入方式，而且必须以三键输入
太实用了！每次遇到这样的情况，都是使用两次vlookup。今天又学到一招，一个公式解决，太棒了。
上述公式是数组公式，使用时需要以三键输入（Ctrl+Shift+Enter.
这是什么意思啊
数组公式是特殊的公式类型，特点就是输入时，需要以三键输入（Ctrl+Shift+Enter）。关于数组公式我会再写一篇介绍文
ROW(A1)))&""部分是什么意思
这里是把值变成文本
关于公式的思路可以提供详细的解析吗
几个公式均用到万金油公式，可以看一下这篇：https://www.lanrenexcel.com/excel-keyword-return-multiple-values/
后期公式文章会发解决思路的
有附件下载吗？
暂时没有，后续会提供下载