VLOOKUP函数最全面最详细的讲解大全，涵盖17个重要和常见用法！

Excel教程 2024-03-01

Excel秘籍大全，前言

在查询界新宠XLOOKUP没出来前，VLOOKUP可以说是Excel中使用频率最高的查询函数，而得心应手的应用查询函数鼻祖LOOKUP则需要更多的Excel功底。今天，小包老师来给大家详细全面的讲解VLOOKUP函数！

先来带大家回顾一下VLOOKUP函数的基础语法：=VLOOKUP（查找值，查找区域，列序数，[匹配方式]）,第三参数列序数为函数返回值在查找区域中的列数，第四参数匹配方式用0或FALSE表示精确匹配，用1或TRUE表示近似匹配，第四参数可省略，省略时默认为精确匹配，通常情况下，我们默认都使用精确匹配。

Excel秘籍大全，正文开始

1.单条件顺向查找

VLOOKUP函数应用于单条件顺向查找，如图1所示，根据姓名查找员工对应的工资，其公式为：=VLOOKUP(H2,A1:F8,6,)或=VLOOKUP(H2,A1:F8,6,FALSE)或=VLOOKUP(H2,A1:F8,6,0)。

第四参数省略时，逗号不可省略，否则会出错，0和FALSE都表示精确匹配。

图1

2.单条件反向查找

VLOOKUP函数不能直接应用于反向查找，需要借助于IF函数。如图2所示，根据薪资查询对应的员工姓名，其公式为：=VLOOKUP(H2,IF({1,0},F1:F8,A1:A8),2,0)。

IF函数配合数组{1,0}，当{1,0}为1时，IF函数返回第二参数即F1:F8，当{1,0}为0时，返回IF函数的第三参数即A1:A8，这样就从空间上构建了顺向连接的数据区域即F1:F8和A1:A8，从而满足VLOOKUP函数只能顺向查询的特性。

图2

3.多条件顺向查找

VLOOKUP函数应用于多条件查找，需要配合IF函数，当然也可以用过添加辅助列的形式。如图3所示，根据“李四”和“女”这两个条件查找对应的薪资，其公式为：=VLOOKUP(H2&I2,IF({1,0},A1:A8&C1:C8,F1:F8),2,0)，然后按下数组三键Ctrl+Shift+Enter。

图3

涉及到多条件查找，第一参数查找值我们要用&连接符来连接条件，第二参数查找区域我们要借用IF函数来构建，当{1,0}为1时，IF函数返回第二参数即A1:A8&C1:C8，A1:A8&C1:C8构成数组{"姓名性别";"张三男";"李四女";"王五男";"赵六女";"李四男";"孙八女";"诸葛亮男"}，当{1,0}为0时，IF函数返回第三参数即F1:F8，F1:F8构成数组{"薪资";5500;7600;8000;7300;5550;5000;8500}，IF函数最终返回的结果即为数组{"姓名性别","薪资";"张三男",5500;"李四女",7600;"王五男",8000;"赵六女",7300;"李四男",5550;"孙八女",5000;"诸葛亮男",8500}，这个数组为两列，第二列对应的是薪资，查找值H2&I2即"李四女"在这个数组的第一列，因此VLOOKUP函数的第三参数返回值所在列数我们要用2。

4.多条件反向查找

VLOOKUP函数多条件反向查询，同样要借用IF函数构建顺向的多条件区域，如图4所示，其公式为：=VLOOKUP(H2&I2,IF({1,0},F1:F8&C1:C8,A1:A8),2,0)，然后按下数组三键Ctrl+Shift+Enter。

图4

原理与上述一致，无非是IF函数构建了一个空间上顺向的数据区域{"薪资性别","姓名";"5500男","张三";"7600女","李四";"8000男","王五";"7300女","赵六";"5550男","李四";"5000女","孙八";"8500男","诸葛亮"}，查找值H2&I2即"5550男"，对应IF数组的第一列，返回值姓名所在第二列。

5.简称查找

根据简称查找目标数据，需要结合通配符*，星号*表示匹配任意多个字符，根据“葛亮”查询对应的薪资，如图5所示，其公式为：=VLOOKUP("*"&H2,A1:F8,6,0)，根据“葛”查询对应的薪资，其公式为：=VLOOKUP("*"&H5&"*",A1:F8,6,0)。

图5

星号*要用英文状态下的双引号引用起来，并使用&连接单元格，如果简称在字符串的中间位置，则需要前后都添加星号*。

6.多项目连续查找

如图6所示，根据姓名分别查找对应的“年龄”“性别”“工龄”等信息，涉及到多项目查找，需要配合COLUMN函数，点击B11单元格，输入公式：=VLOOKUP($A11,$A$1:$F$8,COLUMN(B$1),0)，并向右和向下填充。

图6

这里大家需要注意混合引用和绝对引用，关于绝对引用和混合引用的讲解，不理解的的可以关注小包老师点击观看Excel视频系列教程。COLUMN返回的是单元格对应的列数，例如COLUMN（A3），A3在第一列，那么就返回1，COLUMN（C10），单元格C10在第三列，那么返回的值就是3。COLUMN(B$1)使用混合引用，这样公式在左右和上下拖动填充时，行数会保持不变，列数由B→C→D→E等移动，得到连续的列序数。

