VLOOKUP函数高难度实战用法

VLOOKUP函数是查找函数，常用于从大量数据或信息中查找指定的值。是Excel函数中使用频率最高的函数之一，很多用户用自身的亲身经历见证了Vlookup函数在提高工作效率的神奇；以致于大家纷纷奔走相告，使得Vlookup函数几乎成了Excel函数的代名词。 

那我们一起来看看Vlookup函数有哪些实战应用技巧！

（分起步、进阶、高级三部分） 

基础语法:

 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 

通俗理解：

 VLOOKUP(查找目标,查找范围,返回值的列数,精确查找OR模糊查找) 

VLOOKUP起步应用：

下面以一个实例来介绍这四个参数的用法。 

如下图所示，根据表2中的物料编号，查找物料编号对应的价格。

简要解析: 

查找目标：要找谁，在此我们需要查找的内容是B16；

查找范围：在哪找， 一般在一个区域中找，这个区域要同时满足2个条件: 

第一: 查找目标一定要位于区域中第1列，本例中物料编号在表1中是第二列（B列），所以查找区域从第二列开始，即B列，而不是A列。 

第二: 该区域一定要包含要返回值所在的列，本例中要返回的是价格，所以查找区域中要包含E列，在实际使用时价格所在列一般是最后一列。 

综合所述，查找范围是$B$3:$E$12，不是$A$3:$E$12。 

返回值的列数：往哪找，本例中要返回的是价格，价格在查找范围$B$3:$E$12中从左往右数是第4列，而不是工作表中的第5列。 

精确查找OR模糊查找： 怎么找，0或FALSE代表精确查找，1 或TRUE代表模糊查找，一般使用精确查找的比较多。

VLOOKUP进阶应用：

我们仍以表1作为数据源，根据编号来查找名称、供应商和价格

     场景1：要查找的名称、供应商和价格顺序与表1标题顺序相同时

公式依次如下: 

名 称: =VLOOKUP(B21,$B$3:$E$12,2,0) 

供应商:=VLOOKUP(B21,$B$3:$E$12,3,0) 

价 格: =VLOOKUP(B21,$B$3:$E$12,4,0) 

上述三个公式除第3个参数”返回值的列数”不同外，其它均相同，这样一个个修改起来比较麻烦，那能不能让公式往后复制时第3个参数能自动变为2、3、4、……呢？ 

此时，我们需要用到一个COLUMN函数，它可以返回指定单元格所在的列数，

比如: 

=COLUMN（B1） 返回值2 

=COLUMN（C1） 返回值3=COLUMN（D1） 返回值4 

所以C21单元格中完整的公式是: 

=VLOOKUP($B21,$B$3:$E$12,COLUMN(B1),0) 再向后复制即可

       场景2：要查找的名称、供应商和价格顺序与表1标题顺序不同时

再用COLUMN就不适合了，COLUMN只能按顺序生成序列号；

这时需要用到另一个MATCH函数，它可以算出名称、供应商和价格在表1中所在的位置，并返回正确的值。

MATCH基础语法:

MATCH(lookup_value, lookup_array, [match_type])

MATCH通俗理解: 

MATCH(查找目标, 查找范围, 精确查找OR模糊查找), 如:

（MATCH基本语法与VLOOKUP类似，在此就不作重点讲解）

所以C21单元格中完整的公式是:

=VLOOKUP($B21,$B$3:$E$12,MATCH(C20,$B$2:$E$2,0),0) 再向后复制即可

上述由VLOOKUP+MATCH构成的嵌套函数对于刚接触函数的人来说比较难，也容易写错，可以用以下的方法来写。

VLOOKUP高级应用：

      场景1:模糊查找，与通配符【*】和连接符【&】的联合应用

如下图所示，根据表2中的单位简称，在表1中找到对应单位的销售金额。

上述案例中在E3单元格中输入:

=VLOOKUP(E3,$A$3:$B$10,2,0)会返回错误值#N/A.

这是因为查找目标＂单位简称＂与＂单位全称＂对应的单位是不同的（如长沙丽景与长沙丽景汽车销售有限公司虽然是单位简称与全称的区别，但对于Excel来说，是不同的文本）。

在此情况下，我们需要用到通配符【*】和连接符【&】

在E3中输入:

=VLOOKUP('*'&E3&'*',$A$3:$B$10,2,0),往下复制。

解释:【*】作为文本对待，文本与文本之间是不能直接连接的，需要通过连接符【&】实现。

另【*】代表多个字符，【?】代表单个字符。

本例中单位简称与全称相比，前后字符数不同，所以用的是【*】。

       场景2：指定一个数就可以查找出它落在哪个区间并返回这个区间所对应的值。

如下图所示，表1是不同任务完成率区间对应不同奖励点数，现需要把表2中不同单位的奖励点数算出来。先构建一个辅助区域，如表3

在F3中输入: =VLOOKUP(E3,$A$11:$B$15,2) 向下复制即可。

这在本案例中VLOOKUP第四个参数如果是1或TRUE或省略，则实现区间查找正是第4个参数的模糊查找应用。

模糊查找有两个非常重要的原则：

第一：引用的数字区域一定要从小到大排序。杂乱的数字是无法准确查找到的。如下面A列符合模糊查找的前题，B列则不符合。

第二：给定一个数，它会找到和它最接近，但比它小的那个数。

上案中，89.9%非常接近90%，但取比90%小的那个数，所以奖励点数是4%，而不是5%。

 场景3：反向查找，从右往左查找。

正常情况下，VLOOKUP函数只能从左往右查找，要实现从右往左查找，需要通过一定的方法重新构建第二个参数“查找范围”的区域。

下面通过案例演示:

在C14中输入公式:

=VLOOKUP(B14,CHOOSE({2,1},$B$2:$B$11,$C$2:$C$11),2,0)

公式简述：

本案例中CHOOSE({2,1},$B$2:$B$11,$C$2:$C$11)是最关键的组成部分。

CHOOSE中使用了数组，返回的结果也会是一个数组，在此用2和1把CHOOSE中的区域1（$B$2:$B$11）,区域2（$C$2:$C$11）进行了位置交换；最终形成了{'457桥组合螺母','31030704E';'车架总成','TU3421WW280';……}这样一个区域。

错误值处理：

当查找目标在查找范围中找不到的时候，就会出现#N/A值，在实际应用中会影响报表的可读性，我们一般可以把错误值转换成0或空值。 

在Excel2007版本（含）以上，可使用IFERROR函数: 

= IFERROR（VLOOKUP（参数略），'') 

在Excel2003版本，需要用到IF和ISERROR函数，： 

=IF(ISERROR(VLOOKUP（参数略）),'',VLOOKUP（参数略）

微信名：Lucifer的办公室

微信名：luciferoffice