【原】突破VLOOKUP函数限制，查找多个对应值

送书活动又来了！今天送3本《Excel函数跟卢子一起学　早做完，不加班》，从留言区随机抽奖。

VLOOKUP函数很神奇，不过并非万能查找函数，比如根据著作查找所有人物。

直接用VLOOKUP函数进行查找，只能查找到第一个对应的人物，没法查找到全部人物。

=IFERROR(VLOOKUP($E2,$B:$C,2,0),"")

究竟该如何突破VLOOKUP函数的限制，让VLOOKUP函数可以查找到全部对应值呢？

思路：在查找的时候，唯一值才可以查找，著作都不是唯一值，没办法直接查找。如果添加一个辅助列，获取著作+次数，就变成了唯一值，这样就可以突破VLOOKUP函数的局限。

添加一个辅助列次数，在A2输入公式下拉填充。

=B2&COUNTIF($B$2:B2,B2)

区域采用$B$2:B2这种写法，估计很多初学者不理解，卢子这里详细说明一下。

$B$2加美元$锁定行号和列号，这样下拉的时候，就不会进行任何改变，依然是$B$2，也就是绝对引用。

B2因为没有加美元$锁定，所以下拉的时候就变成了B3、B4、B5……，这种就叫相对引用。

一个锁定，一个不锁定，这样下拉的时候，就可以让区域逐渐变大。用COUNTIF函数，就可以依次获取著作的出现次数。

左边的查找区域原理知道了，现在来看右边如何用VLOOKUP函数查找。

在F2输入公式下拉和右拉。

=IFERROR(VLOOKUP($E2&COLUMN(A1),$A:$C,3,0),"")

现在以红楼梦为例进行说明，红楼梦一共出现3次，也就是红楼梦1、红楼梦2、红楼梦3。

而E2单元格只是红楼梦缺少次数，数字1、2、3可以通过COLUMN函数获取。A就对应1，B就对应2，依次类推。

这样用下面的公式就完成了查找。

=VLOOKUP($E2&COLUMN(A1),$A:$C,3,0)

不过这样直接查找，没有对应值会显示错误值，不太美观，因此嵌套一个IFERROR函数，让错误值显示空白。

下面再补充一些方法。

同理，LOOKUP函数借助辅助列，也能查找多个对应值。

=IFERROR(LOOKUP(1,0/($E2&COLUMN(A1)=$A$2:$A$12),$C$2:$C$12),"")

除此之外，不用辅助列也可以。用筛选公式，当年这条公式也算经典，不过现在很少用了。

=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$12=$D2,ROW($2:$12)),COLUMN(A1))),"")

如果是Office365，这种问题也很简单。前几天说过，FILTER函数可以将查找所有符合条件的值，不过是返回一列，现在是返回一行，再嵌套TRANSPOSE函数进行转置（跟选择性粘贴转置一样）。

=TRANSPOSE(FILTER($B$2:$B$12,$A$2:$A$12=D2))

如果是WPS最新版的，那就非常尴尬，刚测试了，多单元格数组不支持下拉。只能参考筛选公式的模式改公式。

=IFERROR(INDEX(FILTER($B$2:$B$12,$A$2:$A$12=$D2),COLUMN(A1)),"")

其实，只要熟练掌握VLOOKUP函数就行，其他用法大概知道就行。

推荐：VLOOKUP函数之魅

上篇：真伤脑！VLOOKUP、LOOKUP、INDEX+MATCH都解决不了的多行多列查找难题

FILTER函数，我也是临时想起来的。发现WPS新版本出来的函数不成熟，用起来别扭。本来准备以这个大写一篇文章，后来想想算了，文末稍微提一下，一笔带过。

平常有想法，就多动手测试，一来可以巩固老知识，二来可以发现新知识，我就是靠这种方法，不断的学Excel。

作者：卢子，清华畅销书作者，《Excel效率手册 早做完，不加班》系列丛书创始人，个人公众号：Excel不加班（ID：Excelbujiaban）