Excel|VLOOKUP查找有缺点,试试INDEX+MATCH
2020年09月14日 20:18 发布来源: 原来,韩老师讲过很多VLOOKUP查询函数的使用,还专门将这个函数的所有用法录了一个专栏:虽然VLOOKUP深受大家喜爱,但查询却有两个不便之处: 1、查阅值(第一个参数)始终位于查找区域(第二个参数)的第一列; 2、从左向右查找容易,但从右向左查找需要IF或CHOOSE构建新的查找区域,就需要动用数组方法,数组方法查找在数据量较大的时候效率会较低。 今天,韩老师来讲述另一功能更强大的组合: INDEX+MATCH,这一组合可以不受以上两个条件的限制。
【MATCH函数】
MATCH函数查找指定项在单元格区域中的相对位置,即第几行第几列。 语法:MATCH(lookup_value,lookup_array, [match_type]) 中文语法:MATCH(指定项,单元格区域,[匹配方式]) match_type,即匹配方式,参数有三个: -1,查找小于或等于 lookup_value的最大值; 0,查找等于 lookup_value的第一个值; 1,查找大于或等于 lookup_value的最小值。 MATCH函数是查找函数最好的“搭档”,在与INDEX、VLOOKUP、HLOOKUP配合使用中起到重要作用。 举例 1、查所在行: 在B13输入公式:=MATCH(A13,A2:A10,0),即可查找不同姓名在第几行。 2、查所在列: 在B6输入公式:=MATCH(A6,A1:E1,0),即可查找产品在第几行。
【INDEX函数】
功能:查找单元格区域或数组常量中某行、某列或行列交叉点的值 语法:INDEX(array,row_num, [column_num]) 中文语法:INDEX(单元格区域或数组常量,数组中的某行,[数组中的某列]) 举例 1、查询不同销售业绩的销售员姓名: 公式:=INDEX(A2:A10,MATCH(D2,B2:B10,0)) 其中:MATCH(D2,B2:B10,0),是D2单元格销量业绩在所有销量业绩中位于第几行。 2、查询不同不同销量的产品名称: 公式:=INDEX(A1:E1,MATCH(A6,A2:E2,0)) 其中:MATCH(A6,A2:E2,0),是A6单元格销量在第几列。
【INDEX+MATCH组合用法举例】
1、查找业绩最高姓名 D2单元格输入公式: =INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10,0)) 即可得到最高业绩对应姓名。 公式解释:
2、查找业绩第一二三姓名
在E2输入公式: =INDEX($A$2:$A$10,MATCH(LARGE($B$2:$B$10,ROW(A1)),$B$2:$B$10,0)) 公式向下填充,可得前三名的姓名: 公式解释: 本公式中应用了LARGE函数,在B2:B10单元格查找第ROW(A1)大的值,公式在E2单元格时候,ROW(A1)返回值是1,向下填充时,会自动变为ROW(A2)、ROW(A3),即第二大、第三大的值,从而查找出前三名的姓名。 特别注意: INDEX+MATCH配合使用时,INDEX第一个参数区域,一定要和MATCH的第二个参数区域起始行一致,否则,会出现查找错位的情况。 3、查找行列交叉点的数值 C13输入公式:=INDEX(A1:E10,MATCH(A13,A1:A10,0),MATCH(B13,A1:E1,0)) 即可实现指定销售员指定产品的销量,如下动图: 公式解析 MATCH(A13,A1:A10,0):A13单元格姓名,在A1:A10区域中所在行。 MATCH(B13,A1:E1,0):B13单元格产品,在A1:E1区域中所在列。 INDEX(A1:E10,MATCH(A13,A1:A10,0),MATCH(B13,A1:E1,0)):A1:E10区域中,A13姓名所在行与B13产品所在列交叉点的值。 4、提取整行整列 (1)、查找指定姓名所有产品的销量 选中B13:E13区域,输入公式:=INDEX(B2:E10,MATCH(A13,A2:A10,0),0) 以Ctrl+Shift+Enter结束,如下图: 即可完成查找。 (2)、查找所有姓名指定产品的销量 选中H2:H10区域,输入公式:=INDEX(B2:E10,0,MATCH(H1,B1:E1,0)) 以Ctrl+Shift+Enter结束,如下图: 即可完成查找。 原创文章如转载请注明:转载自网 [ ] 需要保留本文链接地址: 分享到:
评论前必须登录!
立即登录