HELLO大家好！今天和大家分享VLOOKUP函数一对多查找技巧。

会使用EXCEL函数公式的小伙伴一定知道，VLOOKUP函数可以根据查找值在查找区域内找到并返回第一次出现的内容相对应的数据，但是如果一个查找值对应多条数据，那么该如何查找并返回多条对应的数据呢？

如图，通过查找财务部，返回财务部所有的员工姓名。

 

看似这个问题很复杂，其实我们只需在数据源前面加一个辅助列就可以用VLOOKUP轻松搞定一对多查找。

如图，在数据源前面插入一列，在A2输入公式=COUNTIF(B$2:B2,$F$2) 向下填充公式，统计B$2:B2，B$2:B3，B$2:B4，B$2:B5……单元格中，F2单元格中内容的次数，通过动态区域条件计数，凡是和F2单元格相同内容形成了一个123……依次往下的编号，而且B列内容和F2单元格内容相同对应的编号数字从上往下都是第一次出现的。

 

有了辅助列的编号之后，我们发现原来查找的财务部现在只需要查找123就可以返回相对应的数据了，那么这样的问题就太简单了，我们只需用一个ROW(A1)作为VLOOKUP第一参数，公式下拉，ROW(A1)，ROW(A2)，ROW(A3)……也就是查找1，查找2，查找3……，公式=VLOOKUP(ROW(A1),A:D,3,FALSE)

 

公式向下填充之后，我们发现超出部门相对应人数时，公式计算返回错误值，那我们再嵌套一个屏蔽错误值的函数就可以完成美化表格了，公式=IFNA(VLOOKUP(ROW(A1),A:D,3,FALSE),"")

 

以上介绍的是借助辅助列完成VLOOKUP一对多查找的方法，那么能不能不添加辅助列，直接用函数公式引用数据区域写VLOOKUP一对多查找呢？

当然可以，俗话说一个篱笆三个桩，一个好汉三个帮，只需给他几个好帮手，VLOOKUP就可以直接对数据源实现一对多查找了。

公式=IFNA(VLOOKUP(ROW(A1),IF({1,0},COUNTIF(INDIRECT("B2:B"&ROW($2:$17)),F$2),$C$2:$C$17),2,FALSE),"")

公式浅析

INDIRECT("B2:B"&ROW($2:$17)) 依次对B2:B2，B2:B3，B2:B4，B2:B5……B2:B17的引用

COUNTIF(INDIRECT("B2:B"&ROW($2:$17)),F$2) 依次统计B2:B17区域中F2单元格内容的数量，形成一个和原来辅助列相同的编号

 

然后再使用IF{1,0}水平数组可以垂直扩展的原理，将数据源整合成一个2列16行的一维数组

IF({1,0},COUNTIF(INDIRECT("B2:B"&ROW($2:$17)),F$2),$C$2:$C$17)

 

有了这样一个直观的2列16行一维数组，我们可以在脑海中形成一个公式的内存数组，VLOOKUP第一参数还是查找ROW(A1)，然后将这部分内存数组作为VLOOKUP第二参数，那么这里只有两列数据，第三参数返回的列序号也自然需要写2了，第四参数精确查找，写FALSE。

VLOOKUP(ROW(A1),IF({1,0},COUNTIF(INDIRECT("B2:B"&ROW($2:$17)),F$2),$C$2:$C$17),2,FALSE)

当然，这个公式下拉当超过要查找值对应数量时还是会出现错误值，那我们还是用IFNA函数来屏蔽错误值。

这个公式是数组公式，输入公式别忘了按CTRL+SHIFT+ENTER

=IFNA(VLOOKUP(ROW(A1),IF({1,0},COUNTIF(INDIRECT("B2:B"&ROW($2:$17)),F$2),$C$2:$C$17),2,FALSE),"")

 

写函数公式，只需有了好的思路，我们需要处理的表格问题也就变得简单了，关注表哥学习更多OFFICE技术。