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的用法,详情请点击下面链接:
▲
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({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}也可以。
光说不练假把式,动手操作才是硬道理!
作者:仰望~星空
联系客服