在EXCEL中,有一部分人只使用过VLOOKUP函数来查找和引用数据,因为VLOOKUP函数确实非常强大,引用数据很方便。

但是VLOOKUP函数也有碰到不方便使用的情况:

1、比如逆向查找,虽然可以配合IF{1,0}完成逆向查找,但是只能逆向查找一列结果,还想要查另一个结果,又得重新编写公式;

2、要同时满足多个条件并且逆向查找多列结果,VLOOKUP很不方便。

3、还要很多功能VLOOKUP无法完成。这个时候,就可以凭借 INDEX+MATCH函数组合来完成以上的任务。

▌先介绍下INDEX函数,它能通过输入第几行,第几列找到"数据区域"相应的行列交叉内容。它的公式模板:=INDEX(数据区域,行数,列数)。通过一个简单的案例讲解,来清楚知道INDEX函数是干嘛用的。如图1:我们要引用“三月小红”的数据,那么在G2单元格输入公式=INDEX($A$1:$D$6,2,4)。$A$1:$D$6是数据源,2表示"小红"在数据源的第2行,4表示"三月"在数据源的第4列,引用数据区域里第2行和第4列交叉的内容,得到203。

Office Excel组合函数INDEX和MATCH函数,真的比VLOOKUP函数好用?分情况,未必

图1

但是大家有没有发现在"图1案例"中,行数和列数都是我自己一个一个手动输的,这样的话我拖动公式的时候就不会自动变换行、列数,这样引用不灵活。

所以我们需要另一个函数MATCH函数和INDEX函数组合就能完成公式自动变动。

▌先介绍下MATCH函数,它是匹配函数,"返回的结果数字"表示"查找值"在某一行或某一列排在第几个,此函数的"数据区域"参数只能选1行或1列区域。公式模板:=match(查找值,1行或1列的查找区域,查找类型)。查找类型有 0:精确查找; 1:查找小于或等于查找值的数(升序排列); -1:查找大于或等于查找值的数(降序排列)。如图2:

Office Excel组合函数INDEX和MATCH函数,真的比VLOOKUP函数好用?分情况,未必

图2

INDEX函数有查找引用内容的功能,MATCH函数可以通过"查找值"自动更改定位数,这样我们就可以把MATCH函数嵌套进INDEX函数中,用于INDEX函数的第二参数行数和第三参数列数。

▌INDEX+MATCH函数组合使用案例解析

▼一、同一行多条件查找、引用数据。

公式模板:=INDEX(数据区域,MATCH(查找值1,查找区域1,0),MATCH(查找值2,查找区域2,0))。

在I2单元格输入公式=INDEX($B$2:$E$6,MATCH(H2,$A$2:$A$6,0),MATCH(G2,$B$1:$E$1,0)),加上“绝对引用”防止数据偏移。如图3:

Office Excel组合函数INDEX和MATCH函数,真的比VLOOKUP函数好用?分情况,未必

图3

多条件正向查找、引用,VLOOKUP配合MATCH也可以完成,i2单元格输入公式=VLOOKUP(H2,$A$1:$E$6,MATCH(G2,$A$1:$E$1,0),0)。大家觉得哪条公式更简便,可以自行选择。

▼二、单个条件逆向查找、引用多列结果。

公式模板:=INDEX(数据区域,MATCH(查找值1,查找区域1,0),MATCH(查找值2,查找区域2,0))。用INDEX+MATCH组合就可以轻松完成单条件逆向查询。

在H2单元格输入公式=INDEX($A$2:$E$6,MATCH($G2,$D$2:$D$6,0),MATCH(H$1,$A$1:$E$1,0)),行参数的$G2要加绝对引用,防止后期向右拖动公式时列方向会偏移错误;列参数的H$1要加绝对引用,防止后期向下拖动公式时行方向会偏移错误。然后H2单元格向右,向下拖动就可以了。如图4:

Office Excel组合函数INDEX和MATCH函数,真的比VLOOKUP函数好用?分情况,未必

图4

用VLOOKUP函数就没办法一次性完成3条逆向结果的查找,只能在第2行每个单元格输入公式,如H2输入=VLOOKUP($G3,IF({1,0},$D$2:$D$6,$C$2:$C$6),2,0),其中$C$2:$C$6是结果列,根据不同的结果选择不同列。4个查找项就要输入4条公式,大家认为哪个公式更方便呢?

▼三、多个条件逆向查找、引用多列结果。

公式模板:=INDEX(数据区域,MATCH(查找值1,查找区域1,0),MATCH(查找值2,查找区域2,0))。用INDEX+MATCH组合就可以轻松完成多条件逆向查询,输完公式后必须按CTRL+Shift+回车三键,因为是数组模式。

I2单元格输入公式=INDEX($A$2:$E$6,MATCH($G2&$H2,$D$2:$D$6&$E$2:$E$6,0),MATCH(I$1,$A$1:$E$1,0)),输完公式后必须按CTRL+Shift+回车三键。行、列参数里都要加“绝对引用”,防止偏移。如图5:

Office Excel组合函数INDEX和MATCH函数,真的比VLOOKUP函数好用?分情况,未必

图5

这里VLOOKUP就不需要再写了,因为用VLOOKUP很麻烦。

▼四、查找值加通配符星号 * 来模糊查找、匹配数据。

公式模板:=INDEX(数据区域,MATCH(“*”&查找值&"*",查找区域,0),列数)。

match函数能通过通配符星号*来匹配相似内容,查出对应的内容项。E2单元格输入公式=INDEX($B$2:$B$6,MATCH("*"&D2&"*",$A$2:$A$6,0),1)。列参数输入数字1表示引用数据区域$B$2:$B$6当前1列。

Office Excel组合函数INDEX和MATCH函数,真的比VLOOKUP函数好用?分情况,未必

图6

▼五、按要求的条件查找最后一个满足此条件的内容信息。

公式模板:=INDEX(数据区域,MATCH(1,0/($C$2:$C$6>=7000),1),1)。

找业绩大于等于7000的最后一个人是谁,在C8单元格输入公式=INDEX($B$2:$C$6,MATCH(1,0/($C$2:$C$6>=7000),1),1)。MATCH的第3参数写1是为了启动模糊匹配,功能同LOOKUP,最后要按CTRL+Shift+回车三键。如图7:

Office Excel组合函数INDEX和MATCH函数,真的比VLOOKUP函数好用?分情况,未必

图7

▼六、用 Match函数求当前月份是第几季度

利用MATCH函数的第3参数写1,升序排列,模糊匹配和查找值相同或小于查找值的最大值,原理同LOOKUP二分法,计算当前月份对应的第几季度。在B2单元输入公式=MATCH(MONTH(A2),{1,4,7,10},1)&"季度" 。如图8:

Office Excel组合函数INDEX和MATCH函数,真的比VLOOKUP函数好用?分情况,未必

图8

总结:INDEX和MATCH函数组合在逆向查找、引用方面确实比VLOOKUP厉害很多。但个人使用习惯,如果能用VLOOKUP轻松完成查找、引用的任务,优先使用VLOOKUP函数,如果VLOOKUP完不成的,可以考虑用INDEX和MATCH函数的组合完成。不知道朋友们更喜欢哪种用法?

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注