VLOOKUP函数一对多查找，掌握方法后就是这么简单

yund56 2025-07-03 21:14 15 浏览

如果查找区域中有多个查找对象，用VLOOKUP函数查找时，只返回该列中自上而下第一个查找对象所对应的值。

如下图，我们要查找“总经办”有哪几个人员？

如果直接用VLOOKUP进行查找，返回的结果为“高磊”，它是“姓名”列中自上而下的第一个结果，向下拖动公式返回的是同一个结果。

那么我们要查找某个部门有哪几位员工应该如何操作呢？作为熟练掌握EXCEL的人员来说，能想到多个方法。今天我们来讲的是用VLOOKUP函数实现一对多查找，这个不仅仅是VLOOKUP函数的一种用法，更是在解题过程中可以学到一些解题思路，对其它问题的解决也有帮助。

思考：

用VLOOKUP查找时，只返回自上而下第1个值的内容，那么我们是不是可以把部门名称都变为唯一值，这样VLOOKUP查找时，可以返回每个唯一值所对应的值呢？

再来考虑一下，我们在F3单元格输入公式后向下拖动，行号是递增1的，那么是不是就可以把部门名称改为“总经办1、总经办2……”，这样依次对应第1个、第2个……出现的姓名呢？

我们用这种试一下，要显示某个人名在部门中是第几次出现，可以用COUNTIF来解决。

在“部门”列前面插入一个辅助列，在A2单元格中输入公式：

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

双击向下填充。

我们会发现，同一个部门每出现一个姓名，对应的辅助列单元格中数字序号就加1，这样我们和部门名称用文本连接符“&”连接起来后，就会形成唯一值。

把A2单元格公式修改为：

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

双击向下填充公式。

唯一值生成了，我们可以把查找公式修改一下：

=VLOOKUP($F$3&ROW(A1),$A$1:$C$9,3,0)

双击向下填充。

ROW(A1)是返回A1单元格所在的行号（1），向下拖动的话，依次变为ROW(A2)、ROW(A3)……，形成数字序列1、2、3……，总经办和数字序列连接后，变成“总经办1、总经办2、总经办3……”，这样通过查找A列的值，来返回姓名列的姓名。

因为我们不能确定某个部门有多少人，所以需要把公式尽量向下拖动，直到返回错误值为止，是一但更换查找部门，可能还会出现更多的错误值。

我们通过IFERROR让错误值返回空值：

=IFERROR(VLOOKUP($F$3&ROW(A1),$A$1:$C$9,3,0),"")

双击向下填充。

这样就可以用VLOOKUP实现一对多查找，我们可以把F3单元格设置为下拉菜单，选择不同部门时，返回这个部门所有人员姓名。

用VLOOKUP函数一对多查找，有两点操作：

一是插入辅助列，通过COUNTIF函数将多项相同内容生成唯一值。
二是用VLOOKUP查找时，查找值要连接生成的序列，如果将内容生成在列中，用ROW来生成序列，如果将内容生成在行中，用COLUMN来生成序列。