excel中match函数_干货！excel中index—match查找函数实例讲解

在excel中&＃xff0c;运用查找函数是excel中进行数据统计分析最常用的操作之一&＃xff0c;说起查找函数&＃xff0c;很多人首先会想到vlookup函数&＃xff0c;其实excel中的查找函数有很多种&＃xff0c;比如lookup、index-match等&＃xff0c;尤其是index—match函数的使用范围比vlookup函数更加广泛&＃xff0c;在反向查找、横向查找等方面能够克服vloolup函数的不便&＃xff0c;提供更加容易理解的查找思路。下面就一起来看看吧。

一、index—match函数基本介绍。excel中match函数即匹配函数&＃xff0c;表示返回查找内容在查找范围的位置。match函数有三个参数&＃xff0c;第一个参数表示查找内容&＃xff0c;第二个参数表示查找范围&＃xff0c;第三个参数表示模糊查找或者精确查找。一般第三个参数选择0&＃xff0c;表示精确查找。(选择1或-1表示模糊查找&＃xff0c;查找前需要排序&＃xff0c;比较少用)当查找内容是文本时&＃xff0c;可以利用通配符“*”和“&＃xff1f;”进行查找。如下图所示&＃xff0c;在H2单元格中输入公式&＃61;MATCH(G2,$B$2:$B$19,0)&＃xff0c;结果会返回8&＃xff0c;表示查找的“关羽”在查找范围“B2—B19”单元格中第8个单元格。

图一

而index函数属于定位函数&＃xff0c;也有三个参数&＃xff0c;第一个参数表示引用或者查找的区域&＃xff0c;第二个参数表示行数&＃xff0c;第三个参数表示列数。后两个参数可以省略其中一个。如下图所示&＃xff0c;在F2单元格内输入公式&＃61;INDEX(A2:D19,4,2) 表示返回A2到D19区域中的第4行第2列&＃xff0c;结果为“曹仁”。同理&＃xff0c;在F6单元格内输入函数&＃61;INDEX(C2:C19,3)&＃xff0c;表示返回C2到C19单元格中的第3行&＃xff0c;结果为100103。此处查找区域只有一列内容&＃xff0c;所以列数可以省略。

图二

二、基本查找。前面介绍完index和match函数的基本用法之后&＃xff0c;此处开始介绍对index—match函数组合进行查找。如下图所示&＃xff0c;我要查询“关羽”和“周瑜”的成绩&＃xff0c;在G2单元格输入&＃61;INDEX($D$2:$D$19,MATCH(G2,$B$2:$B$19,0))&＃xff0c;然后下填充即可。初次学习时请注意我输入函数的顺序&＃xff0c;先输入match函数&＃xff0c;表示返回“关羽”在B2到B19单元格中的行数&＃xff0c;这一步计算结果为8。因为B2到B19区域是固定的&＃xff0c;所以选择B2到B19单元格后通过按F4键转换为绝对引用。然后在前面输入index函数&＃xff0c;这一步的意思表示返回D2到D19中的第8行数&＃xff0c;所以值为78。返回D2到D19区域也是固定区域&＃xff0c;此处也要绝对引用。选择绝对引用是为了向下拖动查找“周瑜”的成绩时查找范围不会发生变化&＃xff0c;如果只查找一个人的成绩&＃xff0c;绝对引用与相对引用没有区别。

动图三

三、反向查找。反向查找中&＃xff0c;使用vlookup函数需要用到数组函数调换顺序&＃xff0c;而使用index—match函数与基本查找没有区别。如下图所示&＃xff0c;根据学号查找姓名时&＃xff0c;在G2单元格中输入函数&＃61;INDEX($B$2:$B$19,MATCH(F2,$C$2:$C$19,0))&＃xff0c;向下拖动即可完成填充。函数的理解和前面并无区别。

动图四

四&＃xff0c;返回多个查找结果。如下图所示&＃xff0c;我要根据姓名查找学号、成绩、排名三项内容&＃xff0c;如何输入一个公式后通过自动填充查找呢&＃xff1f;在H2单元格中输入函数&＃61;INDEX(C$2:C$19,MATCH($G2,$B$2:$B$19,0))&＃xff0c;其实这个函数比较容易理解&＃xff0c;但是其中的相对引用和绝对引用需要特别注意。match函数第一个参数$G2&＃xff0c;对列绝对引用而对行相对引用表示查找的依据只在G列&＃xff0c;不会因为向右拖动填充的时候变成H列或者I列的内容。index函数的第一个参数C$2&＃xff0c;对列相对引用而对行相对引用表示查找的结果随着向右拖动填充时分别返回到学号、姓名、成绩列&＃xff0c;而向下拖动填充时始终选择某一列的第2行到第19行数据。

动图五

五、多条件查找。在下图中&＃xff0c;因为班级与学号都不唯一&＃xff0c;但是组合起来是唯一的&＃xff0c;所以查找成绩时需要组合起来进行多条件查找&＃xff0c;在G2列输入公式&＃61;INDEX($C$2:$C$19,MATCH(E2&F2,$A$2:$A$19&$B$2:$B$19,0))。在match函数中&＃xff0c;第一个参数使用连接符&将学号和姓名连接起来&＃xff0c;即E2&F2。结果是"一班100102"&＃xff0c;对于第二个参数$A$2:$A$19&$B$2:$B$19&＃xff0c;选中这部分参数按F9键可以看到结果是"一班100101";"一班100102"……。多条件查找的意思是把条件和查找区域都作为一个整体进行计算&＃xff0c;最后返回到成绩区域中match函数计算的行数。因为此处区域用连接符连接&＃xff0c;所以就构成数组函数&＃xff0c;需要按下ctrl&＃43;shift&＃43;enter键才可以计算出正确结果。

动图六

六、跨表查找。下图中一班、二班、三班以及查找四个工作表的格式都一样&＃xff0c;现在我们要在查找的工作表中进行进行多个工作表跨表查找。此处C2单元格的公式为&＃61;INDEX(INDIRECT(A2&"!C:C"),MATCH(B2,INDIRECT(A2&"!B:B"),0))&＃xff0c;输入完公式后向下填充即可。这个函数仍然属于index—match函数&＃xff0c;但是加入了indirect函数&＃xff0c;所以理解起来有一些难度。INDIRECT(A2&"!C:C")表示引用A2单元格内容指向的工作表的C列数据&＃xff0c;因为我们查找的结果是A列中一班、二班、三班分别指向的三个工作表的成绩。这里match函数第二个参数选择使用INDIRECT(A2&"!B:B")&＃xff0c;是因为此处查找的姓名在三个班级工作表的B列区域。往下拖动单元格填充时&＃xff0c;引用的工作表会随着行数变化而变化。

动图七

​

这就是本文介绍的index—match函数的查找功能&＃xff0c;这些查找方式涵盖了基本的日常需求&＃xff0c;函数公式也仅仅是两到三个函数嵌套&＃xff0c;理解起来并没有太大难度&＃xff0c;所以不妨现在就动手操作一下吧。