二维码 购物车
部落窝在线教育欢迎您!

深度解读VLOOKUP四大参数以及最新用法

 

作者:逍遥来源:部落窝教育发布时间:2023-05-09 11:16:27点击:1711

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

VLOOKUP的四个参数来深度解读VLOOKUP的使用规则和用法。包括最新用法:不用下拉填充公式即可完成所有查找值的查找。

 

一说起查找函数,大部分人都会想到VLOOKUP函数。

作为一个有34年历史的老牌函数,VLOOKUP也算是声名远扬,无愧 “查找之王”的美称。但在我看来,他既有光鲜的一面,也有一堆糗事,与他的笨拙和固执分不开。

怎么说?

今天就从VLOOKUP函数的4个参数来深度解读VLOOKUP,同时介绍最新用法:在第一和第三参数引用数据区域或者数组,不用下拉填充公式即可完成所有查找值的查找。

分别是:查找值,查找区域,返回列,匹配类型。

 

第一参数查找值:表里如一的坚守者;灵活变通和与时俱进者!

在这里我们可以看到VLOOKUP成为之所以成为明星函数的原因:坚守、灵活、与时俱进。

1.表里如一

表里必须如一,来不得半点虚假。这是VLOOKUP的笨拙之处也是最可贵的地方!


如下图,我们要查找编号为714848的销售金额,Excel却给我们返回了一个错误值,这是咋回事呢?

 

图形用户界面, 应用程序, 表格, Excel
描述已自动生成

 

取消A列和G列居中对齐,G2单元格的数字靠左,而A9单元格的数字靠右,原来G2A9属性不一样!

图形用户界面, 应用程序, 表格
描述已自动生成

Vlookup函数有自己的规则,查找的时候严格遵守【表里如一】的第一定律:数据属性一致。

如果查找值是文本,那么查找区域里对应的值也应该是文本;

如果查找值是数字,那么查找区域里对应的值也应该是数字。

 

再往下查,懊恼继续,怎么又被Vlookup发了一个好人“NA”卡?

 

图形用户界面, 应用程序, 表格, Excel
描述已自动生成

通过取消对齐,排除了数据属性不一致的原因。

考虑是数据违反了第一参数【表里如一】的第二定律:字符数相等!

查找值与查找区域中的比对值必须字符数相等。

表面看到的字符是一样的,但因为有空格或者不可见字符的存在,两者实际字符数可能不等,是不一样的。

LEN函数检查字符数:

A2单元格的字符数=LEN(A2)=8G2单元格的字符数=LEN(G2)=7。字符数不相等,两者肯定不相等啦,也就查不到了。

 

图形用户界面, 应用程序, 表格, Excel
描述已自动生成

 

TIPS

几个常见的Excel不可见字符,水平制表符char(9)、换行符char(10)、空格符char(32)

在单元格输入公式=char(9)即可得到一个不可见的水平制表符,其虽然不可见,但字符数为1

 

彩蛋:如何纠正Vlookup看着有却查找错误的问题?

对于数据属性不一样的:网上有很多方式可以在文本数字和数字之间转换,这里就不说了。

不可见字符造成字符数不一样的:用公式=CLEAN(TRIM(SUBSTITUTE(要清洗的某单元格,CHAR(32),)))可以清除空格和常见的不可见字符。清洗后再复制并选择性粘贴覆盖原来数据即可。

2.灵活变通——支持通配符查找和支持多条件连接查找

表里如一的坚守并不等于死板和不知变通!

相反,第一参数非常灵活。

如果查找值本身不完整,如是简称,我们可以通过添加通配符“*”或者“?”来进行包含查找,如下图,查找鞋,即为查找包含鞋字的:

 

图形用户界面描述已自动生成

 

VLOOKUP的第一参数还支持用&连接多个单元格的内容,用于多条件查询:

表格
描述已自动生成

 

3.与时俱进——引用数据区域或数组进行查找

这是Vlookup最新用法!
在低版本的Excel中,Vlookup的第一参数通常是一个数据,但在最新的office365office2021版本中,VLOOKUP函数的第一参数可以直接引用数据区域或数组。有了这项支持,不需要下拉填充公式即可完成所有查找值的查询。如下,直接在I2中输入公式,第一参数引用数据区域G2:G7,即可完成所有订单查找。

 

 

 

第二参数查找区域:圈地爱好者与固执的向右查找者!

1.圈地爱好者

要查找先圈地。把包含查找值和返回值所在的整片区域都圈起来作为自己的领地,然后只在领地里进行查找,其他地方恕不接待。

比如下图中的BE列就是此次查找的领地(查找区域)。

 

这点与它的兄长Lookup迥然不同,Lookup可以分别指定查找区域和返回区域,而不需要把两者圈在一期。

 

2. 固执的向右查找者!

这块地从哪里开始圈起?往哪个方向圈?往哪个方向查?

VLOOKUP要求领地中左起的第一列必须是查找值所在列,然后向右圈地;圈地后,查找也是从领地左起的第一列开始往右查。

譬如:查找值为订单编号,那么我们就要以订单编号这一列为第一列,向右进行圈地,直到圈到要查询的结果值——销售金额这一列为止。然后在B列中查找订单“762145”,找到后水平往右走,直到返回列。

图形用户界面, 应用程序, 表格, Excel
描述已自动生成

 

