是否可以逐一罗列出两列数据的相同值？当数据源中的值变化时，提取出来的相同值也相应地更新。

以图7.80为例，A列和B列分别是去年和今年的优秀员工姓名，要求在D列罗列出两年都是优秀员工的姓名。

图7.80 去年与今年的优秀员工

解题步骤

判断两列是否存在相同项，使用COUNTIF函数计数即可，然后配合IF函数排除不符合条件的值，具体操作步骤如下。

1.在D1中输入标题“相同项”（见图7.81）。

2.选择D2：D11区域，然后输入以下数组公式：

输入公式后必须按组合键＜Ctrl+Shift+Enter＞结束，只按Enter键无法得到正确结果。

图7.81 提取两列的相同项

知识扩展

1.COUNTIF函数用于计算满足条件的单元格数量，第一参数是区域，第二参数是条件，条件可以是单个的也可以包含多个，当使用单个条件时统计结果也是单个的；当使用多个条件时，统计结果也包含多个，不过必须使用＜Ctrl+Shift+Enter＞组合键输入公式，否则只能取得单个结果。

2.本例中表达式“COUNTIF（B2：B10，A2：A11）”的含义是逐一统计A2：C11区域中每个单元格的值在B2：B10中的出现次数，如果未出现在B2：B10中则返回值为0，否则返回出现次数。表达式的计算结果包含10个值，使用数组公式才能一次性取得这10个值。

3.公式“=IF（COUNTIF（B2：B10，A2：A11）＞0，A2：A11，＂＂）”的含义是如果A2：A11区域中某个单元格的值出现在B2：B10区域，那么就返回该值本身，否则返回空文本。

IF函数在本例中的作用是排除不符合条件的值，使其返回空文本。可以通过以下方式理解公式的运算过程。

首先用COUNTIF函数统计A2：C11区域中每个单元格的值在B2：B10中的出现次数，效果如图7.82所示。

图7.82 统计A2∶C11的值在B2∶B10中的出现次数

图 7.82 中公式的返回值大于 0 时表示该姓名同时出现在去年和今年的区域中，此时用“COUNTIF（B2：B10，A2：A11）＞0”作为IF函数的条件执行判断就可以提取符合条件的姓名，排除其他姓名。

4.本例公式提取出来的姓名未放置在相邻的单元格中，查看时不太方便。改用以下数组公式可以让结果放置在相邻的单元格。

以上公式的使用方法是：在D2单元格输入公式，按组合键＜Ctrl+Shift+Enter＞结束，然后按下填充柄将公式向下填充到D11单元格中（见图7.83）。

图7.83 将两列的相同值放在相邻的单元格

此公式的含义是：如果A2：A11区域中某个单元格的值出现在B2：B10区域中，那么返回其行号，否则返回行号9999。接着使用SMALL函数对这些行号从小到大排序，最后使用INDEX函数从A列提取目标姓名，姓名的行号由SMALL函数生成的升序行号决定。

由于符合条件的姓名都是生成该姓名所在行的行号（本例为2、4、10），不符合条件时则生成9999，因此SMALL函数产生的升序行号其实是2、4、10、9999、9999、9999、9999、9999、9999、9999，如图7.84所示。

图7.84 模拟SMALL函数生成的行号

此时使用INDEX函数从以上行号提取姓名即可，由于A9999没有姓名，因此INDEX会返回数值0，为了将0转换成空白，在INDEX函数的后面添加了表达式“&＂＂”。

以上公式比较复杂，对于未系统学习数组公式的用户而言理解比较困难。不过这属于本案例的补充知识，读者可以忽略此公式，或者加入本书的售后服务群向图书作者提问。