作为一名数据分析师,工作中打交道最多的就是数据,大部分都是用Excel处理,很早之前觉得数据匹配查询只用vlookup函数就够了,但是vlookup函数有个弊端,就是无法反向查找。
如下图所示,用姓名去查找工号,单独用vlookup函数是无法完成的:
平时遇到这种情况,个人最常用的方法就是将数据源中的B列姓名复制到A列工号之前,在利用vlookup函数匹配,但是在遇到表格数据较多,或者表格操作空间较少,这种方法较为耗时,可操作性差。
那么有其它办法吗?下面分享三个可以逆向查找的小技巧,希望对大家有所帮助!
vlookup if(或者choose)
下图的示例中,用姓名去匹配个人工号,在F2单元格输入公式:
=VLOOKUP(E2,IF({1,0},B:B,A:A),2,0),公式下拉,核对数据,可以发现公式可以正确返回结果。
vlookup第二个参数表示要匹配的区域,这里用IF({1,0},B:B,A:A)代替,if函数的参数1是个数组,其中1代表true,0代表false;
true返回if第二个参数,即B:B列内容,false返回if第三个参数,即A:A列内容,这样,IF({1,0},B:B,A:A)相当于重新组合出一个虚拟数组,数组形式为B(姓名)A(工号),这个虚拟数组组成vlookup函数的参数2,这样给vlookup才能返回正确的结果。
小结:
IF({1,0},B:B,A:A)构建了一个虚拟数组,也就是将查找列与匹配列正向化,使vlookup功能能够正常使用。
这里也可以利用vlookup choose方法,公式如下:'=CHOOSE({1,2},B:B,A:A)',原理同vlookup if相似,这里就不在赘述。
index match
这两个函数组合使用功能还是蛮强大的,容易理解,更方便掌握。
第一步,先找出'小天'在B列中的行位置,F2单元格输入:'=match(E2,B:B,0)',函数返回5,核对数据源,小天的确处在B列中的第五行;
第二步,取出A列的第五行的数,即为'小天'的工号,逻辑上大家可以理解吧,F2单元格输入:'=index(A:A,match(E2,B:B,0))',函数返回正确的结果。
注:此法index()有三个参数,这里只用到前两个参数,第三个参数忽略。
lookup法
原则上lookup可以替代vlookup函数,因为lookup函数可以实现vlookup函数的所有功能,只不过vlookup足够应付大部分的数据匹配被大家熟知。
在F2单元格输入:'=LOOKUP(1,0/(E2=B:B),A:A)',公式下拉,完成匹配。
公式:LOOKUP(查找的值,查找区域,返回区域)
第二个参数'查找区域'的数据必须按升序排列,且第三个参数区域的大小必须与第二个参数区域大小一致,否则函数LOOKUP不能返回正确的结果;
为了解决升序问题,引入了LOOKUP(0,0/条件,返回区域)来解决这一问题,参数二中的'条件'成立返回true,不成立返回false;
0/true返回0,0/false返回#DIV/0!,说明满足条件返回0,不满足返回#DIV/0!,这时只要用一个大于等于0的数值查找就可以返回正确结果了,因为lookup默认忽略错误值,相当于排序。
觉得比较饶的小伙伴,直接套用公式即可!
联系客服