深度解读VLOOKUP四大参数以及最新用法
作者：逍遥来源：部落窝教育发布时间：2023-05-09 11:16:27点击：2407
编按：
从VLOOKUP的四个参数来深度解读VLOOKUP的使用规则和用法。包括最新用法：不用下拉填充公式即可完成所有查找值的查找。
一说起查找函数，大部分人都会想到VLOOKUP函数。
作为一个有34年历史的老牌函数，VLOOKUP也算是声名远扬，无愧 “查找之王”的美称。但在我看来，他既有光鲜的一面，也有一堆糗事，与他的笨拙和固执分不开。
怎么说？
今天就从VLOOKUP函数的4个参数来深度解读VLOOKUP，同时介绍最新用法：在第一和第三参数引用数据区域或者数组，不用下拉填充公式即可完成所有查找值的查找。
分别是：查找值，查找区域，返回列，匹配类型。
第一参数查找值：表里如一的坚守者；灵活变通和与时俱进者！
在这里我们可以看到VLOOKUP成为之所以成为明星函数的原因：坚守、灵活、与时俱进。
1.表里如一
表里必须如一，来不得半点虚假。这是VLOOKUP的笨拙之处也是最可贵的地方！
如下图，我们要查找编号为714848的销售金额，Excel却给我们返回了一个错误值，这是咋回事呢？
取消A列和G列居中对齐，G2单元格的数字靠左，而A9单元格的数字靠右，原来G2和A9属性不一样！
Vlookup函数有自己的规则，查找的时候严格遵守【表里如一】的第一定律：数据属性一致。
如果查找值是文本，那么查找区域里对应的值也应该是文本；
如果查找值是数字，那么查找区域里对应的值也应该是数字。
再往下查，懊恼继续，怎么又被Vlookup发了一个好人“NA”卡？
通过取消对齐，排除了数据属性不一致的原因。
考虑是数据违反了第一参数【表里如一】的第二定律：字符数相等！
查找值与查找区域中的比对值必须字符数相等。
表面看到的字符是一样的，但因为有空格或者不可见字符的存在，两者实际字符数可能不等，是不一样的。
用LEN函数检查字符数：
A2单元格的字符数=LEN(A2)=8，G2单元格的字符数=LEN(G2)=7。字符数不相等，两者肯定不相等啦，也就查不到了。
TIPS：
几个常见的Excel不可见字符，水平制表符char(9)、换行符char(10)、空格符char(32)。
在单元格输入公式=char(9)即可得到一个不可见的水平制表符，其虽然不可见，但字符数为1。
彩蛋：如何纠正Vlookup看着有却查找错误的问题？
对于数据属性不一样的：网上有很多方式可以在文本数字和数字之间转换，这里就不说了。
不可见字符造成字符数不一样的：用公式=CLEAN(TRIM(SUBSTITUTE(要清洗的某单元格,CHAR(32),)))可以清除空格和常见的不可见字符。清洗后再复制并选择性粘贴覆盖原来数据即可。
2.灵活变通——支持通配符查找和支持多条件连接查找
表里如一的坚守并不等于死板和不知变通！
相反，第一参数非常灵活。
如果查找值本身不完整，如是简称，我们可以通过添加通配符“*”或者“?”来进行包含查找，如下图，查找鞋，即为查找包含鞋字的：
VLOOKUP的第一参数还支持用&连接多个单元格的内容，用于多条件查询：
3.与时俱进——引用数据区域或数组进行查找
这是Vlookup最新用法！
在低版本的Excel中，Vlookup的第一参数通常是一个数据，但在最新的office365、office2021版本中，VLOOKUP函数的第一参数可以直接引用数据区域或数组。有了这项支持，不需要下拉填充公式即可完成所有查找值的查询。如下，直接在I2中输入公式，第一参数引用数据区域G2:G7，即可完成所有订单查找。
第二参数查找区域：圈地爱好者与固执的向右查找者！
1.圈地爱好者
要查找先圈地。把包含查找值和返回值所在的整片区域都圈起来作为自己的领地，然后只在领地里进行查找，其他地方恕不接待。
比如下图中的B到E列就是此次查找的领地（查找区域）。
这点与它的兄长Lookup迥然不同，Lookup可以分别指定查找区域和返回区域，而不需要把两者圈在一期。
2. 固执的向右查找者！
这块地从哪里开始圈起？往哪个方向圈？往哪个方向查？
VLOOKUP要求领地中左起的第一列必须是查找值所在列，然后向右圈地；圈地后，查找也是从领地左起的第一列开始往右查。
譬如：查找值为订单编号，那么我们就要以订单编号这一列为第一列，向右进行圈地，直到圈到要查询的结果值——销售金额这一列为止。然后在B列中查找订单“762145”，找到后水平往右走，直到返回列。
一个小问题，能向左进行圈地吗？
比如下图，我们需要根据订单编号来查找销售部门，我们可以选中B列再向左边A列开始圈吗？
答案是可以！但选择的区域如B1:A15在公式输入完毕后自动会更正为A1:B15。
那能否向左查找呢？
答案是不可以！
TIPS
如果必须从右往左查找，可以请来IF这个热心肠的函数将这两列内容颠倒一下顺序，即用IF({1,0}把B列和A列组合在一起，并把 B列放在A列前面。
TIPS：
如果有向右或者向下复制公式的需求，我们还需要给这块查找区域上个保险。
通俗一点就是说，不想你千辛万苦圈起来的地到处乱跑，最好给它修个栅栏。修栅栏，就得花钱对不对？
所以我们用这个$符号，将它放在你需要固定的数据前面，这样，拖拽公式的时候，查找区域就不会再变化了。
TIPS：
VLOOKUP的查找区域是只能圈一块地，还是可以圈多块地呢？
比如现在我们要找西红柿的产量、销量、利润，而这些数据分别放在三个Excel表里，又该如何写公式？
借助INDIRECT函数可以实现多表查询数据，如下图所示：
第三参数返回列：笨拙的数数者
1.默认数数
返回第几列呢？VLOOKUP是边走边数数来确定返回列数的。从领地的第一列开始，一步一列，走到返回列有几步，就写几列。
尤其是同样的条件需要查找多列返回值的时候，每次都去数一次然后手动修改，特麻烦。
2.找朋友相助自动给出返回数
在查找多列返回值时，为避免数来数去以及改来改去出错，那就只有求人相助了。
VLOOKUP：各位大哥大姐，小弟能力有限，实在是活不下去了。俗话说在家靠父母，出门靠朋友……
众函数觉得VLOOKUP不摆明星架子，还算真诚，于是纷纷效犬马之劳。
（1）COLUMN，搞定有序变化的返回列
如图所示，要找出A、B、D产品在1、2、3月的销量，如果单单是靠VLOOKUP的话，只能频繁地去修改第三参数，于是COLUMN雪中来送碳。
=VLOOKUP($A14,$A$1:$G$10,COLUMN(B2),FALSE)
（2）MATCH——自动识别返回列
如果不是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的第三参数用花括号括起来，2、 3 、 4，这三个数据分别对应着 “产品A” 在1 、2、3月份的销售数量。然后在外面套一个SUM函数就得到了A产品第一季度的销量。
数组公式，低版本（office365、2019、2021等以下）的，需按Ctrl+Shift+Enter三键结束。
第四参数匹配类型：失误的反人类设计
1.反人类设计
第4参数是个可选参数，用于设置匹配类型。匹配类型有近似匹配（TRUE或1）和精确匹配（FALSE或0）两种。
默认为近似匹配，可以省略不写，而精确匹配则须给出参数，这和我们日常主要查询需求——追求精确——截然不同。
如下图所示，我们要查找编号为“786029”的销售数量，保持默认不填写第4参数，那对不起，只能得到一个错误的答案。
这就意味着，每次输完前三个参数时，你都需要谨慎地写出第4参数（FALSE或0），或者你至少要在第三参数后加一个逗号，才能精确查找。
2.也并非一无是处——做区间查找很合适
默认近似匹配也并非一无是处，当我们做区间查找，如根据数据查等级时，就可以少写一个参数，很省事。
譬如查A列的销售等级，只用三个参数即可。
注：利用近似匹配做区间查找时，查找区域首列必须是升序排列。Vlookup近似匹配时，其查找方法与Lookup函数一样，都采用二分法进行。需要了解的可以看《一文讲透LOOKUP二分法原理》。
VLOOKUP作为查找明星，很多Excel人都认他、用他。我们从四个参数入手，去掉光环，看到他的本真，有坚守，有笨拙，有固执，有失误。同时，Excel高版本中Vlookup有最新用法，可以直接用数组做参数，不用下拉填充公式即可完成所有查找。
有了这些理解，相信大家再使用VLOOKUP可以提前规避很多错误并提高效率。
本文配套的练习课件请加入QQ群：902294808下载。
做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！
扫下方二维码关注公众号，可随时随地学习Excel：
相关推荐：
版权申明：
本文作者逍遥；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。