Excel通过公式查找不存在名单

在Excel中通过公式查出旧名单中有但新名单中无的名单。

样例格式如下：

1、在B2中输入公式并下拉填充如下，目的是依次将旧名单从新名单列表中比较是否存在。

=COUNTIF($C$2:$C$7,B2)

2、成品一：在D2中输入公式如下（注意输入完成后，同时按Ctrl+Shift+Enter，以生成大括号，这个大括号不是手动输入的）：

{=IF(SMALL($B$2:$B$7*100+ROW($B$2:$B$7)-1,ROW()-1)<100,INDEX($A$2:$A$47,MATCH(SMALL($B$2:$B$7*100+ROW($B$2:$B$7)-1,ROW()-1),$B$2:$B$7*100+ROW($B$2:$B$7)-1,0)),"")}

解释一下：

1、其中

$B$2:$B$7*100+ROW($B$2:$B$7)-1

是核心数据，其中-1是为了从1往后依次递增，否则是从2依次递增，不方便后续取数，是将这些只有1或0的数据变成各不相同的数据，形如{101;2;103;104;5;6}，从而可以用于由小到大从中找出结果。

2、其中

SMALL($B$2:$B$7*100+ROW($B$2:$B$7)-1,ROW()-1)

是从刚才新生成的不同数据中，由小到大一个一个取出来，前面是IF用于判断取出的数据是否比基础数据100小，如果小于则说明曾经是0×100，而不是1×100，那么就要把这个数据在列表中的位置用MATCH查找出来，然后根据这个位置用INDEX取出相应的旧名单。比如上面就是将2;5;6对应的名单取出来，而2;5;6还同时表示所在的位置。

由此，成品二：可得出D2终极简化公式为，注意输入完之后要Ctrl+Shift+Enter才能加上大括号：

{=IF(SMALL($B$2:$B$7*100+ROW($B$2:$B$7)-1,ROW()-1)<100,INDEX($A$2:$A$47,SMALL($B$2:$B$7*100+ROW($B$2:$B$7)-1,ROW()-1)),"")}

结合上述分析，可以发现，如果像以下省略B列公式，成品三：那就只需要在D2输入一个公式并下拉即可，注意用组合键Ctrl+Shift+Enter加上大括号：

{=IF(SMALL(COUNTIF($C$2:$C$7,$A$2:$A$7)*100+ROW($B$2:$B$7)-1,ROW()-1)<100,INDEX($A$2:$A$47,SMALL(COUNTIF($C$2:$C$7,$A$2:$A$7)*100+ROW($B$2:$B$7)-1,ROW()-1)),"")}

说明，如果数据量超过100个，可以将上述100改为10^4

继续简化上述公式，成品四：D2输入公式并下拉，注意组合键加大括号：

{=IFERROR(INDEX($A$2:$A$7,SMALL(COUNTIF($C$2:$C$7,$A$2:$A$7)*10^4+ROW($A$2:$A$7)-1,ROW()-1)),"")}

引申，成品五：D2输入公式并下拉，注意组合键加大括号：

{=INDEX($A$2:$A$8,SMALL(IF(COUNTIF($C$2:$C$8,$A$2:$A$8)=0,ROW($A$2:$A$8)-1,ROW($A$8:$A$8)-1),ROW()-1))&""}