轻松工作之用好Vlookup函数

大家好，我是永不止步的老牛

今天我们要分享的是Vlookup函数的用法。

Vlookup函数的语法是：

Vlookup(lookup_value,table_array,col_index_num,[range_lookup])，其中

• lookup_value：表示要查找的值，可以是数值、文本或引用，必须是要查找表格区域(table_array)中的第一列
• table_array：表示要查找的表格区域
• col_index_num：表示要查找的值(lookup_value)在表格区域(table_array)中的第几列，必须是正整数，包含隐藏列
• range_lookup：表示查找方式，模糊匹配还是精确匹配

True或1代表模糊匹配，可以省略，这里注意一下，模糊匹配时需要对lookup_value对应列，即table_array的第一列进行升序排序，结果返回的是小于lookup_value的最大数值

False或0代表精准匹配，可以省略，不需要加逗号

什么意思呢？就是说你可以通过这个函数在一个表格区域(table_array)内查找一个值(lookup_value)，返回该值所在行的第col_index_num列的数据。

如果是精准匹配(range_lookup等于False或0)，则在table_array内查找等于lookup_value的数据，找到后返回table_array中的第col_index_num列数据，如果没找到，返回#N/A，如果有多个等于lookup_value的数据，只返回第一个。

如果是模糊匹配(range_lookup等于true或1)，则在table_array内从第一个数据开始匹配，没有匹配到一样的值就继续与下一个值进行匹配，直到遇到大于查找值的值，此时返回上一个数据对应table_array中的第col_index_num列数据，如果没找到，返回#N/A。

我们用一个例子来描述：我们模拟了一班学生的两个考试成绩表格

1.基础用法

在表一中，根据G12的学生姓名查找期末成绩，放到H12中。

我们只要在H12中输入公式：=VLOOKUP(G12,B5:E12,3,FALSE)

公式的含义是：在B5:E12区域内，在B列中查找等于(第4个参数是False，所以是等于)G12的数据，找到后，返回D列(第3个参数是3，所以是D列)对应的数据。

2.多个条件组合查找

在表二中，根据G18的学生姓名和H18的考试场次查找历史成绩，放到I18中。

我们只要在I18中输入公式：

=VLOOKUP(G18&H18,IF({1,0},B18:B25&C18:C25,D18:D25),2,FALSE)

然后同时按Ctrl+Shift+Enter结束，关于IF函数的用法，请参考我的另一篇文章“如何成为Excel大神—IF函数”。

公式的含义是：在IF({1,0},B18:B25&C18:C25,D18:D25)返回的区域内，在第1列中查找等于(第4个参数是False，所以是等于)G18&H18的数据，找到后，返回D列(第3个参数是2，所以是D列)对应的数据。

3.动态返回列数据(第3个参数动态获取)

在表一中，根据G12的学生姓名查找期中、期末及平均成绩，放到H12:J12中。

我们只要在H12中输入公式：

=VLOOKUP($G12,$B$5:$E$12,MATCH(H$11,$B$4:$E$4,0),FALSE)

右拉到J12列填充公式，公式中的$表示绝对引用。

公式的含义是：在B5:E12区域内，在B列中查找等于(第4个参数是False，所以是等于)G12的数据，找到后，返回MATCH(H$11,$B$4:$E$4,0)返回的值对应列的数据。

MATCH(H$11,$B$4:$E$4,0)：在$B$4:$E$4中查找H$11所在位置，就是第几列。

4.从右到左查找

Vlookup函数从左边列往右边列查，但我们有时需要根据右边列查左边列的数据，在不允许变动表格列的情况下，我们可以用进行IF配合。

在表一中，根据G12的学生期末成绩查找学生姓名，放到H12中。

我们只要在H12中输入公式：

=VLOOKUP(G12,IF({0,1},B5:B12,D5:D12),2,FALSE)

然后同时按Ctrl+Shift+Enter结束，关于IF函数的用法，请参考我的另一篇文章“如何成为Excel大神—IF函数”。

公式的含义是：在IF({0,1},B5:B12,D5:D12)返回的区域内，在第1列中查找等于(第4个参数是False，所以是等于)G12的数据，找到后，返回2列(第3个参数是2)对应的数据。

5.交叉查找

很多时候我们需要同时根据行列信息查找交叉单元格的数据。

在表一中，根据G12的学生姓名查找期末的成绩，放到H12中。

