首页 > Excel专区 > Excel函数 >

你还不会excel逆向查找?这三个excel小技巧分享给你vlookup+if(或者choose)index+matchlookup法

Excel函数 2022-02-27 00:11:45

作为一名数据分析师,工作中打交道最多的就是数据,大部分都是用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列中的第5行;

你还不会逆向查找?这三个小技巧分享给你

第二步,取出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(0,0/条件,返回区域)来解决这一问题。

觉得麻烦的小伙伴,直接套用公式即可!



标签: excel逆向查找

关于本站 联系我们 免责声明 最新信息 热门文章 登录发布
八维库 Copyright © 2016-2021 baweiku.com Rights Reserved. 粤ICP备2021175789号