vlookup想查询多个值,我该怎么办?
“我的表格中某个人或者某个代码的记录出现多次,现在想用vlookup根据这个人或这个代码来查询出其出现的所有记录,我该怎么办?”你是否也遇到过这个问题呢?我想或迟或早,你都会遇到这个问题的。通常我们知道vlookup只返回查找到的第一个值,但现在要返回多个值,这确实是一个问题。今天就来说下vlookup如何实现一对多的查询。
-01-
具体应用
1.根据姓名查询出所有的记录
如下图所示,用vlookup根据姓名查询出所有的记录,实现筛选的功能。也就是我说的一对多的查询。具体怎么做的下面一步一步说明。
对于相同的名字vlookup只返回查到的第一个值,所以我们需要将相同的名字变成不同的名字,那就用它出现的次数给它添加一个序号。比如,刘卓第一次出现就是刘卓1,第二次出现就是刘卓2。
在A列添加一个辅助列,在A2单元格输入公式=COUNTIF(B$2:B2,H$1),下拉。这样H1单元格的姓名在B列中第1次出现的时候,A列中对应的序号就是1,第2次出现的时候对应的序号就是2。
然后将B列的姓名和A列的序号连接起来作为vlookup查询区域的第1列,也就是$B$2:$B$11&$A$2:$A$11这部分。vlookup查询区域的第2列还是B列,也就是B$2:B$11这部分。实际就是下图这样。但是现在这两列还是单独的两列,怎么让它们组合在一起呢?用if函数,IF({1,0},$B$2:$B$11&$A$2:$A$11,B$2:B$11)这样就组合在一起了,作为vlookup的第2参数,也就是查询区域。
由于vlookup查询区域的第1列连接了序号,所以查询的值,也就是姓名,也要连接序号,可以用row函数,所以vlookup的第1参数就为$H$1&ROW(1:1),右拉序号不变,下拉序号加1。正好符合查询区域。
由于vlookup的查询区域只有2列,所以返回第2列,第3参数为2;查找方式是精确查找,第4参数为0。所以完整的公式为=VLOOKUP($H$1&ROW(1:1),IF({1,0},$B$2:$B$11&$A$2:$A$11,B$2:B$11),2,)。
这个公式可以右拉,是由于vlookup查询区域的第2列的列号没有锁定,右拉的时候会相对变化。但是下拉的时候会出现错误值#N/A。可以用ifna处理这种错误,最后的公式为=IFNA(VLOOKUP($H$1&ROW(1:1),IF({1,0},$B$2:$B$11&$A$2:$A$11,B$2:B$11),2,),""),不要忘了按三键。
如果用辅助列的话,用上面那种方法还不如用下面的方法。辅助列的公式为=B14&COUNTIF(B$14:B14,H$13),实际就是将姓名和序号连接起来放在辅助列中。然后就可以将A13:E23这个区域作为vlookup的查询区域,最后的公式为=IFNA(VLOOKUP($H$13&ROW(1:1),$A$13:$E$23,COLUMN(B:B),),""),而且这个公式还不是数组公式。
如果不用辅助列,你可以做到吗?在G30单元格输入公式=IFNA(VLOOKUP($H$27&ROW(1:1),IF({1,0},$A$28:$A$37&COUNTIF(OFFSET($A$28,,,ROW($28:$37)-27),$H$27),A$28:A$37),2,),""),三键结束,右拉下拉完成。这种思路和第一种方法是一样的,只不过将序号放在一个数组中,这样就可以免去辅助列,用的是offset的多维引用。感兴趣的话,你可以研究一下。