我们只要在H12中输入公式：

=VLOOKUP($G12,$B$5:$E$12,MATCH(H$11,$B$4:$E$4,0),FALSE)

公式的含义是：在B5:E12区域内，在B列中查找等于(第4个参数是False，所以是等于)G12的数据，找到后，返回MATCH(H$11,$B$4:$E$4,0)返回的值对应的数据。

MATCH(H$11,$B$4:$E$4,0)：在$B$4:$E$4中查找H$11所在位置，就是第几列

大家可能发现，这个公式和第3点的一样，是的，只要是动态获取第3个参数col_index_num的需求，都可能考虑这样的写法。

6.区间查找

假如我们有如下图规则，根据该规则对表一的平均成绩进行评价。

我们要根据学生平均分所在区间，来得出评价，我们需要添加评价区间数据进行辅助查询，然后利用Vlookup的模糊匹配实现该功能。

首先我们要对G18:G21升序排序。

我们在F5中输入公式：

=VLOOKUP(E5,$G$18:$H$21,2,TRUE)

公式的含义是：第4个参数是True，所以是模糊匹配，用平均分E5在$G$18:$H$21区域内查找，从第一个数据开始匹配，匹配到就返回该数据，没有匹配到一样的值就继续与下一个值进行匹配，直到遇到大于查找值的值，此时返回上一个数据，找到后，返回H列(第3个参数是2，所以是H列)数据。

7. 查询结果是合并单元格

在表一中，查找G12对应的平均分，而这个平均分是个合并单元格。

我们只要在H12中输入公式：

=VLOOKUP(9E+307,OFFSET(E5,,,MATCH(G12,B5:B12,0)),1,1)

9E+307表示Excel支持的最大数值。

公式的含义是：先通过MATCH(G12,B5:B12,0)获取到G12在B5:B12中的位置为6，那么OFFSET函数返回的数据是E5:E10，再用VLOOKUP的模糊查询查找并返回。

如果这种方法感觉很绕，急忙看不明白，那我们换一种处理办法。

添加一个辅助列，用VLOOKUP函数把合并单元格的列拆分成不合并的。

在F5中输入公式：=VLOOKUP(9E+307,E$5:E5,1,1)，往下填充到F12。

9E+307表示Excel支持的最大数值。

下来在H12中输入公式：=VLOOKUP(G12,B5:F12,5,0)

8.查询区域存在合并单元格

我们将演示数据修改成如下图，要查找二班李四的成绩，班级列存在合并单元格，姓名列存在重名。

在I12中输入公式：=VLOOKUP(H12,INDIRECT("C"&MATCH(G12,B:B,0)&":D25"),2,0)

公式的含义是：先通过MATCH(G12,B:B,0)获取到G12在B列中的位置为22，得到字符创“C22:D25”，然后用INDIRECT函数返回该字符串对应的区域数据，这时公式其实就变成=VLOOKUP(H12,C22:D25,2,0)，第2列就是我们想返回的列。

9.通配符查找

Vlookup函数支持通配符(*和？)查找

星号表示任意一串字符，问号表示任意单个字符，

如果需要查找星号和问号本身，需要在星号和问号前加波形符~，

此时星号和问号只代表一个字符，不是通配符。

想查找包含~的数据时，只要在~前面加波形符~，“~~”就是查找~

因为这个比较容易理解，我们不举例子了

10.查找一个值，返回多个结果

我们将演示数据修改成如下图，要查找男生的成绩

首先我们添加一个辅助列，在A18中输入公式=(C18=$G$12)+E17，往下填充到A25，目的是每发现一名男生，数字+1。

我们只要在H12中输入公式：=IFERROR(VLOOKUP(ROW(A1),$A$2:$E$11,4,0),"")

往下填充公式，看到空值就表示查完了。

11. lookup_value和table_array格式不一致

lookup_value和table_array格式不一致时，将无法返回想要的结果，无法修改原数据格式时，可以通过将lookup_value转换成和table_array一样的格式来解决，格式不一致有2种情况。

• lookup_value是数值型，table_array是字符型

只要在lookup_value后面添加&""，就是说给lookup_value加上一个空字符串，让它变成字符串

• lookup_value是字符型，table_array是数值型

只要在lookup_value后面添加*1，就是让lookup_value变成数值

Excel函数及技巧持续更新中，如果对你有帮助，请关注点赞支持一下。