Lookup_value为需要在Table_array第一列中查找的数值。
可以为数值、引用或文本字符串。需要注意的是类型必须与table_array第一列
查找文本时,文本不区分大小写;可以使用通配符“*”、“?”。
Table_array为需要在其中查找数据的数据表。
可以使用对区域或区域名称的引用、常数数组、计算后的内存数组。
对区域引用时,可以引用整列,excel会自动判断使用区域。
该参数的第一列必须包含查找的内容,其它列包含需返回的内容;返回内容的列Col_index_num为table_array中待返回的匹配值的列序号。
如为1时,返回table_array第一列中的数值;为2,返回table_array第二列中
如果col_index_num小于1,函数 VLOOKUP 返回错误值值 #VALUE!;
如果col_index_num大于table_array的列数,函数 VLOOKUP 返回错误值 #REF Range_lookup为一逻辑值,指明函数VLOOKUP返回时是精确匹配还是近似匹配。
如果为TRUE或省略,则返回近似匹配值,也就是说,如果找不到精确匹配值,则
近似匹配查询一般用于数值的查询,table_array的第一列必须
如果range_value为FALSE(或0),函数VLOOKUP将返回精确匹配值。
此时,table_array不必进行排序。如果找不到,则返回错误值#
以上举例是vlookup的基本运用,再结合其它函数,可以发挥其更多作用。
处理查找错误
vlookup使用中一般会产生以下错误:
#VALUE!和 #REF!是由于col_index_num设置不当造成的,虽然可以使用ISERROR剔除,但建议不
有时col_index_num参数使用column()函数生成,当设置不当造成的错误用ISE
#N/A是常见的错误信息,需要针对不同情况处理:
精确匹配查找时出现,是由于未找到完全匹配的值。
近似匹配查找时出现,是由于查找值小于数据区的最小值。
如果vlookup的参数产生错误,vlooukp也返回相同错误,那不在本讨论范围内。
精确匹配查找时出现#N/A错误
首先检查引用范围是否正确,查询值是否在数据区的第一列。
当确定引用正确、不应出现#N/A(即应该查找到值)时,检查是否存在空格或其它字符、格式
因此,这是一个数组公式,需要按Ctrl+Shift+Enter输入
如果数据区比较大,数组公式会很慢;不如直接使用替换将数据
对于这类不可见字符,可以先复制该字符,然后替换该字符为空
查找内容格式不符,也会造成#N/A错误
可以使用鼠标选定几个单元格,看状态栏的合计。有,则为数值
要注意的是采用修改格式的方法,仅仅将单元格格式更改还不行
例如对于包含数值的常规单元格将格式改为文本后
单元格很多时,采用逐个激活的方法肯定会累死。可以采用分列
当某列数据需要全部转换时,采用分列是个好办法。它可以将文
分列位于菜单栏——数据——分列
由文本转数值也可以
复制一空白单元格,选定需转换的数据区,选择性
注意使用条件格式处理后单元格内的值仍是#N/A,其他单元格引
近似匹配查找时出现#N/A错误
近似匹配查找时,除了前述的格式等原因,查找值小于数据区的最小值将返回#N/A。
对于此类错误,只要数据区设计合理就可避免(建立可能的最小值),当然也可以使用ISNA去除精确匹配查询之基本运用
精确查找是vlookup最基本也是最常用的功能,对于数据量大的查找,其速度比菜单中的查找还快。设置vlookup第四个精确查找适用于文本,也适用于数值;但对数值查找时须注意格式一致,否则会出错
数
据
含
空
格
等
数
据
格
式
不
一
致
处理错误
返回
数值近似匹配查询之基本运用
近似匹配查找通常情况下用于累进数值的查找。此时第四个参数省略,或为true,或为非0数值。(EXCEL中0等同FALS
注意公式中第四个参数省略,或为true,或为非0数值。
并且查找的第一列必须升序排列,否则不会返回期望的结果(需要注意的是此时不一定返回错误)。
试着输入5000,你会发现税率为20%,应是15%(由条件指定),虽然对最后计算结果没有影响。以下公式可以解决这个
文本近似匹配查询之基本运用
对于文本,一般不使用近似匹配查找(非模糊查找)。
并非文本不能用于近似查找,主要是没有太多用途。除了下面这
这个例子利用文本近似匹配查找汉字的第一个拼音字母
由于EXCEL中汉字字符是按拼音排序的,因此汉字字符比较时的大左边的表是根据汉字拼音首字母排列,并取各字母的临界点汉字
返回
返回
通过将“家”与表中字符比较后,找到比“家”小的
这个例子一般用于姓名的缩写,而上面的公式只找第一个字符,
这个公式取得前三个汉字字符的拼音字母。使用MID依
使用该例关键是【表三】的建立;同理,如果要查找汉字的完整
但这个方法并不一直正确,由于多音字及文字大小排序与其拼音区别查询结果的空白与0值
依次序返回同一查询结果的多列内容
当查询某个结果的多列内容,并要依次取出,可使用column()函数作为index_num参数
column()函数返回公式所在列的列号,当公式复制时,会随单元格的移动相应改
lookup_value参数使用混合引用,如$B96。即对列标绝对引用(列标前加$)、上面公式只要输入C96中的公式,然后向右、向下复制就可以。
返回同一查询结果的多列(不按原次序)的内容
注意公式中Match函数的按部分内容模糊查询
返回
返回
返回
返回
vlookup支持通配符【*】和【?】,因此当查找内容不全时,可以使用通配符;如例公式中使用【&】将查找文本与通配符连接后查找,注意通配符连接位置注意查找中,有多个符合条件的结果时,只会返回第一个
在同一单元格按不同字段查询
返回多个符合条件的查询结果
该公式结果为:如姓名列内容与查找姓为便于复制,vlookup 第一个参数数值1、2、3……使有两个并列条件(不同字段)的查询
对于此类根据两个或多个
B158公式=C158&D158,即
有些数据需要在两个数据间插入一特
例如两个条件
此时就需要用其他符号将
使用【&】将要查询的两个条件合并后
使用数组常量的查询
一般,使用vlookup 前需要有一个对应的表格供查找,即作为vlookup 的Table_array 参数的表格区域;
返回
返回
返回
返回
但有时这个表格可能会显得累赘;如果数据不是太多而且比较固定,可以考虑使用数组常量代替单元格区域的方法;
所谓数组常量就是直接输入的一组数组,以“{}”开始结束、同一行的数值用逗号“,”
如{10,20,30},是一行三列(横向)、包含三个元素的一维数组常量{10;20;30;40},是一列四行(纵向)、包含四个元素的一维数组常量{10,20,30;40,50,60},是两行三列、包含六个元素的二维数组常量;二维数组常
如果你觉得输数组常量麻烦(要区分【,】、【;】,文本要加【"】),
可以先将数据输入单元格区域,然后在其它单元格输入【=】,用鼠标选定该区域后按F9
其实将以前使用的vlookup 公式中第三个参数(区域不要太大)选定按F9,再回车,可以看到公式正常运以下举例中的公式并不是数组公式,输入时不用Ctrl+Shift+Enter一齐按
F9得到的;
从右向左查询
我们知道,v lookup查找的列必须位于查找区域的最左列,有时会需要按右面的查找左边的值以【表一】为例,要按【姓名】查询【工号】很容易;但反过来按【工号】查找【姓名】直接使用vlooku
此时可以使用的公式很多,如l ookup、index+match、offset+match、indirect+mat
但由chenjun版主提供的使用vlookup的解法我觉得很有参考价值,介绍这个用法的目的主要是学会将两
可以看到,在该内存数组中,【工号】位于【姓名】的左侧,Vlookup在该内存注意{1,0}是一行二列(横向)常量数组,后面的两个区域是多行一
程香宙的示例:
返回
=VLOOKUP(G11,C6:H8,G12,FALSE
功能
这个函数在表格左侧的行标题中查找指定的内容
当找到时,它再挑选出该行对应的指定列的单元格内容。语法
VLOOKUP (lookup_value ,table_array ,col_index_num
,range_lookup)
Lookup_value 为需要在数组第一列中查找的数值。Lookup_value 可以为数值、引用或文本字符串。Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用,例如数据库或数通过在“数据”菜单中的“排序”中选择“升序”,可将数值按升序排列。Table_array 的第一列中的数值可以为文本、数字或逻辑值。文本不区分大小写。
说明
如果函数 VLOOKUP 找不到 lookup_value,且 range_lookup 为 TRUE,则使用小于等于 lookup_value 的最大值。如果 lookup_value 小于 table_array 第一列中的最小数值,函数 VLOOKUP 返回错误值 #N/A。
如果函数 VLOOKUP 找不到 lookup_value 且 range_lookup 为 FALSE,函数 VLOOKUP 返回错误值 #N/A。格式
没有专门的格式示例 1
下面的下示例是以指定的名字和月份为基础查找一个数值.=VLOOKUP()是用于沿第一列向下查找指定的名字.难点是如何向右查找指定的月份.
解决这个难题的方法是使用=MATCH()函数.
函数=MATCH()通过使用找到名字的列表查找对应月份.并推算该月份在列表中的位置.不巧的是,因为月份列表的搜索范围与查找数值的范围不等宽.
函数=MATCH()函数返回的数字比我们需要的数字少1, 因此在公式中用了+1进行调整.
函数=VLOOKUP()中最后使用了 FALSE ,因此左侧标题行不用排序。
如果 range_lookup 为 TRUE,则 table_array 的第一列中的数值必须按升序排列:…、-2、-1、0、1、
TRUE;否则,函数 VLOOKUP 不能返回正确的数值。如果 range_lookup 为 FALSE,table_array 不必进行排序。Col_index_num 为 table_array 中待返回的匹配值的列序号。Col_index_num 为 1 时,返回 table
数值;col_index_num 为 2,返回 table_array 第二列中的数值,以此类推。如果 col_index_num 小于 1,函数 V 返回错误值值 #VALUE!;如果 col_index_num 大于 table_array 的列数,函数 VLOOKUP 返回错误值 #REF!。
Range_lookup 为一逻辑值,指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省
值,也就是说,如果找不到精确匹配值,则返回小于 lookup_value 的最大数值;如果 range_value 为 FALSE,函数VLOOKUP 将返回精确匹配值。如果找不到,则返回错误值 #N/A。函数=VLOOKUP()现在使用函数 =MATCH()得到的调整的数字n,在对应名字所在行向右查找到该行第n 列对应单元格容.
示例 2
这个示例使用函数=VLOOKUP() 查找不同小车生产厂商不同配件的价值。
函数=VLOOKUP()向下扫描F列的标题行并查找对应的位于C列的配件名称.
找到配件后,函数VLOOKUP根据函数MATCH找到的位置查找到对应配件的价格。
公式中使用了绝对引用,为的是确保公式复制移动时函数 =HLOOKUP() 和=MATCH()引用的范围不发生变化
示例 3
下面的示例是一个建材经销商提供的不同采购数量的折扣率
价格表中显示了砖,木材和玻璃的单价.
折扣表提供了不同产品不同采购数量的折扣率.
采购表是采购预算.
所有的预算结果显示在采购表中.
产品名称列表在C列.
单价是从价格表中获得的.
FALSE选项表示产品名称在价格表中没有排序整理.
使用FALSE强迫搜索精确匹配. 如果没有找到,则函数显示错误.
折扣是从折扣表中获得的
如果采购数量与折扣表中某个值匹配,函数 =VLOOKUP将在折扣表中查找正确的匹配折扣.
TRUE选项表示采购数量在折扣表中经过了升序排列整理.
使用TRUE允许模糊匹配.如果采购数量在折扣表中没有找到匹配的值,则它下面较小的值将被使用.
比如采购数量为125将向下与100匹配,并且使用100对应列的折扣率.
公式为:
单价E118: =VLOOKUP(C118,C106:D108,2,FALSE)
折扣F118: =VLOOKUP(D118,F106:I108,MATCH(C118,G105:I105,0)+1,TRUE)合计
G118: =(D118*E118)-(D118*E118*F118)
示例4
型必须与table_array第一列的类型一致。
“*”、“?”。
算后的内存数组。
需返回的内容;返回内容的列序号由下个参数指定。
,返回table_array第二列中的数值,以此类推。
误值值 #VALUE!;
VLOOKUP 返回错误值 #REF!。
,如果找不到精确匹配值,则返回小于lookup_value的最大数值;
table_array的第一列必须按升序排列;否则不能返回正确的结果。返回精确匹配值。
如果找不到,则返回错误值#N/A;可
isna检测错误后使用if判断去除错误信息。
使用ISERROR剔除,但建议不要这么做,因为这类错误需要纠正。
当设置不当造成的错误用ISERROR剔除时,会与#N/A错误混淆,不利于公式查错。讨论范围内。
存在空格或其它字符、格式是否一致
返回
含空格,可能看不出,但在精确匹配查找时造成不完全匹配
的空格替换掉,或使用TRIM去除空格后查找
”后含空格,在精确匹配查找时不完全匹配
接引用单元格区域,而是引用对$B$87:$C$88运算trim后的内存数组
rl+Shift+Enter输入
;不如直接使用替换将数据区的空格替换后使用普通公式。
不可见字符(非空格),使用trim、clean、替换空格都不能去除。
经常会出现这类字符,CODE可以测试其并非空格
字符,然后替换该字符为空白
态栏的合计。有,则为数值,无,则为文本。
,一个是按数值查文本,一个是按文本查数值
据区的格式,使之统一;也可以在公式中处理
"",强制转为文本
仅将单元格格式更改还不行:
规单元格将格式改为文本后,单元格中的值仍是数值形式,需要激活(双击)才会真正转为文本。肯定会累死。可以采用分列或选择性粘贴的方法:
列是个好办法。它可以将文本转为数值,也可将数值转为文本。
定需转换的数据区,选择性粘贴——加
可以使用条件格式或直接在公式中处理
值仍是#N/A,其他单元格引用该单元格也会返回#N/A错误。
据区没有B)。
格式公式=isna(D55),并设定条件字体颜色与底色相同。
N/A,如vlookup函数返回#N/A,则ISNA函数返回TRUE。再使用IF函数判断,即可去除错误。
来去除#N/A错误,如此例使用COUNTIF测试数据区是否有查找值。
回#N/A。
),当然也可以使用ISNA去除。
找还快。设置vlookup第四个参数为false或0,即为精确查找。
域最好使用绝对引用(可在公式中选定区域按F4转换),便于复制。
B$6:$F$9位于第四列,因此第三个参数为4;精确查找,第四个参数FALSE 在名称框(编辑栏左边)可以选定;也可在插入—名称—定义中查看修改由于范围的改变,籍贯位于该范围的第3列,因此第三个参数为3
回的是时间序列值(即格式没有带过来)
0数值。(EXCEL中0等同FALSE,非零值均视为TRUE)
影响。以下公式可以解决这个问题:
(非模糊查找)。
没有太多用途。除了下面这个例子:
的第一个拼音字母
的,因此汉字字符比较时的大小与拼音同序(一般情况下)
,并取各字母的临界点汉字字符(该字母的“最小汉字”)
”小的最大值“夻”,返回J
只找第一个字符,因此需要修改
用MID依次取出字符后由Vlookup查询到字母,最后“&”连接文本
要查找汉字的完整拼音,可以建立一个由各种拼音组成的类似列表。
字及文字大小排序与其拼音不完全一致的原因,有时返回的字母可能不是你要的
对于查找到的0值会返回0;
单元格(非空字符串)也会返回0;
串仍返回空字符串。
值和空白单元格区别,可以直接判断是否="",或使用len函数测试返回值的长度;
0值,可直接判断=0,或在【工具】—【选项】中将【零值】去掉。
作为index_num参数
时,会随单元格的移动相应改变
标绝对引用(列标前加$)、行号相对引用(行号前没有$);复制时引用单元格会改变。h函数确定项目内容在B8:F8区域的位置,将返回值作为vlookup的第三个参数。
式中Match函数的第一个参数使用行号绝对引用、列标相对引用;
Vlookup函数的第一个参数使用行号相对引用、列标绝对引用;
使用通配符;如例
tif判断,如果在姓名区中有C131单元格的内容,按姓名查找;否则按工号查找isna判断,如果按姓名查找出错,就按工号查找;
一个vlookup,第二、三个参数由判断结果决定(查找范围、列号)。
会返回第一个结果(数据区由上向下第一个)
需要按姓名查找同名的各个工号
数据量大时,使用数组公式会由于计算量大,速度会很慢
添加辅助列(注意辅助列需添加在左侧,因为要根据辅助列的内容查找)
式为=($C$148=C141)+B140;等同=IF($C$148=C141,1,0)+B140
查找姓名相同,则按顺序显示1、2、3……,从而将相同姓名区分
到对应查找内容的多个结果(注意查找区域由辅助列开始)
第一个参数数值1、2、3……使用函数row()返回的行号产生
到区别相同内容的目的
或多个并列条件查找某个结果的问题,使用vlookup也需要添加辅助列
式=C158&D158,即将被查找的内容合并作为一列,并以此作为查找列。
)以示区别
】将要查询的两个条件合并后作为查询值,在区域$B$158:$E$163中查询第4列内容参数的表格区域;
量代替单元格区域的方法;
一行的数值用逗号“,”分隔、同一列的数值用分号“;”分隔
三个元素的一维数组常量
四个元素的一维数组常量
的二维数组常量;二维数组常量需要行列等长
用鼠标选定该区域后按F9,显示的就是数组常量,然后复制到公式中
车,可以看到公式正常运作;此时已使用了数组常量
F9得到的;
洁多了,还不占用单元格
插入】——【名称】——【定义】中查看
的查找左边的值
lookup就不行了
t+match、indirect+match等
法的目的主要是学会将两个一维数组合并为一个二维数组
内存数组:
王麻子";"KT004","赵六儿"}
】的左侧,Vlookup在该内存数组中查找
后面的两个区域是多行一列(纵向),即两个数组的方向不同,这样才会生成两列多行数组
KUP(G11,C6:H8,G12,FALSE)
或文本字符串。
库或数据清单。
lue 的最大值。回错误值 #N/A。
、1、2、…、-Z、FALSE、_array 不必进行排序。
table_array 第一列中的index_num 小于 1,函数 VLOOKUP 返回错误值 #REF!。
或省略,则返回近似匹配ange_value 为 FALSE,函数
查找到该行第n列对应单元格的输入内
3,0)+1,FALSE)
范围不发生变化。
折扣表