7.多项目非连续查找

如图7所示，返回值字段标题与源数据字段标题并非一一对应，这种非连续多项目查找，该如何操作呢？这种情况我们就无法再使用COLUMN函数了，但可以使用MACTH函数，点击B11，输入公式：=VLOOKUP($A11,$A$1:$F$8,MATCH(B$10,$A$1:$F$1,0),0)。

图7

使用MATCH函数判断字段标题在源数据标题中对应的列数，MATCH(B$10,$A$1:$F$1,0)返回3，即“性别”在$A$1:$F$1区域中处于第三列，以此对应到VLOOKUP函数的第三参数列序数。当公式向右拖动填充时，分别查找“部门”“工龄”“薪资”等字段在源数据标题字段中对应的列序数。

8.一对多查找

VLOOKUP函数也可应用于一对多查找，与上文所述的多项目查找不同，当查找值存在多个，我们需要把多个雷同的查找值对应的返回值都查找到。如图8所示，源数据中有3个“李四”，现在要求我们讲3个李四的信息全部查找出来，这种情况该如何操作呢？我们需要构建辅助列，在原A列前面插入一列，标题名为“辅助列”，在A2输入公式：=B2&COUNTIF(B$2:B2,B2)，并向下填充，如果存在重复的话，就能得到增长的姓名序号，本案例为李四1，李四2，李四3。

图8

点击C12,输入公式：=IFERROR(VLOOKUP($B$12&ROW($A1),$A$1:$G$8,MATCH(C$11,$A$1:$G$1,0),0),"")，并向右和向下填充。本案例的难点在于构建动态的查找值，即怎么将“李四”更改为“李四1”“李四2”“李四3”，这里我们需要借助ROW函数达到生成1,2,3……的作用。当公式向下填充时，,ROW($A1）返回1，ROW($A2）返回2，ROW($A3）返回3，以此类推，这样我们就得到了“李四1”“李四2”“李四3”“李四4”“李四5”……的查找值，“李四4”“李四5”辅助列中不存在，当查找值不存在，VLOOKUP会返回#N/A错误值，我们借助IFERROR函数将错误值替换为空值。

9.星号*非通配符查询

如图9所示，要查询产品规格30*30对应的销量，其公式为：=VLOOKUP(D20,A19:B23,2,0)，返回的结果为50而并不是45，为什么会出现这种情况呢？因为30*30里面的星号*，Excel会默认为通配符，第一个产品30*45*30就相当于30*30，所以返回的结果是30*45*30对于的销量，那么如何解决这种问题呢？我们需要利用字符替换SUBSTITUTE函数将*替换为*本身，让它失去通配符的意义。如图10所示，正确的公式为：=VLOOKUP(SUBSTITUTE(D20,"*","~*"),A19:B23,2,0)，将"*"替换为波浪号（波形符~）加星号的形式"~*"，就能将星号*去除通配符的意义。

图9

图10

10.查找值出现波形符~

当查找值出现波形符~，Excel会忽略波形符~，如图11所示，我们根据“星~晨”查找对应的天数，返回值是48，而并非25，这里我们同样要使用SUBSTITUTE函数将“~”替换为波形符本身，正确的公式为：=VLOOKUP(SUBSTITUTE(D26,"~","~~"),A25:B29,2,0)，将"~"替换为"~~"，那么查找值中的“~”才会有意义。

图11

11.取消合并项并填充数据

如图12所示，取消合并项并填充，在B34单元格输入公式：=VLOOKUP(9E+307,A$34:A34,1,TRUE)，并向下填充。9E+307是Excel中的最大数值，当查找值不存在且第四参数我们设置为TRUE也就是近似匹配时，VLOOKUP会返回比9E+307小且排名最末的数值。A$34:A34混合引用构建动态的数据区域，会匹配到这个动态区域中的最后一个数值。

图12

12.查找一列中最后一个数值和文本

如图13所示，查找A列中最后一个数值，公式为：=VLOOKUP(9E+307,A15:A26,1,TRUE)，查找A列中最后一个文本，公式为：=VLOOKUP("座",A15:A26,1,TRUE)。"座"为Excel中的最大字符编码，当"座"为第一参数且函数为近似匹配时，会配到数据区域中的最后一个文本。

图13

13.合并项查找

如图14所示，根据姓名查找对应的部门，其公式为：=VLOOKUP("座",INDIRECT("A1:A"&MATCH(D2,B1:B9,0)),1,TRUE),用INDIRECT构建动态的数据区域，第一参数为"座"，会匹配到这个动态区域中最后一个文本字符。

图14

14.合并项查找高阶用法

如图15所示，查询产品对应的单价，D13输入公式为：=VLOOKUP(VLOOKUP("座",A$13:A13,1,TRUE),$F$12:$G$15,2,0)，并向下填充。VLOOKUP函数第一参数用VLOOKUP指代，VLOOKUP("座",A$13:A13,1,TRUE)会返回动态区域A$13:A13中的最后一个文本，然后利用这个文本进行再一次的查找。