Lookup的用法详解含实例和动画.docx
《Lookup的用法详解含实例和动画.docx》由会员分享，可在线阅读，更多相关《Lookup的用法详解含实例和动画.docx（13页珍藏版）》请在冰豆网上搜索。
Lookup的用法详解含实例和动画
Lookup的用法详解（含实例和动画）
VLOOKUP函数的用法
“Lookup”的汉语意思是“查找”，在Excel中与“Lookup”相关的函数有三个：
VLOOKUP、HLOOKUO和LOOKUP。
下面介绍VLOOKUP函数的用法。
一、功能
在表格的首列查找指定的数据，并返回指定的数据所在行中的指定列处的数据。
二、语法
标准格式：
VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）
三、语法解释
VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）可以写为：
VLOOKUP（需在第一列中查找的数据,需要在其中查找数据的数据表,需返回某列值的列号,逻辑值True或False）
1.Lookup_value为“需在数据表第一列中查找的数据”，可以是数值、文本字符串或引用。
2.Table_array为“需要在其中查找数据的数据表”，可以使用单元格区域或区域名称等。
⑴如果range_lookup为TRUE或省略，则table_array的第一列中的数值必须按升序排列，否则，函数VLOOKUP不能返回正确的数值。
如果range_lookup为FALSE，table_array不必进行排序。
⑵Table_array的第一列中的数值可以为文本、数字或逻辑值。
若为文本时，不区分文本的大小写。
3.Col_index_num为table_array中待返回的匹配值的列序号。
Col_index_num为1时，返回table_array第一列中的数值；
Col_index_num为2时，返回table_array第二列中的数值，以此类推。
如果Col_index_num小于1，函数VLOOKUP返回错误值#VALUE!
；
如果Col_index_num大于table_array的列数，函数VLOOKUP返回错误值#REF!
。
4.Range_lookup为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配。
如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果range_value为FALSE，函数VLOOKUP将返回精确匹配值。
如果找不到，则返回错误值#N/A。
四、应用例子
ABCD
1编号姓名工资科室
22005001周杰伦2870办公室
32005002萧亚轩2750人事科
42005006郑智化2680供应科
52005010屠洪刚2980销售科
62005019孙楠2530财务科
72005036孟庭苇2200工会
A列已排序（第四个参数缺省或用TRUE）
VLOOKUP（2005001,A1:
D7,2,TRUE）等于“周杰伦”
VLOOKUP（2005001,A1:
D7,3,TRUE）等于“2870”
VLOOKUP（2005001,A1:
D7,4,TRUE）等于“办公室”
VLOOKUP（2005019,A1:
D7,2,TRUE）等于“孙楠”
VLOOKUP（2005036,A1:
D7,3,TRUE）等于“2200”
VLOOKUP（2005036,A1:
D7,4,TRUE）等于“工会”
VLOOKUP（2005036,A1:
D7,4）等于“工会”
若A列没有排序，要得出正确的结果，第四个参数必须用FALAE
VLOOKUP（2005001,A1:
D7,2,FALSE）等于“周杰伦”
VLOOKUP（2005001,A1:
D7,3,FALSE）等于“2870”
VLOOKUP（2005001,A1:
D7,4,FALSE）等于“办公室”
VLOOKUP（2005019,A1:
D7,2,FALSE）等于“孙楠”
VLOOKUP（2005036,A1:
D7,3,FALSE）等于“2200”
VLOOKUP（2005036,A1:
D7,4,FALSE）等于“工会”
五、关于TRUE和FALSE的应用
先举个例子，假如让你在数万条记录的表格中查找给定编号的某个人，假如编号已按由小到大的顺序排序，你会很轻松地找到这个人；假如编号没有排序，你只好从上到下一条一条地查找，很费事。
用VLOOKUP查找数据也是这样，当第一列已排序，第四个参数用TRUE（或确省），Excel会很轻松地找到数据，效率较高。
当第一列没有排序，第四个参数用FALSE，Excel会从上到下一条一条地查找，效率较低。
笔者觉得，若要精确查找数据，由于计算机运算速度很快，可省略排序操作，直接用第四个参数用FALSE即可。
关于Lookup的实例
示例1
下面的下示例是以指定的名字和月份为基础查找一个数值.
=VLOOKUP（）是用于沿第一列向下查找指定的名字.
难点是如何向右查找指定的月份.
解决这个难题的方法是使用=MATCH（）函数.
函数=MATCH（）通过使用找到名字的列表查找对应月份.并推算该月份在列表中的位置.
不巧的是，因为月份列表的搜索范围与查找数值的范围不等宽.
函数=MATCH（）函数返回的数字比我们需要的数字少1,因此在公式中用了+1进行调整.
函数=VLOOKUP（）现在使用函数=MATCH（）得到的调整的数字n,在对应名字所在行向右查找到该行第n列对应单元格的输入内容.
函数=VLOOKUP（）中最后使用了FALSE，因此左侧标题行不用排序。
元月
二月
三月
程香宙
10
80
97
刘冰
20
90
69
程龙
30
100
45
程坤
40
110
51
chengxiang
50
120
77
输入要查找的名称:
程龙
输入要查找的月份:
三月
结果是:
45
=VLOOKUP（F60,C54:
F58,MATCH（F61,D53:
F53,0）+1,FALSE）
3
=MATCH（F61,D53:
F53,0）
示例2
这个示例使用函数=VLOOKUP（）查找不同小车生产厂商不同配件的价值。
函数=VLOOKUP（）向下扫描F列的标题行并查找对应的位于C列的配件名称.
找到配件后，函数VLOOKUP根据函数MATCH找到的位置查找到对应配件的价格。
公式中使用了绝对引用，为的是确保公式复制移动时函数=HLOOKUP（）和=MATCH（）引用的范围不发生变化。
厂商
配件
价值
查找表格
日本丰田
火花塞
￡50
日本丰田
福特
奔驰
奔驰
变速箱
￡600
变速箱
500
450
600
福特
引擎
￡1,200
引擎
1000
1200
800
奔驰
方向盘
￡275
方向盘
250
350
275
福特
火花塞
￡70
火花塞
50
70
45
福特
刹车片
￡290
刹车片
300
290
310
日本丰田
变速箱
￡500
福特
引擎
￡1,200
=VLOOKUP（C80,F74:
I78,MATCH（B80,G73:
I73,0）+1,FALSE）
示例3
下面的示例是一个建材经销商提供的不同采购数量的折扣率
价格表中显示了砖,木材和玻璃的单价.
折扣表提供了不同产品不同采购数量的折扣率.
采购表是采购预算.
所有的预算结果显示在采购表中.
产品名称列表在C列.
单价是从价格表中获得的.
FALSE选项表示产品名称在价格表中没有排序整理.
使用FALSE强迫搜索精确匹配.如果没有找到，则函数显示错误.
折扣是从折扣表中获得的
如果采购数量与折扣表中某个值匹配，函数=VLOOKUP将在折扣表中查找正确的匹配折扣.
TRUE选项表示采购数量在折扣表中经过了升序排列整理.
使用TRUE允许模糊匹配.如果采购数量在折扣表中没有找到匹配的值，则它下面较小的值将被使用.
比如采购数量为125将向下与100匹配,并且使用100对应列的折扣率.
折扣表
价格表
砖
木材
玻璃
砖
￡2
1
0%
0%
0%
木材
￡1
100
6%
3%
12%
玻璃
￡3
300
8%
5%
15%
采购表
项目
采购数量
单价
折扣
合计
砖
125
￡2
6%
￡235
木材
200
￡1
3%
￡194
玻璃
150
￡3
12%
￡396
砖
225
￡2
6%
￡423
木材
50
￡1
0%
￡50
玻璃
500
￡3
15%
￡1,275
公式为:
单价
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
该示例使用1个大气压的空气值。
密度
粘度
温度
0.457
3.55
500
0.525
3.25
400
0.616
2.93
300
0.675
2.75
250
0.746
2.57
200
0.835
2.38
150
0.946
2.17
100
1.09
1.95
50
1.29
1.71
0
公式
说明（结果）
2.17
在A列中查找1，并从相同行的B列中返回值（2.17）
=VLOOKUP（1,B128:
D136,2）
100
在A列中查找1，并从相同行的C列中返回值（100）
=VLOOKUP（1,B128:
D136,3,TRUE）
#N/A
在A列中查找0.746。
因为A列中没有精确地匹配，所以返回了一个错误值（#N/A）
=VLOOKUP（0.7,B128:
D136,3,FALSE）
#N/A
在A列中查找0.1。
因为0.1小于A列的最小值，所以返回了一个错误值（#N/A）
=VLOOKUP（0.1,B128:
D136,2,TRUE）
1.71
在A列中查找2，并从相同行的B列中返回值（1.71）
=VLOOKUP（2,B128:
D136,2,TRUE）
Excel查询函数Lookup和Vlookup区别
电脑爱好者 张剑悦
Excel查询函数中，Lookup和Vlookup有哪些区别？
它们在应用中应该如何把握？
请看本文讲解。
★Lookup——数与行列比
Lookup的工作职责是什么呢?
用一个数与一行或一列数据依次进行比较，发现匹配的数值后，将另一组数据中对应的数值提取出来。
·工资税率表：
用数值比较
根据不同的工资进行不同的税率计算是一个常见的应用。
我们来看这张“工资税率查询”表（见图1）。
现在要在右侧根据“收入”（F列），直接得到对应的“税率”（G列）。
在计算第1个“税率”时，输入函数公式“=LOOKUP（F4,$B$3:
$B$8,$D$3:
$D$8）”，回车，便可得到“36.00%”。
这个结果是怎么来的?
用F4中的第1个收入数“$123,409”，与左侧表的“收入最低”各档数据（“$B$3:
$B$8”）进行对比，虽然“$123,409”在“收入最低”各档数中没有完全一致的数据与之匹配，但是会与其中小于它的最大数“$58,501”相匹配。
这样，同一行对应的“36.00%”就提取出来了。
·图书销售表：
用文本比较
Lookup函数的对比数还可以是文本。
在这张图书销售查询表中（见图2），用下表输入的“编号”（A15单元格）文本当作查询数，与上表的“编号”一列（$A$3:
$A$11）进行对比，查询到了匹配的文本后，将“教材名称”一列（$B$3:
$B$11）对应的数据提取出来。
公式是“=LOOKUP（A15,$A$3:
$A$11,$B$3:
$B$11）”。
★Vlookup——数与表格比
Lookup有一个大哥——Vlookup函数。
两兄弟有很多相似之处，但大哥本领更大。
Vlookup用对比数与一个“表”进行对比，而不是Lookup函数的某1列或1行，并且Vlookup可以选择采用精确查询或是模糊查询方式，而Lookup只有模糊查询。
·模糊匹配
用Vlookup函数进行模糊查询时，几乎与Lookup的作用完全一致。
我们用Vlookup函数来提取第1个例子中的工资税率结果。
函数公式为“=VLOOKUP（F4,$B$3:
$D$8,3,TRUE）”。
在这个函数中，用第1个收入“$123,409”（F4单元格）当作对比数，用它与左侧表（“$B$3:
$D$8”）的第1列数进行对比，虽然“$123,409”在“收入最低”各档数中没有完全一致的数据与之匹配，但是函数的最后一个参数是“TURE”（“TURE”就是模糊查询），所以它会与其中小于它的最大数“$58,501”相匹配。
并将表中第3列（函数的第3个参数为“3”）对应的数据提取出来，所以结果同样是“36.00%”。
·订单明细表：
精确匹配
有时候，我们需要精益求精。
在下面这个“订单明细表”（见图3）中，最后一列“货运费用”中的数据要通过“交货方式”从左侧“配送公司收费表”中进行匹配查询。
这是一个典型的精确查询的例子，计算第1个数据的函数公式是“=VLOOKUP（H3,$B$2:
$D$6,3,FALSE）”。
小提示：
把最后一个参数从“TRUE”变更成“FLASE”，就是精确匹配。
而精确查询，就是查询数要与查询表第1列中的数据完全一致才能匹配提取，否则结果返回错误值“#N/A”。
点评：
Excel为我们提供了近20个有关“查找和引用”的函数，除了最常用的Lookup、Vlookup，还有Choos、Row、Colum、Index和Match等，大家可以通过函数的帮助查看具体的功能。
这些函数往往不是单独使用，可以与其他函数和Excel中的一些功能进行配合。
EXCEL函数VLOOKUP高级应用
VLOOKUP是处理根据条件查找对象的函数
这里有几个数组公式可以达到二个条件（在两列）或多个条件查找的功能
=VLOOKUP（A2&B2,IF（COLUMN（A1:
B1）=COLUMN（A1:
A1）,sheet1!
$A$2:
$A$110&sheet1!
$B$2:
$B$110,sheet1!
$C$2:
$C$110）,2,）
=INDEX（sheet1!
C$2:
C$110,MATCH（A2&B2,sheet1!
A$2:
A$110&sheet1!
B$2:
B$110,0））
=LOOKUP（2,1/（（sheet1!
$A$2:
$A$110=A2）*（sheet1!
$B$2:
$B$110=B2））,sheet1!
$C$2:
$C$110）
=SUMPRODUCT（（sheet1!
$A$2:
$A$110=A2）*（sheet1!
$B$2:
$B$110=B2）,sheet1!
$C$2:
$C$110）
前两个需按数组公式组合键。
符合两列或两列以上数据查找对应结果{=VLOOKUP（A3&C3&E3,CHOOSE（{1,2},基础表!
$A$3:
$A$123&基础表!
$C$3:
$C$123&基础表!
$E$3:
$E$123,基础表!
$D$3:
$D$123）,2,0）} 相当于ACE列合并形成数组：
基础表!
$A$3:
$A$123&基础表!
$C$3:
$C$123&基础表!
$E$3:
$E$123和基础表!
$D$3:
$D$123中查找取第2个数组如果使用vlookup（XXXXX,choose（{1,2,3,4...n},数组1,数组2...数组n）,K），还可以解决许多比这复杂的问题即可取第N个数组 只有两个数组时CHOOSE和IF相同
返回有重复列数据中所有有重复的内容
=IF（ROW（A1）>COUNT（IF（（COUNTIF（$A$2:
$A$7,$A$2:
$A$7）>1）*（MATCH（$A$2:
$A$7,$A$2:
$A$7,0）=ROW（$A$2:
$A$7）-1）,ROW（$A$2:
$A$7）））,"",INDEX（A:
A,SMALL（IF（（COUNTIF（$A$2:
$A$7,$A$2:
$A$7）>1）*（MATCH（$A$2:
$A$7,$A$2:
$A$7,0）=ROW（$A$2:
$A$7）-1）,ROW（$A$2:
$A$7））,ROW（A1））））
返回有重复列数据中所有内容，达到不重复。
=IF（SUM（1/COUNTIF（A$2:
A$7,A$2:
A$7））>=ROW（A1）,INDEX（A$2:
A$7,SMALL（IF（ROW（A$2:
A$7）-1=MATCH（A$2:
A$7,A$2:
A$7,0）,ROW（A$2:
A$7）-1,"0"）,ROW（A1）））,""）
另敬请注意看贴的网友，此楼的贴子如不注明为数组，即默认为数组公式。
顶一下,不能让他沉下去在B列中找出与A列重复的，{=OFFSET（$B$1,SMALL（MATCH（$A$1:
$A$8,$B$1:
$B$19,0）,ROW（1:
1））-1,0）}
解释SMALL（{10,20,30},1）中，得到的结果是“10”，SMALL函数是取得数据区域中指定的第几个最小值。
在这个公式中的“1”是指第一小值，也就是最小值。
公式...SMALL（MATCH（$A$1:
$A$8,$B$1:
$B$19,0）,ROW（1:
1））...是先用MATCH（）函数取得$A$1:
$A$8的数据在$B$1:
$B$19中的行数值，设A1在第12行，A2在第14行A3在15,A4在16,A5在3,A6在6行......，最后的结果是｛12,14,15,16,3,6｝，然后再套上SMALL（），后面的ROW（1:
1）等于1，也就是取得｛12,14,15,16,3,6｝中的最小值是“3”，“3”对应在B列第3行内容，即公式得到的第一个重复内容,向下填充,即可相应得到第二个第..个
顶一下,不能让他沉下去在B列中找出与A列重复的，{=OFFSET（$B$1,SMALL（MATCH（$A$1:
$A$8,$B$1:
$B$19,0）,ROW（1:
1））-1,0）}
解释SMALL（{10,20,30},1）中，得到的结果是“10”，SMALL函数是取得数据区域中指定的第几个最小值。
在这个公式中的“1”是指第一小值，也就是最小值。
公式...SMALL（MATCH（$A$1:
$A$8,$B$1:
$B$19,0）,ROW（1:
1））...是先用MATCH（）函数取得$A$1:
$A$8的数据在$B$1:
$B$19中的行数值，设A1在第12行，A2在第14行A3在15,A4在16,A5在3,A6在6行......，最后的结果是｛12,14,15,16,3,6｝，然后再套上SMALL（），后面的ROW（1:
1）等于1，也就是取得｛12,14,15,16,3,6｝中的最小值是“3”，“3”对应在B列第3行内容，即公式得到的第一个重复内容,向下填充,即可相应得到第二个第..个=SUM（（A2:
A100="渭塘实小"）*（C2:
C100="男"）*（D2:
D100-DATE（1958,1,1）>0）*（E2:
E100="本科"））
用VLOOKUP函数查找引用其他工作表数据和自动填充数据
VLOOKUP函数，在表格或数值数组（数据表）的首列查找指定的数值（查找值），并由此返回表格或数组当前行中指定列（列序号）处的数值。
VLOOKUP（查找值，数据表，列序号，[匹配条件]）
例如在SHEET2表中有全部100个学生的资料，B列为学号、C列为姓名、D列为班级，现在在SHEET1表的A列有学号，我们需要使用该函数，将SHEET2表中对应学号的姓名引用到SHEET1表的B列。
我们只需在SHEET1的B2输入以下公式 =VLOOKUP（A2,SHEET2!
$B:
$D,2,FALSE） （或者=VLOOKUP（A2,SHEET2!
$B$2:
$D$101,2,0），就得到了A2单元格学号对应的学生姓名。
同理, 在SHEET1表的C2输入公式 =VLOOKUP（A2,SHEET2!
$B:
$D,3,FALSE）,即可得到对应的班级.
VLOOKUP（A2,SHEET2!
$B:
$D,
升级会员 