VLOOKUP七大高级用法，会用的都是EXCEL函数高手!

VLOOKUP七大高级用法，会用的都是EXCEL函数高手！

一、查找多行

场景：查找WPS2、WPS3的工资。

公式：

=VLOOKUP(F3:F4,C2:D5,2,0)

解析：

F3:F4为VLOOKUP的第一参数，表示同时查找“WPS2、WPS3”，好处在于不用下拉填充公式，结果就可以返回每个查找对象对应的工资。

二、查找多列

场景：查找序号1对应的部门、姓名、工资。

公式：

=VLOOKUP(F3,A1:D5,{2,3,4},0)

解析：

{2,3,4}为VLOOKUP的第三参数，表示同时查找序号1对应的第2、3、4列数据，结果返回序号1对应的部门、姓名、工资。

如果将第三参数改为{2;3;4}，查找结果还会纵向排为一列。

三、定位查找

场景：动态查找序号0任意两个标题对应的数据。

公式：

=VLOOKUP(F4,A1:D5,XMATCH(G3:H3,A1:D1),0)

解析：

XMATCH(G3:H3,A1:D1)定位“姓名、工资”行标题所在的列位置，作为VLOOKUP的第三参数，结果返回查找对象对应的“姓名、工资”。

当改变查找的标题行时，定位的结果改变带来VLOOKUP第三参数的改变，从而使VLOOKUP达到动态查找的目的。

四、逆向查找

场景：查找WPS2的序号。

公式：

=VLOOKUP(E3,HSTACK(C1:C5,A1:A5),2,0)

解析：

HSTACK(C1:C5,A1:A5)将C列A列重新横向拼接为新的数组，作为VLOOKUP的第三参数，从而使VLOOKUP实现从右向左逆向查找。

五、查找比对

场景：核对两个数据区域是否一致。

公式：

=IF(VLOOKUP(E3:E6,B9:D12,2,0)=B2:B5,"√","×")

解析：

VLOOKUP在B9:D12数据范围内查行查找，将查找到的结果与B2:B5数据区域的值对比，相同返回"√",不相同返回"×"，从而实现数据核对功能。

六、一对多查找

场景：查找部门A对应的多个姓名。

公式：

=LET(X,SCAN(0,B2:B5=E3,SUM),VLOOKUP(UNIQUE(X)&E3,HSTACK(X&B2:B5,C2:C5),2,0))

解析：

虽然一对多查找FILTER很容易实现，但是VLOOKUP在多个函数帮助下也不是不能实现。

公式的核心思路是重构数据区域：

首先多个相同的查找对象部门A，由SCAN(0,B2:B5=E3,SUM)生成{1;1;2;3}数组序列，再与A连接生成{"1A";"1A";"2A";"3A"}，UNIQUE去重就会得到{"1A";"2A";"3A"}作为查找对象。

然后{"1A";"1A";"2A";"3A"}与C2：C5用HSTACK横向拼接，得到新的数组{"1A","WPS1";"1A","WPS2";"2A","WPS3";"3A","WPS4"}作为查找范围。

这样，查找对象、查找范围都得到了重构，VLOOKUP就可以轻松实现一对多查找了。

七、循环查找

场景：查找D对应的所有下级，即查出D的下级E，再继续查找E的下级F，查到F无下级返回结束。

公式：

=f(D3,A2:B7)

解析：D3为f函数的第一参数，A2:B7为f函数的第二参数，公式中并没有看到VLOOKUP出现。

实际VLOOKUP在f函数中，f函数为一个自定义函数，方法是在“公式”-“名称管理”中定义一个函数f，公式如下：

=LAMBDA(a,b,LET(x,VLOOKUP(a,b,2,0),IF(x="","结束",HSTACK(x,f(x,b)))))

这样，就可以通过f函数实现循环查找。