打开APP
userphoto
未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
查询引用,你喜欢用Lookup、Vlookup还是Index Match组合?

     查询引用,常用的函数有Lookup、Vlookup或Index+Match组合,你最喜欢用那个了?


一、Lookup函数。

Lookup函数具有两种使用形式。

1、向量形式。

功能:是指在单行或单列中查询指定的值,然后返回第二个单行或单列相同位置的值。

语法结构:=Lookup(查询值,查询值所在的范围,[返回值所在的范围])。

前提条件:查询值所在范围的值,必须按照升序排序,否则无法得到正确的结果。

目的:查询销售员的销量。

方法:

1、以“销售员”为主要关键字进行升序排序。

2、在目标单元格中输入公式:=LOOKUP(H3,B3:B9,D3:D9)。

解读:

1、由于Lookup函数本身的特点,在查询一用之前,必须对查询范围的值进行升序排序,否则无法得到准确的查询结果。

2、当查询范围和返回返回相同时,返回范围可以省略哦!


2、数组形式。

作用:在对应的数据源中的第一列或第一行中查找值,并返回最后一列或最后一行对应的值。

语法结构:=Lookup(查询值,查询值和返回值所在的范围)。

前提条件:查询值所在范围的值,必须按照升序排序,否则无法得到正确的结果。

目的:查询销售员的销量。

方法:

1、以“销售员”为关键字进行升序排序。

2、在目标单元格中输入公式:=LOOKUP(H3,B3:D9)。

解读:

1、由于Lookup函数本身的特点,在查询一用之前,必须对查询范围的值进行升序排序,否则无法得到准确的查询结果。

2、使用数组形式时,查询值范围必须在第一列,返回值范围必须在最后一列哦!


3、单条件查询。

在实际工作中,数据源中的数据不可能按照升序有序排列,难倒就不能用Lookup函数查询引用了吗……为了解决这一难题,Lookup衍生出了“变异用法”。

目的:查询销售员的销量。

方法:

在目标单元格中输入公式:=LOOKUP(1,0/(B3:B9=H3),D3:D9)。

解读:

1、在学校Lookup函数的基础语法时,已经强调过,要向得到正确的查询结果,必须对查询值所在范围的值进行升序排序,但在“单条件”查询时,并未对查询值所在范围的值进行升序排序,而是采用了奇诡的公式,查询值为1,查询范围为0/(B3:B9=H3),Why?

2、Lookup函数的特点:当在查询范围中找不到需要查询的值时,Lookup函数就会进行匹配,原则是用小于查询值的最大值进行替代查询值。

3、当B3:B9=H3成立时,返回True,暨1,不成立时返回False,暨0,而0/0则返回错误,所已查询范围就变成了一个由0和1组成的新数组,Lookup进行向下最大值匹配,从而返回0对应位置上的值。


4、多条件查询。

目的:查询销售员在相应地区的销量。

方法:

在目标单元格中输入公式:=IFERROR(LOOKUP(1,0/((B3:B9=H3)*(E3:E9=I3)),D3:D9),'无销量')。

解读:

多条件查询和单条件查询的原理是相同的,当两个或多个条件都成立时,返回True,暨1,否则返回False,暨0。


二、Vlookup函数。

作用:在指定的范围内,查找符合条件的值。

语法:=Vlookup(查询值,查询值和返回值所在的范围,返回值所在的列数,匹配模式)。其中匹配模式有0和1两种,“0”为精准查询,“1”为模糊查询。

1、单条件查询。

目的:查询销售员的销量。

方法:

在目标单元格汇总输入公式:=VLOOKUP(H3,B3:D9,3,0)。

解读:

H3为查询值所在单元格,B3:D9为查询值和返回值所在范围;因为要返回的是销量,而在B3:D9范围中,销量在D列,也就是第3列,所以第三个参数为3;第四个参数为“0”代表精准查询。


2、多条件查询

目的:查询销售员在相应地区的销量。

方法:

在目标单元格中输入公式:=IFERROR(VLOOKUP(H3&I3,IF({1,0},B3:B9&E3:E9,D3:D9),2,0),'无销量')。

解读:

公式中IF({1,0},B3:B9&E3:E9,D3:D9)的作用就是在后台重新形成新的数据范围,暨H列和I列形成新的一列。


3、多列查询。

目的:返回销售员的所有信息。

方法:

在目标单元格中输入公式:=VLOOKUP($B13,$B$3:$E$9,COLUMN(C3)-1,0)。

解读:

多列查询的关键在于返回列的相对位置,而Column函数正好可以返回当前单元格所在的列数,如果在加以修正,从而达到我们的目的。


4、从右向左查询。

目的:根据销量查询销售员。

方法:

在目标单元格中输入公式:=VLOOKUP(H3,IF({1,0},D3:D9,B3:B9),2,0)。

解读:

公式汇总IF({1,0},D3:D9,B3:B9)的作用为调换B类和D列的位置,形成新的数据范围。


5、划分区间等级。

目的:对销量划分等级。

方法:

在目标单元格中输入公式:=VLOOKUP(D3,$H$3:$I$7,2,1)。

解读:

Vlookup函数的特点是:当在查询范围中找不到对应的值时,自行向下匹配,返回小于当前值的最大值所对应的结果。此时必须使用模糊查询哦,暨第四个参数为1。


三、Index+Match组合函数。

1、单列查询。

目的:查询销售员对应的销量。

方法:

在目标单元格中输入公式:=INDEX(D3:D9,MATCH(H3,B3:B9,0))。

解读:

1、Index函数的作用为:返回给定的单元格区域中,行列交叉处的值或引用。语法结构:=Index(区域,行,[列])。当省略“列”时,默认为第1列。

2、Match函数的作用是:返回指定值在指定范围内的相对位置。语法结构:=Match(定位置,定位范围,匹配模式)。其中“1”为:小于;“0”为精准;“-1”为:大于。


2、多列查询。

目的:返回销售员对应的所有信息。

方法:

在目标单元格中输入信息:=INDEX($C$3:$E$9,MATCH($B$13,$B$3:$B$9,0),MATCH(C$12,$C$2:$E$2,0))。

解读:

利用Match函数定位目标值所在的行和列,然后用Index提取。


结束语:

本文主要介绍了3种常见的查询技巧,Lookup函数、Vlookup函数、Index+Match函数。对于其实用技巧,你Get到了吗?

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP,阅读全文并永久保存 查看更多类似文章
猜你喜欢
类似文章
【热】打开小程序,算一算2024你的财运
查询函数Choose、Lookup、Hlookup、Vlookup应用技巧解读
Excel解析lookup的经典查找方式
Excel函数公式:查找引用之王LOOKUP。
LOOKUP万能函数18大用法,与VLOOKUP,IF十几种函数组合无敌,必收藏
Excel函数公式:万能查找函数Lookup函数的神应用和技巧
lookup函数的使用方法,含向量和数组形式实例及与vlookup的区别
更多类似文章 >>
生活服务
热点新闻
分享 收藏 导长图 关注 下载文章
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!

联系客服