比较两列的值并提取不相同的数据

如下图1所示，要提取列C中西区超市有而列A中东区超市没有的水果，如何编写公式呢？

图1

先不看答案，自已动手试一试。

---

公式

在单元格A10中的数组公式：

=IFERROR(INDEX($C$2:$C$6,SMALL(IF(ISNA(MATCH($C$2:$C$6,$A$2:$A$5,0)),ROW($C$2:$C$6)-ROW($C$2)+1),ROWS(A$10:A10))),"")

如图2所示。

图2

向下拖至单元格中没有数据为止。

公式解析

公式中的：

MATCH($C$2:$C$6,$A$2:$A$5,0)

在单元格区域A2:A5中依次查找单元格区域C2:C6中的值，如果找到则返回该值在单元格区域A2:A5中的位置，否则返回#N/A值，最后生成的数组为{#N/A;4;1;2;#N/A}。将数组传递给ISNA函数，生成数组{TRUE;FALSE;FALSE;FALSE;TRUE}，作为IF函数的第1个参数。

公式中的：

ROW($C$2:$C$6)-ROW($C$2)+1

生成数组{1;2;3;4;5}，作为IF函数的第2个参数。

这样，IF函数就生成数组{1;FALSE;FALSE;FALSE;5}，作为SMALL函数的第1个参数。

公式中的：

ROWS(A$10:A10)

根据当前单元格生成一个数字，在A10中的数字为1，在A11中为2，将此数字作为SMALL函数的第2个参数。

公式演化为：

=IFERROR(INDEX($C$2:$C$6,SMALL({1;FALSE;FALSE;FALSE;5},1)),"")

SMALL函数求值后公式为：

=IFERROR(INDEX($C$2:$C$6,1),"")

获取单元格C2中的值。

扩展

如果要提取两列中相同的数据。如本例中，提取两个超市中都有的水果，数组公式为：

=IFERROR(INDEX($C$2:$C$6,SMALL(IF(ISNUMBER(MATCH($C$2:$C$6,$A$2:$A$5,0)),ROW($C$2:$C$6)-ROW($C$2)+1),ROWS(E$10:E10))),"")

小结

• SMALL函数忽略参数中的任何非数字的数据。

• ISNA函数将错误值转换为TRUE，否则为FALSE。ISNUMBER函数的运用同理。

• IF函数一次性判断获取数组。

• 公式适用于Excel2007及以上版本。