vlookup两个条件匹配_VLOOKUP函数用法大全,一站式学会!

VLOOKUP( )函数和IF( )函数、SUM( )函数一起,成为大家用的最多的三大函数之一。现在我们一起来看看Vlookup的常用方法,由浅入深,循序渐进!

本文主要内容:

  • VLOOKUP函数语法规则
  • VLOOKUP函数常规(正向)查询
  • VLOOKUP函数混合引用
  • VLOOKUP函数反向查询
  • VLOOKUP函数区间匹配
  • VLOOKUP函数多区域查询
  • VLOOKUP函数多条件查询
  • VLOOKUP函数动态查询多行多列结果

此外,VLOOKUP函数还有一对多查询、使用通配符查询等,不过适用情况较少,此文不予讲解。

VLOOKUP函数语法规则

所有的查找函数无非四大查找哲学问题:查找谁、哪里找、怎么找、返回谁!

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

VLOOKUP函数混合引用

要求:已知多名员工工号,查找其姓名

思路:

先确认第一位员工查找公式,再下拉复制到其他员工行。存在公式的复制,所以第一位员工的公式中查找区域应绝对引用。

分析:

查找谁——工号(HZ005):H2

哪里找——员工信息表:A1:F10

返回谁——姓名在第2列:2

怎么找——精确匹配:0

公式:=VLOOKUP(H2,$A$1:$F$10,2,0)-多处使用员工信息表,需要绝对引用

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

****注意

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

VLOOKUP函数区间匹配

以上学习了vlookup函数的精确查询,区间匹配是模糊查询

要求:已知员工销售额,查找其提成比例

思路:

提成比例是和销售额相关的阶梯式数据,每个员工根据销售额向下匹配,找到对应的区间。所以,适合VLOOKUP的模糊匹配:匹配小于查找内容的最大值

分析:

查找谁——销售额:F2

哪里找——提成表:I2:J8

返回谁——提成比例在第2列:2

怎么找——模糊匹配:1

公式:=VLOOKUP(F2,$I$2:$J$8,2,1) -多处使用提成表区域,需绝对引用

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

****注意

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

****注意

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

****注意

1、公式在复制时,需要考虑单元格的相对引用、绝对引用和混合引用;

2、虽然姓名和手机号码在员工信息表中所在的列是确定的,但是用match函数可以实现动态的匹配结果,具体见下图:

922033b00162a6c09cc2897a2171a00f.gif
  • 0
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值