VLOOKUP( )函数和IF( )函数、SUM( )函数一起,成为大家用的最多的三大函数之一。现在我们一起来看看Vlookup的常用方法,由浅入深,循序渐进!
本文主要内容:
- VLOOKUP函数语法规则
- VLOOKUP函数常规(正向)查询
- VLOOKUP函数混合引用
- VLOOKUP函数反向查询
- VLOOKUP函数区间匹配
- VLOOKUP函数多区域查询
- VLOOKUP函数多条件查询
- VLOOKUP函数动态查询多行多列结果
此外,VLOOKUP函数还有一对多查询、使用通配符查询等,不过适用情况较少,此文不予讲解。
VLOOKUP函数语法规则
所有的查找函数无非四大查找哲学问题:查找谁、哪里找、怎么找、返回谁!
![95500a471fcd3efb332b70204e4c36df.png](https://img-blog.csdnimg.cn/img_convert/95500a471fcd3efb332b70204e4c36df.png)
这里要着重强调一下vlookup函数的第四个参数——匹配方式:
- 如果该参数为0或者False,即为精确匹配,如果查找不到则返回错误值#N/A
- 如果该参数为1、True或者省略,即为模糊匹配,如果查找不到则返回小于查找内容的最大值,前提是查找区域首列需升序排列
VLOOKUP函数常规(正向)查询
要求:已知员工工号,查找其手机号码
分析:
查找谁——工号(HZ005):H2
哪里找——员工信息表:A1:F10
返回谁——手机号码在第4列:4
怎么找——精确匹配:0
公式:=VLOOKUP(H2,A1:F10,4,0)
![f664cd9cf8064558bb93be075275e467.png](https://img-blog.csdnimg.cn/img_convert/f664cd9cf8064558bb93be075275e467.png)
VLOOKUP函数混合引用
要求:已知多名员工工号,查找其姓名
思路:
先确认第一位员工查找公式,再下拉复制到其他员工行。存在公式的复制,所以第一位员工的公式中查找区域应绝对引用。
分析:
查找谁——工号(HZ005):H2
哪里找——员工信息表:A1:F10
返回谁——姓名在第2列:2
怎么找——精确匹配:0
公式:=VLOOKUP(H2,$A$1:$F$10,2,0)-多处使用员工信息表,需要绝对引用
![87e7a280211cb66d000401016466a833.png](https://img-blog.csdnimg.cn/img_convert/87e7a280211cb66d000401016466a833.png)
****注意:
1、公式在复制时,需要考虑单元格的相对引用、绝对引用和混合引用;
2、由于多行都要使用A1:F10区域,所以锁行即可:A$1:F$10,此时公式=VLOOKUP(H2,A$1:F$10,2,0)
VLOOKUP函数反向查询
以上学习了vlookup函数的正向查询(已知工号查询手机号),接下来将学习vlookup函数反向查询(已知手机号查询姓名)。vlookup函数本身没有反向查询功能,一般借助IF+{1,0}数组,关于数组的知识,参见数组章节。
要求:已知员工手机号,查找其姓名
思路:
借助IF+{1,0}生成临时的二维区域,且手机号在前、姓名在后,然后运用正向查询即可。
分析:
查找谁——手机号:H2
哪里找——临时二维区域:IF({1,0},D2:D10,B2:B10),即手机号列和姓名列组成
返回谁——姓名列在临时二维区域的第2列(如不明白下面有补充说明):2
怎么找——精确匹配:0
公式:=VLOOKUP(H2,IF({1,0},$D$2:$D$10,$B$2:$B$10),2,0)
![23fde5154aff2966c5a736ce61b066d6.png](https://img-blog.csdnimg.cn/img_convert/23fde5154aff2966c5a736ce61b066d6.png)
****注意:
1、公式在复制时,需要考虑单元格的相对引用、绝对引用和混合引用;
2、由于多行都要使用D2:D10和B2:B10区域,所以锁行即可:D$2:D$10和B$2:B$10,此时公式=VLOOKUP(H2,IF({1,0},D$2:D$10,B$2:B$10),2,0)
补充说明:IF+{1,0}
1、IF({1,0},D2:D10,B2:B10)的运算结果,是由手机号和姓名列组成的9行、2列区域
2、IF+{1,0}的目的,就是构造一个手机号列在前,姓名列在后的2列数据区域K1:L9,然后用vlookup常规查询(姓名在第2列,精确查询),具体见如下动图:
![c4b483345587fcd87806698cb26fca47.gif](https://img-blog.csdnimg.cn/img_convert/c4b483345587fcd87806698cb26fca47.gif)
VLOOKUP函数区间匹配
以上学习了vlookup函数的精确查询,区间匹配是模糊查询
要求:已知员工销售额,查找其提成比例
思路:
提成比例是和销售额相关的阶梯式数据,每个员工根据销售额向下匹配,找到对应的区间。所以,适合VLOOKUP的模糊匹配:匹配小于查找内容的最大值。
分析:
查找谁——销售额:F2
哪里找——提成表:I2:J8
返回谁——提成比例在第2列:2
怎么找——模糊匹配:1
公式:=VLOOKUP(F2,$I$2:$J$8,2,1) -多处使用提成表区域,需绝对引用
![d61c5d7752fdb28d17d825b5db8640e3.png](https://img-blog.csdnimg.cn/img_convert/d61c5d7752fdb28d17d825b5db8640e3.png)
****注意:
1、公式在复制时,需要考虑单元格的相对引用、绝对引用和混合引用;
2、由于多行都要使用I2:J8区域,所以锁行即可:$I$2:$J$8,此时公式=VLOOKUP(F2,I$2:J$8,2,1);
3、模糊查询规则:如果查找区域内找不到查找内容,匹配小于查找内容的最大值;前提是查找区域第一列(销售额分段点)升序排列。
VLOOKUP函数多区域查询
要求:已知员工销售额和工龄,查找其提成比例
(提成比例和销售额相关,受工龄影响)
思路:
- 根据工龄不同,提成比例有两个计算表格,首先要判断工龄以确定员工的计算表格;
- 每个表格的提成比例是和销售额相关的阶梯式数据,每个员工根据销售额向下匹配,找到对应的区间。所以,适合VLOOKUP的模糊匹配:匹配小于查找内容的最大值。
分析:
查找谁——销售额:F2
哪里找——两个提成表中的一个:I3:J9 或者L3:M8
返回谁——提成比例在第2列:2
怎么找——模糊匹配:1
公式:=VLOOKUP(F2,IF(E2<3,$I$3:$J$9,$L$3:$M$8),2,1) -多处使用提成表区域,需绝对引用
![7fb90d6e088f7703d19b4828ea9beec1.png](https://img-blog.csdnimg.cn/img_convert/7fb90d6e088f7703d19b4828ea9beec1.png)
****注意:
1、公式在复制时,需要考虑单元格的相对引用、绝对引用和混合引用;
2、使用IF()函数选择提成表:如果工龄<3年,使用I3:J9,否则使用L3:M8;
3、由于多行都要使用I3:J9 或者L3:M8区域,所以锁行即可:I$3:J$9 或者L$3:M$8,此时公式=VLOOKUP(F2,IF(E2<3,I$3:J$9,L$3:M$8),2,1);
4、模糊查询规则:如果查找区域内找不到查找内容,匹配小于查找内容的最大值;前提是查找区域第一列(销售额分段点)升序排列。
VLOOKUP函数多条件查询
要求:已知员工部门和职务,查找其姓名
思路:
- vlookup只能有一个查找内容,所以两个条件的查找内容需要合并——“部门职务”;
- 查找区域需要包含两部分:查找内容所在的列和返回结果所在的列。查找内容所在的列由部门列和职务列合并——“部门职务”,然后和“姓名”列用IF+{1,0}数组组成临时二维表
分析:
查找谁——工号:H2&I2
哪里找——临时二维区域:IF({1,0},D2:D10&E2:E10,B2:B10),"部门职务”列和姓名列组成
返回谁——姓名列在临时二维区域的第2列(如不明白下面有补充说明):2
怎么找——精确匹配:0
公式:=VLOOKUP(H2&I2,IF({1,0},D2:D10&E2:E10,B2:B10),2,0)
![4ace6f45dbb8a9f15dba5455aae06a38.png](https://img-blog.csdnimg.cn/img_convert/4ace6f45dbb8a9f15dba5455aae06a38.png)
****注意:
1、公式在复制时,需要考虑单元格的相对引用、绝对引用和混合引用;
2、使用IF()函数构建临时表,临时表包含两列:“部门职务”列和“姓名”列。
VLOOKUP函数动态查询多行多列结果
要求:已知多名员工工号,查找其姓名和手机号
思路:
- 返回多列结果时,每次返回内容对应的列是不一样的,所以需要匹配出返回结果的内容是第几列
- 根据内容匹配出第几列可以用match()函数
分析:
查找谁——部门职务:H2
哪里找——员工信息表:A2:F10
返回谁——姓名和手机号码所在的列:用动态的match()函数匹配
怎么找——模糊匹配:0
公式:=VLOOKUP($H2,$A$2:$F$10,MATCH(I$1,$A$1:$F$1,0),0)
![ee660079542aa99b7276dba8b212f9fe.png](https://img-blog.csdnimg.cn/img_convert/ee660079542aa99b7276dba8b212f9fe.png)
****注意:
1、公式在复制时,需要考虑单元格的相对引用、绝对引用和混合引用;
2、虽然姓名和手机号码在员工信息表中所在的列是确定的,但是用match函数可以实现动态的匹配结果,具体见下图:
![922033b00162a6c09cc2897a2171a00f.gif](https://img-blog.csdnimg.cn/img_convert/922033b00162a6c09cc2897a2171a00f.gif)