lookup也能实现一对多的查询

最近对怎么用函数来实现筛选的功能有点想法，所以就写几篇关于这个问题的文章。昨天说了用vlookup函数来实现一对多的查询，也就是筛选的功能。其实用lookup函数也可以实现这个功能，接下来就说一说如何实现。

-01-

具体应用

1.根据姓名查找出所有出现的记录

还是昨天那个例子，今天用lookup函数来完成，思路和昨天是一样的。先要给姓名后面添加序号，让相同的姓名变成不同的姓名。

如下图所示，在A列添加个辅助列，A2单元格中输入公式=COUNTIF(B$2:B2,H$1)，下拉。这样H1单元格的姓名在B列中第1次出现时，A列对应的序号就是1；第2次出现时对应的序号就是2。可以看下图的红框标记出来的单元格。

下一步就是将B列的姓名和A列的序号连接起来，也就是$B$2:$B$11&$A$2:$A$11这部分，形成的效果如下。

形成的这1列要和H1单元格的姓名进行比较，看是否相等。由于一个添加了序号，一个没有添加序号，肯定是不会相等的。所以要给H1单元格也连接个序号，可以用row函数。也就是$H$1&ROW(1:1)这部分，下拉时序号会增加，就相当于黛玉1，黛玉2。

$B$2:$B$11&$A$2:$A$11=$H$1&ROW(1:1)这样就可以进行比较了，黛玉第1次出现时，会找到黛玉1；下拉也就是第2次出现时，会找到黛玉2。

然后就可以用lookup来查找了，公式为=LOOKUP(1,0/($B$2:$B$11&$A$2:$A$11=$H$1&ROW(1:1)),B$2:B$11)。这是lookup的经典查找方式，LOOKUP(1,0/(条件区域=条件),返回区域)。由于返回区域的列号没有锁定，所以右拉时会相对变化，这样刚好符合我们的需要。

但是下拉时会出现错误值，要处理错误，最后的公式为=IFNA(LOOKUP(1,0/($B$2:$B$11&$A$2:$A$11=$H$1&ROW(1:1)),B$2:B$11),"")。右拉下拉完成。

另一种简单的方法就是在辅助列中将姓名和序号连接起来。在A15单元格输入公式=B15&COUNTIF(B$15:B15,H$14)，下拉。最后的公式就会简化一些，在G17单元格输入公式=IFNA(LOOKUP(1,0/($A$15:$A$24=$H$14&ROW(1:1)),B$15:B$24),"")，右拉下拉完成。

如果不用辅助列呢？公式为=IFNA(LOOKUP(1,0/($A$28:$A$37&COUNTIF(OFFSET($A$28,,,ROW($28:$37)-27),$H$27)=$H$27&ROW(1:1)),A$28:A$37),"")。还是用到offset的多维引用，将序号列放在1个数组中，就可以免去辅助列。

这个公式还可以写为=IFNA(LOOKUP(1,0/(($A$28:$A$37=$H$27)*(COUNTIF(OFFSET($A$28,,,ROW($28:$37)-27),$H$27)=ROW(1:1))),A$28:A$37),"")。

其实今天和昨天的思路是一样的，后来我才意识到这种思路是把一对多查询的问题转化成了多条件查询的问题。

链接：

https://pan.baidu.com/s/1KMpogKXY2J8DQPwFq90EOQ

提取码：p4bv