数据逆向查询只会vlookup就out了,Index+Match函数嵌套更实用
Excel中说到数据查询,相信绝大多数人都会想到一个函数,它就是vlookup函数。vlookup在单条件、多条件、逆向查询等多种查询操作中都起着非常重要的作用。
如上图所示,我们通过人员的工号查询对应的姓名,利用vlookup函数也是可以做到。但是逆向查询的时候,建议不使用vlookup函数,因为它需要搭配if函数进行嵌套使用,操作起来会比较麻烦。下面我们就来学习一下,如何利用vlookup函数和index+match函数进行数据的逆向查询操作。
操作一:vlookup+if函数进行数据的逆向查询
函数公式:
=VLOOKUP(H5,IF({1,0},$C$3:$C$8,$B$3:$B$8),2,0)
函数解析:
1、vlookup函数在进行数据的逆向查询的时候,需要搭配if函数进行数组操作,利用if函数重新构造vlookup的第二参数,也就是数据查询区域。
2、if函数重构vlookup的第二参数,这样的结果就是重新生成:sz0001;张三;sz0002;李四…等结构。这样vlookup函数第三参数查询第二个值,结果就是工号对应的姓名了。
操作二:Index+Match函数快速进行数据向左查询
函数公式:
=INDEX($B$2:$B$8,MATCH(H5,$C$2:$C$8,0))
函数解析:
1、Index+Match函数相比vlookup函数,使用起来会更加的容易理解;
2、Index()函数在这里的作用是查询B列姓名中第一参数数据区域中,根据第二参数指定的位置返回对应的值;
3、Match函数在这里的作用就是查询我们需要的工号,在C列工号自上往下的位置,从而作为index的第二参数,实现通过工号逆向查询对应的姓名。
通过两组函数公式的运用,现在你学会如何利用函数进行数据的逆向查询了吗?
Xlookup再牛,也打不过Vlookup+Match公式组合
在新版本的函数公式中,Xlookup公式用法简单,受到大多数朋友的喜欢,比如左边是工资表数据,我们想根据姓名,查找出多个字段的结果
如果我们使用Xlookup函数公式,只需要在H2单元格中输入公式:
=XLOOKUP(G2,A:A,B:E)
就可以得到结果了
XLOOKUP公式万能用法,=XLOOKUP(查找值,查找列,结果列)
结果列可以是单列,也可以是多列,太方便了
如果要使用Vlookup来查找的话,我们还要结合另外2个知识点才能一次性查找出来,那就是Column()公式,以及相对引用和绝对引用方式,需要输入的公式是:
=VLOOKUP($G2,$A:$E,COLUMN(B1),0)
查找值G2单元格,要按3下F4固定列标,查找数据区域A:E列要按1下F4绝对引用,第3个参数要使用Column(B2)返回B2单元格在第几列,代替我们普通的数字。
以上我们的结果列的顺序是和原数据中的顺序一模一样的,如果说不一样呢,如下所示
这个时候用Xlookup公式就不方便了,但是用Vlookup+Match组合就无敌了
首先,固定用法VLOOKUP($G2,$A:$E,第几列,0),查找不同的字段,就是在不同的列,这个时候我们可以用Match公式来自动的定位在第几列
=MATCH(H$1,$A$1:$E$1,0)
部门字段,在第一行字段名称里面去查找,返回的就是第3列,也是Vlookup的结果列位置
所以说我们把Match公式当成Vlookup的第3参数,整体的公式是:
=VLOOKUP($G2,$A:$E,MATCH(H$1,$A$1:$E$1,0),0)
这个时候,公式可以是固定的,你字段名字可以随便改,Match公式能自动定位到第几列,从而得到正常的结果
但是Xlookup公式,就没法一次性的得到多个结果了
关于这个小技巧,你学会了么?动手试试吧!
Excel数据的向左查询,index+match和lookup函数比vlookup更实用
Excel中我们都知道常见的数据查询函数,用的最多的就是vlookup函数。但是这个函数也会有自己的弊端,也就是在进行向左查询或者多条件查询的时候,需要搭配其他函数进行嵌套运用。今天我们就来学习一下向左数据查询中,index+match和lookup两类更加实用的函数。
一、案例演示:
案例说明:如上图所示,我们需要根据人员的工号,在数据区域中向左查询对应人员姓名。下面我们就通过vlookup+IF、Index+match、Lookup三类函数来进行一下实际操作,了解一下哪一类函数更好用。
函数一:vlookup+IF函数嵌套运用,利用工号向左查询数据
函数公式:
=VLOOKUP(F4,IF({1,0},$B$1:$B$8,$A$1:$A$9),2,0)
函数解析:
1、vlookup+IF函数进行向左查询时,主要利用了IF函数修改vlookup函数的第二参数,重新组成一个新的数据查询区域;
2、if{1,0}结果在这里,主要对1、0两个逻辑值进行判断,将B、A两列的内容重新组成如下的数据区域,结果如下图所示:
3、最后在利用vlookup函数在上面的数组结果中进行查询第二为的值,这样就能实现通过工号查询对应的姓名。
函数二:Index+Match函数简单高效的完成数据向左查询
函数公式:
=INDEX(A:A,MATCH(F4,B:B,0))
函数解析:
1、Index+Match函数逆向查询,主要利用了match函数查询出对应值的位置,从而返回Index函数引用内容的值;
2、match函数在这里主要为定位函数,MATCH(F4,B:B,0)=5,也就是查找到sz0004的工号在B列中的从上往下第五个值。所以最后index(A:A,5)就是返回A列数据中的第五个值。
函数三:lookup函数快速实现数据的逆向查询
函数公式:
=LOOKUP(1,0/($B$1:$B$8=F4),$A$1:$A$8)
函数解析:
1、lookup函数在逆向查询操作中,是唯一一个不用与其他函数搭配使用的一个函数。主要是通过向量查询的方式,查询到符合条件的值的位置,最终返回对应的数值。相比其他两个函数都会更加简单。
现在你学会如何利用不同的函数,高效快捷的进行数据的逆向查询了吗?
本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com
文章为作者独立观点不代本网立场,未经允许不得转载。