一个小问题,能向左进行圈地吗?

比如下图,我们需要根据订单编号来查找销售部门,我们可以选中B列再向左边A列开始圈吗?

答案是可以!但选择的区域如B1:A15在公式输入完毕后自动会更正为A1:B15

那能否向左查找呢?

答案是不可以!

图形用户界面, 应用程序, 表格, Excel
描述已自动生成

TIPS

如果必须从右往左查找,可以请来IF这个热心肠的函数将这两列内容颠倒一下顺序,即用IF({1,0}B列和A列组合在一起,并把 B列放在A列前面。

 

图形用户界面, 应用程序, 表格, Excel
描述已自动生成

TIPS

如果有向右或者向下复制公式的需求,我们还需要给这块查找区域上个保险。

通俗一点就是说,不想你千辛万苦圈起来的地到处乱跑,最好给它修个栅栏。修栅栏,就得花钱对不对?

所以我们用这个$符号,将它放在你需要固定的数据前面,这样,拖拽公式的时候,查找区域就不会再变化了。

 

TIPS

VLOOKUP的查找区域是只能圈一块地,还是可以圈多块地呢?

比如现在我们要找西红柿的产量、销量、利润,而这些数据分别放在三个Excel表里,又该如何写公式?

借助INDIRECT函数可以实现多表查询数据,如下图所示:

图形用户界面, 应用程序, 表格, Excel
描述已自动生成

 

第三参数返回列:笨拙的数数者

1.默认数数

返回第几列呢?VLOOKUP是边走边数数来确定返回列数的。从领地的第一列开始,一步一列,走到返回列有几步,就写几列。

 

 

尤其是同样的条件需要查找多列返回值的时候,每次都去数一次然后手动修改,特麻烦。

2.找朋友相助自动给出返回数

在查找多列返回值时,为避免数来数去以及改来改去出错,那就只有求人相助了。

VLOOKUP:各位大哥大姐,小弟能力有限,实在是活不下去了。俗话说在家靠父母,出门靠朋友……

众函数觉得VLOOKUP不摆明星架子,还算真诚,于是纷纷效犬马之劳。

1COLUMN,搞定有序变化的返回列

如图所示,要找出ABD产品在123月的销量,如果单单是靠VLOOKUP的话,只能频繁地去修改第三参数,于是COLUMN雪中来送碳。

=VLOOKUP($A14,$A$1:$G$10,COLUMN(B2),FALSE)

 

2MATCH——自动识别返回列

如果不是1月、2月、3月这样的有序排列,而是1月、3月、5月的序列,还有MATCH函数绝渡逢舟。

=VLOOKUP($A14,$A$2:$G$10,MATCH(B$13,$A$1:$G$1,0),FALSE)

 

 

3.支持数组一次返回多个值

这也是VLOOKUP的最新用法!
在低版本中,虽然Vlookup
的第三参数可以输入数组,但需要提前选中多个单元格后再来输入公式并三键回车。现在不一样,直接选中第一个单元格输入数组,直接回车,结果会返回一组数。如下图求不同产品一二季度的销量。

Vlookup的第三参数用花括号括起来,23 4,这三个数据分别对应着产品A” 1 23月份的销售数量。然后在外面套一个SUM函数就得到了A产品第一季度的销量。

 

图形用户界面, 表格, Excel
描述已自动生成

 

数组公式,低版本(office36520192021等以下)的,需按Ctrl+Shift+Enter三键结束。

 

第四参数匹配类型:失误的反人类设计

1.反人类设计

4参数是个可选参数,用于设置匹配类型。匹配类型有近似匹配(TRUE1)和精确匹配(FALSE0)两种。

默认为近似匹配,可以省略不写,而精确匹配则须给出参数,这和我们日常主要查询需求——追求精确——截然不同。

 

如下图所示,我们要查找编号为“786029”的销售数量,保持默认不填写第4参数,那对不起,只能得到一个错误的答案。

 

 

这就意味着,每次输完前三个参数时,你都需要谨慎地写出第4参数(FALSE0),或者你至少要在第三参数后加一个逗号,才能精确查找。

 

2.也并非一无是处——做区间查找很合适

默认近似匹配也并非一无是处,当我们做区间查找,如根据数据查等级时,就可以少写一个参数,很省事。

譬如查A列的销售等级,只用三个参数即可。

 

 

注:利用近似匹配做区间查找时,查找区域首列必须是升序排列。Vlookup近似匹配时,其查找方法与Lookup函数一样,都采用二分法进行。需要了解的可以看《一文讲透LOOKUP二分法原理》

 

VLOOKUP作为查找明星,很多Excel人都认他、用他。我们从四个参数入手,去掉光环,看到他的本真,有坚守,有笨拙,有固执,有失误。同时,Excel高版本中Vlookup有最新用法,可以直接用数组做参数,不用下拉填充公式即可完成所有查找。

有了这些理解,相信大家再使用VLOOKUP可以提前规避很多错误并提高效率。

 

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

如何提取品牌信息?LOOKUP函数有绝招!

如何在交叉查询中使用VLOOKUP?看完就懂!

Excel教程:如何制作带有层次和透视感的图表?

八大查找函数公式,轻松搞定数据中的多条件查找

版权申明:

本文作者逍遥;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。