2018-09-14

一、基本用法（vlookup、hlookup、index、match）

1、vlookup

参数：vlookup（lookup_value，table_array，col_index_num，[range_lookup]）

vlookup(要查找的值，查找值所在的区域，查找第几列，精确还是模糊查找）

2、match

参数：match（lookup_value, lookup_array, match_type)

match(查找的值，查找的区域或数组，小于、精确、大于）

作用：在一个区域或数组中查找指定数值的位置，也就是查找的值所在哪一行或哪一列。

注意：其要查找的区域必须是一维数据而且是连续的，即只能包含一行或一列数据

3、index

参数：index(array,row_num,column_num)

index（指定区域，查找值所在行数，查找值所在的列数）

作用：根据指定的行数和列数，返回指定区域的值。（行列交叉的值）

一般与match搭配使用

4、offset(补充）

语法：offset(reference,rows,cols,[height],[width])

offset（参考位置，往上下移动几行，往右移动几列，[返回后显示几行]，[返回后显示几列]）,行列高度宽度移动都是根据参考位置来的，参考位置可以是一个单元格，也可以是一个区域，如果是区域，就以区域的形式移动，返回需使用数组的形式。

=OFFSET(B2,1,2,1,1）

=OFFSET(A1：B2,3,2,2,1)，以ctrl+shift+enter键结束

二、借助名称框查找

=VLOOKUP(D2,查找区域,2,0)

将查找的区域定义名称，在引用数据区域的时候，不需选取区域，直接输入已经定义好的名称即可，也可用F3调取出来，碰到数据较多，表格不在同一张表，函数又比较复杂的时候特别方便。

三、通配符模糊查找

“*”通配任意个字符

“？”通配单个字符

=VLOOKUP("*老师*",$A$2:$B$12,2,0)

四、查找返回多列数据

这个是利用column和row，也可以用match来实现，但column和row只能引用单元格或单元格区域，不能引用公式返回的结果，所以match刚好可以用来弥补。

=VLOOKUP($G2,$A$2:$E$9,COLUMN(B1),0)

=HLOOKUP(H$1,$A$1:$E$9,MATCH($G2,$A$1:$A$9,0),0)

五、查找指定区域

要求：左侧表格是数据源区域，包含姓名，1月，2月，3月，4月数据，需要在右侧根据姓名调取对应的数据，月份利用下拉列表形式，所以这里面就用column不能实现，需用到match。

=VLOOKUP($G2,$A$2:$E$9,MATCH($H$1,$A$1:$E$1,0),0)

六、多条件查找

要求：左表为产品销售额情况（产品列包含重复的产品），要求在F:H列的蓝色区域根据F2的产品名称查找所有销售情况。

难点：数据源区域中产品有多种，也就是需要找出1对多的信息。

这个在前面学习countif中也学习过，在最左侧添加辅助列用countif将产品编序（A-A1）

方法1：=IFERROR(INDEX($B$2:$E$15,MATCH($G$2&ROW(A1),$B$2:$B$15,0),MATCH(G$8,$B$1:$E$1,0)),"")

方法2：=IFERROR(VLOOKUP($G$2&ROW(A1),$B$2:$E$15,MATCH(J$8,$B$1:$E$1,0),0),"")

方法3：=IFERROR(HLOOKUP(M$8,$B$1:$E$15,MATCH($G$2&ROW(A1),$B$1:$B$15,0),0),"")

方法4：=IFERROR(VLOOKUP($G$2&ROW(A1),$B$2:$E$15,COLUMN(B1),0),"")

方法5：=IFERROR(OFFSET(INDEX($B$2:$B$15,MATCH($G$2&ROW(A1),$B$2:$B$15,0),1),0,1,1,3),"")，按ctrl+shift+enter结束

七、区间查询

要求：需要按照等级划分规则，将成绩划分到其对应的等级中

=VLOOKUP(B2,$J$2:$K$5,2)

=LOOKUP(B2,$J$2:$J$5,$K$2:$K$5)

注意引用的区域，其区间是按照升序排列的

八、动态图表

预先设置好查找的值，利用下拉列表实现多行数据变动。