一文搞懂lookup、vlookup、hlookup函数与index、match函数的使用

lookup

函数解析

当需要查询一行或一列并查找另一行或列中的相同位置的值时，会使用其中一个查找和引用函数LOOKUP。

使用方式

向量形式

在一行或一列中搜索值。 如果要指定包含要匹配的值的区域，请使用这种形式。 例如，如果要在 A 列中向下搜索值到第 6 行。
语法：LOOKUP(①查找值，②查找值所在区域，③返回的结果)
②为单行区域或单列区域，查找值所在区域必须先排序，否则出错。
③可以省略
没有精确匹配对象时，返回小于等于目标值的最大值
重要: lookup_vector 中的值必须按升序排列：…, -2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE；否则，LOOKUP 可能无法返回正确的值。 文本不区分大小写。

数组形式

数组是要搜索的行和列（如表）中的值的集合。 例如，如果要在 A 列和 B 列中向下搜索值到第 6 行。 LOOKUP 将返回最接近的匹配项。 要使用数组形式，必须对数据排序。数组形式在于第一列和第一行中查找制定的值，并返回数组最后一行或最后一列的同一位置的值。
语法：LOOKUP(①查找值，②二维数组)
LOOKUP 的数组形式与 HLOOKUP 和 VLOOKUP 函数非常相似。 区别在于：HLOOKUP 在第一行中搜索 lookup_value 的值，VLOOKUP 在第一列中搜索，而 LOOKUP 根据数组维度进行搜索。如果数组的行列不相等，则lookup永远在少的行/列里进行查找。
使用 HLOOKUP 和 VLOOKUP 函数，可以通过索引以向下或遍历的方式搜索，但是 LOOKUP 始终选择行或列中的最后一个值。

vlookup

=VLOOKUP (要查找的项、要查找位置、区域中包含要返回的值的列号、返回近似匹配或精确匹配 - 指示为 1/TRUE 或 0/FALSE) 。
VLOOKUP 的秘诀在于组织数据，这样您查找的值（水果）位于要查找的返回值（金额）的左侧。

hlookup

=HLOOKUP(在首行中要查找的值, 要查找位置, 返回值的行号, 返回近似匹配或精确匹配 - 指示为 1/TRUE 或 0/FALSE)
如果 range_lookup 为 TRUE，则 查找范围 的第一行的数值必须按升序排列

index

返回表格或区域中的值或值的引用。
=INDEX(搜索范围,位置参数[行,列])
如果在构造的二维表格中仅使用一个参数会报错，在构造的一维表格中使用两个位置参数也会报错。
index(A1:B2,1,1) /index(A1:A10,6)

match

=MATCH(查找值,查找区域,匹配类型)

当匹配类型为0时，表示精确查找，函数返回该值所在区域中的位置
当匹配类型为1时，表示升序查找，查找小于或等于查找值的最大值并返回其所在位置。要求数据必须升序排列。
当匹配类型为-1时，表示降序查找，查找大于或等于查找值的最小值并返回其所在位置。要求数据必须降序排列。

应用示例

逆向查询、单条件和多条件查询通用公式：
=LOOKUP(1,0/(条件),目标区域或数组)
其中，条件可以是多个逻辑判断相乘组成的多条件数组。
=LOOKUP(1,0/((条件1)( 条件2) ( 条件N)),目标区域或数组)
公式说明：
①((条件1)( 条件2) ( 条件N))，所有条件满足返回TRUE，否则返回FALSE。
②以0/((条件1)( 条件2) ( 条件N))构建一个0、#DIV/0!组成的数组，避免了查找范围必须升序列排序的弊端。（因为True在运算时当作1，False在运算时当作0，所以0/TRUE返回0，0/FALSE返回#DIV/0!）
③再用1作为查找值，即可查找最后一个满足非空单元格条件的记录。

单条件逆向查询

根据姓名查询工号

多条件查询

根据姓名和部门查询办公室
使用index+match组合函数，该公式输入完毕后，不能直接按“Enter”键进行确认，而需要按“shift+ctrl+Enter”组合键来进行确认。

查询最后一次出现的数据


查询A列中的最后一个文本/数字/记录


根据简称查询全称


多个区间的条件判断


提取单元格内的数字

公式说明：
①-LEFT(A2,ROW($1:$99))用LEFT函数从A2单元格左起第一个字符开始，依次返回长度为ROW($1:$99)也就是1至99的字符串，添加负号后，数值转换为负数，含有文本字符的字符串则变成错误值。
②LOOKUP函数使用1作为查询值，在由负数、0和错误值构成的数组中，忽略错误值提取最后一个等于或小于1的数值。
③最后再使用负号，将提取出的负数转为正数。

实战题

数据A

考试日期	学科	姓名	分数
2019/1/30	数学	张三	92
2019/1/30	数学	李四	92
2019/1/30	数学	王五	82
2019/1/31	语文	张三	77
2019/1/31	英语	张三	73
2019/1/31	语文	李四	83
2019/1/31	英语	李四	88
2019/1/31	语文	王五	97
2019/1/31	英语	王五	97
2019/2/28	语文	张三	76
2019/2/28	数学	张三	82
2019/2/28	英语	张三	75
2019/2/28	语文	李四	74
2019/2/28	数学	李四	83
2019/2/28	英语	李四	99
2019/2/28	语文	王五	71
2019/2/28	数学	王五	77
2019/2/28	英语	王五	94
2019/3/30	数学	张三	74
2019/3/30	数学	李四	79
2019/3/30	数学	王五	96
2019/3/31	语文	张三	96
2019/3/31	英语	张三	93
2019/3/31	语文	李四	89
2019/3/31	英语	李四	98
2019/3/31	语文	王五	85
2019/3/31	英语	王五	85

完成以下题目：
1、case1:使用公式函数–通过数据A生成数据B
数据B：

		总分	语文	数学	英语
201901	张三
201901	李四
201901	王五

2、case2：使用公式函数–通过数据B生成数据C
数据C：

月份	姓名	学科	分数
201901	张三	语文
201901	张三	数学
201901	张三	英语
201901	李四	语文
201901	李四	数学
201901	李四	英语
201901	王五	语文
201901	王五	数学
201901	王五	英语

3、case3：使用公式函数–通过数据A计算如下人员出现最高分的月份

姓名	最高分出现的月份
张三
李四
王五

4、匹配如下数据，重量数据精度为0.1g
已知快递重量g与运费元对应数据关系：（ (0，24]>2,(24，40]>3,(40，50]>4,(50，100]>5,(100，200]>6,(200，500]>7,(500，800]>8,(800，1000]>9,(1000，1500]==>10。求以下快递重量对应的运费，除if以外的方法

序号	重量	运费
1	10
2	25
3	50
4	100
5	544
6	300
7	1200

答案：
1、考察要点：sumifs函数
总分==SUMIFS($E$2:$E$28,$B$2:$B$28,H4,$C$2:$C$28,I4)科目分数==SUMIFS($E$2:$E$28,$B$2:$B$28,$H4,$C$2:$C$28,$I4,$D$2:$D$28,K$3)2、考察要点：index与match组合使用=INDEX($K$4:$M$6,MATCH(I11,$I$4:$I$6,0),MATCH(J11,$K$3:$M$3,0))3、考察要点：lookup使用及注意事项=LOOKUP(1,0/(H23=$C$2:$C$28),$B$2:$B$28)
4、考察要点：lookup使用
设置辅助表

标准	运费
0	2
24	3
40	4
50	5
100	6
200	7
500	8
800	9
1000	10
1500	11

=LOOKUP(B15,$F$15:$G$24)