文君竹

又一个WordPress站点

自动门生产厂家函数篇:人见人爱的VLOOKUP,你真的会用他吗?-爱上Excel合伙人

函数篇:人见人爱的VLOOKUP,你真的会用他吗?-爱上Excel合伙人
张嘉蓉 点击上面微信号关注我关注我哟
【1】教程分享,全面学习Excel技巧、函数、图表和VBA编程;
【2】工作实际运用Excel案例,带你从入门走向技巧帝;
【3】开设excel培训课程、工作运用工具开发。
Hello,大家好,首先允许我做下自我介绍,我就是传说中的人见人爱花见花开车见车爆胎的号称最常用的、使用频率最高的、人送外号大众情人的VLOOKUP是也!
关于我的秘密,你真正了解多少呢?下面我将从头到脚的重新介绍下我自己,看能俘获多少少女的心!
函数语法解析
1、函数定义:
在数据表的首列查找指定的值,并返回数据表当前行中指定列处的值。
2、语法格式:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
VLOOKUP(查找值,查找区域,要返回的结果在查找区域的第几列,匹配方式)
3、参数说明:
①、lookup_value(必需):要查找的值,可以为数值、引用或文本字符串。查找文本时,文本不区分大小写。
②、Table_array(必需):查找区域,可以使用对区域或区域名称的引用、常数数组、计算后的内存数组。并要求查找值在该区域的第一列,且其它列包含需返回的内容。
③、col_index_num(必需):要返回的结果在查找区域中的序列号,可以为数字或结果为数字的表达式。
如果col_index_num小于1,函数VLOOKUP返回错误值值#VALUE!;
如果col_index_num大于table_array的列数,函数VLOOKUP返回错误值#REF!。
④、range_lookup(可选):查找方式,指明是近似匹配和精确匹配。
如果为TRUE或省略,则返回近似匹配值,也就是说,如果找不到精确匹配值,则返回小于lookup_value的最大数值;
Table_array中的首列值必须以升序排序;
如果为FALSE或0,函数VLOOKUP将返回精确匹配值,如果找不到,则返回错误值#N/A。
4、注意事项:
①、在查找区域的第一列中搜索文本值时,请确保其第一列中的数据没有前导空格、尾部空格、直引号(' 或 )与弯引号(‘ 或 “) 不一致或非打印字符。否则,VLOOKUP可能返回不正确或意外的值。
②、在搜索数字或日期值时,请确保查找区域第一列中的数据未存储为文本值。否则,VLOOKUP可能返回不正确或意外的值。
③、如果range_lookup是精确匹配且lookup_value为文本,则可以在lookup_value中使用通配符 问号 (?) 和星号 (*)。
问号匹配任意单个字符;星号匹配任意一串字符。如果要查找通配符本身,则该字符前键入波形符(~)。
函数示例

01
精确匹配

公式:
=VLOOKUP(F3韦团儿 ,A3:D8,4,0)
解析:
第一参数:查找值,查找君柳,输入F3
第二参数:查找区域,注意查找区域的首列要包含查找值,区域为A3:D8
第三参数:要返回的结果在查找区域的第几列,爱好在查找区域的第4列,所以为4
第四参数:匹配方式,精确匹配,输入FALSE或0

02
近似匹配

公式:
=VLOOKUP(B14,E$14:F$17,2)
解析:
第一参数:查找值,查找业绩,输入B14
第二参数:查找区域,查找区域的首列要包含查找值,区域为E14:F17
第三参数:要返回的结果在查找区域的第几列,等级在查找区域的第2列,所以为2
第四参数:匹配方式,近似匹配,为TRUE或省略
以查找B14单元格9847为例,近似匹配,则返回小于9847的最大数值,即9000,其对应的等级为优丁文元。
注意:查找区域中的首列值必须以升序排序

03
查找第一次采购单价

公式:
=VLOOKUP(E25超级异能左手,B25:C36,2,)
解析:当查找区域首列出现有两个或更多值与查找值匹配时,函数VLOOKUP返回第一次出现的对应值。

04
通配符查找

公式:
=IFNA(VLOOKUP("*"&D42&"*",A$42:B$46,2,),"")
解析:全称中包含简称,用通配符星号(*)佛萨,星号(*)匹配任意一串字符。
查找不到时会返回错误值#N/A,可以用函数IFERROR或IFNA容错。
有关函数IFERROR和IFNA的用法,详情请点击下面链接:
函数篇:容错高手IFERROR与IFNA

05
带“~”的查找

公式:
=VLOOKUP(F53,A53:B58董沁,2,0)
查找值F53,查找区域A53:B58,地区在查找区域的第2列,精确查找叶剑波,没错啊,为什么结果会显示错误值呢?
原因在于波形符(~)

公式:
=VLOOKUP(SUBSTITUTE(F53,"~","~~"),A53:B58,2,0)
解析:波形符(~)作为通配符金陵十二君,在查找包含其本身的值时,需在~前键入~,本题中用函数SUBSTITUTE将~替换成~~。

06
格式不一致的查找
分两种情况:
第一种:查找值文本型,查找区域数值型

出现错误值的原因在于格式不统一
正确解法:

公式:
=VLOOKUP(D64*1,A64:B69,2,0)
解析:将查找值转换为和查找区域首列的值一样的格式
转换的方式很多种,比如:+0,-0,--,*1,/1,^1......等等。
第二种:查找值数值型,查找区域文本型

同样的,出现错误值的原因在于格式不统一
正确解法:

公式:
=VLOOKUP(D75&"",A75:B80,2,0)
解析:查找值数值型,查找区域文本型超级客栈系统,将查找值连接个空(&"")变为文本,格式统一后就能查找出正确结果了。

07
取消合并单元格
第一种:全部为文本

公式:=VLOOKUP("座",A$86:A86,1,1)
或者:=VLOOKUP("々",A$86:A86,1,1)
注意:匹配方式为近似匹配
“々”很多人都打不出来,可以按快捷键<Alt+41385>
第二种:全部为数值

公式:
=VLOOKUP(9E+307,A$108:A108,1,1)
注意:匹配方式为近似匹配
这两种情况不要说只有函数LOOKUP能做到田震什么病,VLOOKUP照样做得到。

08
查找返回多列数据

公式:
=VLOOKUP($F130,$A130:$D135,COLUMN(B1),0),向右填充
解析:地区、性别、爱好分别在查找区域的第2、3、4列,可以用函数COLUMN构造;
COLUMN:返回一引用的列号
COLUMN(B1)即=2,公式右拉变成COLUMN(C1)、COLUMN(D1)正好得到列号2、3、4张养浩传,不需要手动更改数字。

09
反向查找

公式:
=VLOOKUP($F141,IF({1,0},$D141:$D146,A141:A146),2,0),向右填充
解析:
IF(条件,条件成立时返回的值,条件不成立时返回的值)
有关函数IF的用法,详情请点击下面链接:
函数篇:小小IF不简单
以IF({1,0},$D141:$D146,A141:A146)为例解释下IF{1,0}结构:
{1,0}是函数IF中的条件
当为1时条件成立返回$D141:$D146
当为0时条件不成立返回A141:A146

整体来说就是两列顺序对换,将逆序转换为顺序。
公式也可以写成:
=VLOOKUP($F141破馆珍剑,IF({0,1},A141:A146,$D141:$D146),2,0)

VLOOKUP+IF{0,1}可以实现逆向查找,VLOOKUP+CHOOSE组合照样可以

公式:
=VLOOKUP($F141,CHOOSE({1,2},$D141:$D146,A141:A146),2,0)

10
交叉查询

公式:
=VLOOKUP(F152,A152:D157,MATCH(G152呱呱学车,A151:D151,0),0)
解析:用函数MATCH找到要返回的值在查找区域的第几列
MATCH:返回符合特定值特定顺序的项在数组中的相应位置。
MATCH(查找值,查找区域,查找方式)
MATCH(G152,A151:D151,0)即在区域A151:D151中精确查找5月的位置为3
所以用函数VLOOKUP查找业务员为君柳时其返回区域A152:D157中对应的第3列中的值即6062。

11
合并单元格的引用问题

公式:
=VLOOKUP("座",OFFSET(A163,,,MATCH(G163,B163:B179,)),1,1)
解析:MATCH(G163,B163:B179,)部分找到客服“君柳”在区域B163:B179中的位置为8
OFFSET(基点,偏移行数,偏移列数,行高,列宽)
OFFSET(A163,,,MATCH(G163,B163:B179,))是以A163单元格为基点,偏移0行0列,返回行高为8的新区域A163:A170的引用。
抹黑按F9得到:

用“座”或“々”等较大的汉字查找区域中最后一个单元格内容。

12
合并单元格的查询问题

公式:
=VLOOKUP(VLOOKUP("々",A$185:A185,1,1),G:H,2,0)
解析:用VLOOKUP("々",A$185:A185,1,1)查找出对应区域中最后一个单元格内容;
外层再套个VLOOKUP精确查找出对应直播课程的单价隋蕾。

13
按指定次数重复

公式:
=IFERROR(VLOOKUP(ROW(A1)血王的疯妃,IF({1穆雷桑,0},SUMIF(OFFSET(B$207,,,ROW($1:$4)),"<>"),A$207:A$210),2,0),E207)&""
数组公式,按<Ctrl+Shift+Enter>三键结束。

14
与T+IF的组合应用

公式:
=SUM(VLOOKUP(T(IF({1},A221:A228)),D221:E228汪芷榆,2,0)*B221:B228)
数组公式,按<Ctrl+Shift+Enter>三键结束。
T起降维作用

15
多条件查找

公式:
=VLOOKUP(E236&F236,IF({1,0},A$236:A$243&B$236:B$243,C$236:C$243),2,0)
数组公式,按<Ctrl+Shift+Enter>三键结束。
VLOOKUP+CHOOSE组合实现多条件查找:

公式:
=VLOOKUP(E236&F236,CHOOSE({1,2},A$236:A$243&B$236:B$243,C$236:C$243),2,0)
数组公式,按<Ctrl+Shift+Enter>三键结束。

16
一对多查找

公式:
=IFERROR(VLOOKUP(D$249&ROW(A1),IF({1,0},A$249:A$257&COUNTIF(INDIRECT("A262:A"&ROW($249:$257)),D$249),B$249:B$257),2,),"")
数组公式,按<Ctrl+Shift+Enter>三键结束。
同样的IF{1,0}改成CHOOSE{1,2}也可以。
光说不练假把式自动门生产厂家,动手操作才是硬道理!
作者:仰望~星空
2017年直播课程(点击下面蓝色字跳转页面了解详情)
有电脑有Wi-Fi就可以看直播
1、SQL基础课程499
2、函数七天199
3、函数中级600
4、技巧课398
5、数据透视表速成299
6、SQL高级课程499
7、VBA基础499
8、VBA 高级699
9、VBA基础加VBA高级套餐价980
备注:同时报名两个课程八五折,此时报名还有你想不到的优惠!
有需要报名的联系合伙人Coffee
微信号:linlin748242

合伙人QQ交流社群
QQ群1:121053731(收费社群)
QQ群2:519685594(函数&VBA)
QQ群3:482664551(函数公开群)
QQ群4:497926173(财务公开群)
QQ群5:528527713(数据分析群)
QQ群6:542988423(透视&图表)
QQ群7:515547199(EXCEL无敌群)
QQ群8:205178522(Leaf VBA进步群)
QQ群9:478275911(工具箱售后服务群)
微信群:加微信ln995704896入群(答疑专群)

Excel办公,让职场更轻松!