ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 效率神器,一键搞定繁琐工作
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
让更多数据处理,一键完成 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 39460|回复: 83

[分享] VLOOKUP套路大全

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2017-3-11 08:49 | 显示全部楼层 |阅读模式
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 祝洪忠- 于 2017-3-11 10:16 编辑

小伙伴们好啊,今天和大家来说说VLOOKUP的那些事儿,深入了解一下VLOOKUP函数的各种用法,看看这位大众情人还藏着多少不为人知的秘密。
函数的语法为:
VLOOKUP(要找谁,在哪儿找,返回第几列的内容,精确找还是近似找)
第一参数是要在表格或区域的第一列中查询的值。
第二参数是需要查询的单元格区域,这个区域中的首列必须要包含查询值,否则公式将返回错误值。如果查询区域中包含多个符合条件的查询值,VLOOKUP函数只能返回第一个查找到的结果。
第三参数用于指定返回查询区域中第几列的值,该参数如果超出待查询区域的总列数,VLOOKUP函数将返回错误值#REF!,如果小于1返回错误值#VALUE!。
第四参数决定函数的查找方式,如果为0或FASLE,用精确匹配方式,而且支持无序查找;如果为TRUE或被省略,则使用近似匹配方式,同时要求查询区域的首列按升序排序。

1、常规查询
如图,需要从B~E的数据表中,根据H3单元格的姓名查询对应的职务。
公式为:=VLOOKUP(H3,C:E,3,0)
1.png
提示:
  • VLOOKUP函数第三参数中的列号,不能理解为工作表中实际的列号,而是指定要返回查询区域中第几列的值。


2、带通配符的查询
如图,需要从B~E的数据表中,根据H3单元格的姓氏,查询对应的姓名和部门。
公式为:=VLOOKUP($H3&"*",$C:$E,COLUMN(A1),0)
6.png
提示:
通配符“*”表示任意多个字符,VLOOKUP函数第一参数使用$H3&"*",即在C列中查询以H2单元格内容开头的内容,并返回对应列的信息。


3、近似查询
如图,需要根据H~I的对照表,判断D列成绩对应的评议结果。
公式为:=VLOOKUP(D2,H:I,2)
8.png
提示:
  • VLOOKUP函数第四参数被省略,在近似匹配模式下返回查询值的精确匹配值或近似匹配值。如果找不到精确匹配值,则返回小于查询值的最大值。
  • 使用近似匹配时,查询区域的首列必须按升序排序,否则无法得到正确的结果。



4、逆向查询
如图,需要从B~E的数据表中,根据H3单元格的部门,查询对应的姓名。
公式为:=VLOOKUP(H3,CHOOSE({1,2},D2:D11,C2:C11),2,0)
2.png
提示:
  • VLOOKUP函数的查询值要求必须位于查询区域中的首列,如果被查找值不在数据表的首列时,需要先将目标数据进行特殊的转换。
  • CHOOSE函数第一参数使用常量数组{1,2},将查询值所在的D2:D11和返回值所在的C2:C11整合成一个新的两列多行的内存数组。
  • 生成的内存数组符合VLOOKUP函数的查询值必须处于数据区域中首列的要求。VLOOKUP函数以职务做查询条件,在内存数组中查询并返回对应的姓名信息,从而实现了逆向查询的目的。



5、多条件查询
如图,需要从B~E的数据表中,根据H3单元格的部门和I3单元格的职务,查询对应的姓名。
J3单元格公式为:=VLOOKUP(H3&I3,IF({1,0},D2:D11&E2:E11,C2:C11),2,)
7.png
提示:
  • 使用连接符“&”将部门和职务合并成新的字符串,以此作为VLOOKUP函数的查询条件。
  • IF部分,先将D列的部门和E列的职务进行连接,再使用IF({1,0}的方式,构造出部门职务在前、姓名在后的内存数组。
  • VLOOKUP函数在IF函数构造出的内存数组首列中查询部门职务字符串的位置,返回对应的姓名。
  • 数组公式,不要忘了按<Ctrl+Shift+Enter>组合键。



6、一对多查询
如图,需要从B~E的数据表中,根据H3单元格的职务,查询对应的多个姓名。
5.png
首先在A2单元格输入以下公式,向下复制:
=(E2=$H$3)+A1
3.png

然后在I3单元格输入以下公式,向下复制:
=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),"")
4.png
提示:
  • C列的职务每重复出现一次,A列的序号增加1。
  • VLOOKUP函数使用1至N的递增序列作为查询值,使用A:C列作为查询区域,以精确匹配的方式返回与之相对应的B列的姓名。注意查找区域必须由辅助列A列开始。
  • 最后将辅助列字体设置为白色或进行隐藏即可。



7、按指定次数重复数据
如图,需要根据C列指定的次数,重复显示B列的内容。
9.png

首先在A2单元格输入以下公式,向下复制:
=A1+C2
10.png

然后在E2单元格输入以下公式,向下复制:
=IFERROR(VLOOKUP(ROW(A1),A:B,2,0),E3)&""
11.png


评分

19

查看全部评分

TA的精华主题

TA的得分主题

发表于 2017-3-11 09:11 | 显示全部楼层
赶紧的,
收下了!

谢谢!

TA的精华主题

TA的得分主题

发表于 2017-3-11 09:20 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 liurunxing 于 2017-3-11 09:23 编辑

请教老师,
第一种用法,可以同时返回多列值吗,比如部门和职务?

学生太急了,后面就有类似用法。

TA的精华主题

TA的得分主题

发表于 2017-3-11 09:25 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2017-3-11 10:01 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
收藏学习!谢谢!

TA的精华主题

TA的得分主题

发表于 2017-3-12 17:46 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
收藏了,慢慢学习,谢谢版主。

TA的精华主题

TA的得分主题

发表于 2017-3-13 09:02 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
真没想到有这么多用法……

TA的精华主题

TA的得分主题

发表于 2017-3-17 15:47 | 显示全部楼层
赶紧收藏学习,真没想到还有这么多的用法。。。。

TA的精华主题

TA的得分主题

发表于 2017-3-20 06:53 | 显示全部楼层
原来有这么多种用途  ;……

TA的精华主题

TA的得分主题

发表于 2017-3-20 08:28 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
精彩 收藏学习!谢谢!
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-11-14 15:10 , Processed in 0.046675 second(s), 9 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表