相信很多甲纯已经会比较熟练的使用VLOOKUP函数,进行数据的查找比对和返回取值了。
但是VLOOKUP函数有一个小缺点就是,查找比对完毕后,只能从左到右进行返回取值。
函数定义如此,改是没得改了。
所以每次要解决此类问题,要么要在查找取值区域的最左侧,加入用于查找的特征列:
要么用映射的方式,把取值区域映射到特征列的右侧:
操作起来都不是太舒服。
下面分别介绍2个函数
【一】MATCH函数
MATCH函数的作用就是“获取偏移量”,最形象的比喻就是:报数!
最关键的点是,MATCH函数的报数只能针对单行或者单列,即获取查找值在某一行或者某一列内,相对于该行(列)内第一个单元格(基点)的偏移量。
比如下面几个例子:
注意看公式:
(1)[D9]和[D10]【取值区域】不一样,导致基点不一样,结果也不一样。
(2)[D10]和[D11]分别以行[C:C]和列[5:5]作为【取值区域】。
(3)[D13]之所以错误,是因为MATCH取值区域只支持单行或者单列。
(4)[D13]之所以错误,是因为要查找的值,并不在取值区域内。
【匹配类型】有三种:-1、0、1:
(1)【匹配类型】:1 或省略,表示MATCH 函数会查找小于或等于【查找值】的最大值。【查找区域】中的值必须按升序排列,例如:...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE。
(2)【匹配类型】:0,表示MATCH 函数会查找等于【查找值】的第一个值。【查找区域】中的值可以按任何顺序排列。
(3)【匹配类型】:-1,表示MATCH 函数会查找大于或等于【查找值】的最小值。【查找区域】中的值必须按降序排列,例如:TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ... 等等。
所以-1和1其实都是模糊匹配,因为默认(不填写)匹配类型是1,但通常情况下采取的是精确匹配,所以建议甲纯拟写该函数的时候要写全,匹配类型明确要写上(精确匹配填写0)。
【二】INDEX函数
INDEX函数可以通俗的理解成为“偏移取值”函数,就是先指定一个取值区域,基点是取值区域的第一个单元格(最左上格),然后根据指定的行列偏移量,进行偏移取值。
一些例子:
一些注意点:
1、基点(取值区域最左上的单元格)的偏移量是(1,1),不是(0,0),这个跟坐标系原点不一样,如上面[B9]的取值结果。
2、偏移量必须是正数,不能是0,或者负数。
3、偏移的范围不能超过指定的【取值区域】,如上面[B11]公式出错。因为行、列偏移量(7,7)已经超出了整个【取值区域】
4、取值区域可以设置为单列,此时只需要指定行偏移量。同理可以设置为单行,此时只需要指定列偏移量。如上面[B12]和[B13]的公式。如果取值区域设置为多行多列,则行、列偏移量必须都填写。
【三】INDEX+MATCH函数的组合应用
上述2个函数的作用,一个是“获取偏移量”,一个是“偏移取值”,简直就是天造地设、珠联璧合。
下面说下INDEX+MATCH函数的组合应用
举下面实例,
要获取【小猪头】的【ERP号】,但是【ERP号】列在【姓名】列的左边,VLOOKUP函数无法直接使用。
先用MATCH获取查找值在特征列的行偏移量:
再使用INDEX在要取值的列上进行偏移取值:
把函数组合起来,其实跟VLOOKUP函数的作用是一致的,
但可以实现VLOOKUP函数无法实现的从右向左取值
如果要再智能一点,那就再加上MATCH函数自动获取INDEX函数想要进行偏移取值的所在行,公式如下:
操作动图:
怎么样?看着是不是有点绕?!
好了,下期再见!