EXCEL表格只要提到数据查找匹配,VLOOkUP函数通常是我们的第一选择。但其使用过程中也有着诸多限制,比如(通常情况下只能从左向右查询,特殊用法也可以的,详细见:Excel教程——如何快速学习掌握VLOOKUP函数(进阶篇))只能从左往右查询,对数据格式的一致性要求比较高等。那有没有好的方法或者函数来替代VLOOKUP,同时适用性更加广泛呢。
没错,他就是INDEX+MATCH函数组合。
很多学Excel的朋友也听说过这个组合,但往往搞不清楚其原理,更别谈来使用了。今天我们就和大家谈谈INDEX+MATCH函数组合究竟如何使用,让大家轻松掌握!
一、函数认识
1、MATCH函数:返回指定数值在指定数组区域中的位置
1、1 名词解释
基本语法结构:=MATCH(查找值、查找区域、查找类型)
match函数一共有三个参数,首先我们来看第一个参数lookup_value,含义:需要在数据表(lookup_array)中查找的值。接着我们来看第二个参数lookup_array,含义:可能包含有所要查找数值的连续的单元格区域。第三个参数match_type,含义:排列顺序,一共有三个值,1代表升序排列,0代表任意顺序排列,-1代表降序排列。其中第一个和第二个必选参数,第三个为可选参数,默认为1。通常我们使用的都是精确查找,查找类型参数为0
1、2 案例详解
案例1:查找文本字符:查找字母C在所选区域内的顺序
输入公式=MATCH(B2,$A$2:$A$8,0); 具体操作如下:
上面查找的是文本内容的单元格,查找区域内没有大小关系,所以一般match函数中一般用精确查找,即最后个参数用0。
案例2:数值内容查找:如果是数值类查找,同样用match函数的精确查找可以找到排序
如下图:
match函数中的0要变化了,因为match函数的查找类型有0,-1,1三种,分别表示精确查找,大于查找,小于查找。看下面的步骤详细解释。
当用1类型的小于查找时,查找出来的结果是小于4.5的最大值的排序位置,也就是图中的4的位置4。
当用-1类型的大于查找时,查找的结果是大于4.5的最小值的排序位置,也就是图中的5所在的位置3。大于类型必须要用降序才能查找到结果。
当用0类型的精确查找时,要查找的值必须包含于查找的区域中,否则是错误结果。
2、INDEX函数:返回表或区域中的值或对值的引用
连续区域中index函数的公式格式是=index(array,row_num,column_num) ,其中array表示我们要引用的区域,row_num表示要引用的行数,column_num表示要引用的列数,最终的结果就是引用出区域内行列交叉处的内容。
基本语法结构:=INDEX(数据区域、行位置、列位置) 最终的结果就是引用出区域内行列交叉处的内容。
我们现在用一个简单的案例解释一下,如下图:输入公式=INDEX(A2:C7,2,3)
非连续区域的引用
输入公式=INDEX((A2:C3,A5:C6),2,3,2), 具体操作如下:
非连续区域中index函数的公式格式是=index((array_1,array_2,array_3....array_n),row_num,column_num,array_num) ,其中array表示我们要引用的非连续区域,其必须用小括号括起来,row_num表示要引用的行数,column_num表示要引用的列数,array_num表示第几个区域。
还是以上文中实例看一下index函数的应用。将该区域分成两个区域以表示非连续的区域。
引用到的是第二个区域A5到C6中第二行第三列交叉处单元格中的内容5,非连续区域与连续区域的不同处是要把分散的区域用小括号括起来,同时在函数括号最后面加入一个array_num表示引用第几个区域。上图基本可以完整解释非连续区域中index函数的使用。
二、NDEX+MATCH组合应用策略
基本结构:=INDEX(数据区域,MATCH函数确定的行位置,MATCH函数确定的列位置)
2.1 反向查找
【例1】如下图所示,要求根据产品名称,查找编号。
输入公式=INDEX(A2:A7,MATCH(E3,B2:B7,0))
2.2 双向查找
例2:根据姓名和身份证号码,查找具体数值
输入公式=INDEX($B$2:$F$14,MATCH($H2,$B$2:$B$14,0),MATCH(I$1,$B$1:$F$1,0))
2.3根据产品名称和入库时间,查询入库单价
输入公式=INDEX(C2:C7,MATCH(E4&F4,A2:A7&B2:B7,0))
由于公式中含有数组运算(一组数同另一组数同时运算),所以公式需要按ctrl+shift+enter三键完成输入。
点赞,或者收藏,疑惑是分享,都是对我的鼓励!是我坚持每天一更的动力!
如果关注,抑或是评论交流我将会笑上几分钟,因为被认同!
4000520066 欢迎批评指正
All Rights Reserved 新浪公司 版权所有