INDEX和MATCH两个函数搭着用,效率涨三倍
INDEX 和 MATCH 是 Excel 中比较常用的两个查找函数,堪称数据查询的好兄弟,这两个函数组合,能够完成 VLOOKUP 函数和 HLOOKUP 函数的全部查找功能。
使用 MATCH 函数精确位置
想知道某个数据是一列或一行数据中的第几个,则可以使用查找函数中的 MATCH 函数,因为它可以轻松确定某个数据在其所属行或列中的位置。
在 MATCH 函数中,第 3 参数“[match_type]”可以为 1、0 或 -1。
使用 MATCH 函数查找数据时,将会在数据区域中按从左到右,或者从上到下的顺序查找指定的数据,当找到匹配数据后,再返回数据所在的位置。
例如,下图所示表格中有两列数据,现在需要找出 A 列数据是否与 C 列中的数据重复,重复的数据在 C 列中处于什么位置,此时就可以使用 MATCH 函数轻松解决。
如果要判断下表中A2单元格中的数据是否与C列重复,则可在E单元格中输入公式“=MATCH(A2,$C$2 : $C$10,0)”,按【Enter】键即可。
向下填充公式,可查找到其他员工编号是否重复。
如果希望返回的结果为指定的数据,则可结合 IF 函数和 ISNA 函数。
例如,将上图返回的数字转换为“是”,将 #N/A 错误值转为“否”,只需要在 E2 单元格中输入公式“=IF(ISNA(MATCH(A2,$C$2 : $C$10,0)),' 否 ',' 是 ')”,然后向下填充公式即可。
MATCH 函数很少单独使用,一般都会与其他函数配合使用。
在使用 MATCH 函数时,还需要注意以下几点。
(1)MATCH 函数返回匹配值在 lookup_array 中的位置,而非其值本身。例如,MATCH(1,{2,3,1},0) 返回 3,即“1”在数组 {2,3,1} 中的相对位置。
(2)匹配文本值时,MATCH 函数不区分大小写字母。
(3)如果 match_type 为 0 且 lookup_value 为文本字符串,则可在 lookup_value 参数中使用通配符问号(?)和星号(*)。问号匹配任意单个字符,星号匹配任意一串字符。如果要查找实际的问号或星号,那么就在字符前键入波形符(~)。
INDEX 函数的两种形式
INDEX函数用于返回表或区域中的值或对值的引用,它有数组和引用两种形式。
1 数组形式
INDEX 函数的数组形式有 3 个参数,分别用来指定保存数据的区域、提取第几行的数据和提取第几列的数据。
在下图所示表格的 H2 单元格中输入公式“=INDEX(A2 : F11,5,4)”,按【Enter】键即可。
当第 1 参数的数据区域只有 1 行或 1列时,可以只为 INDEX 函数设置两个参数,这两个参数分别用于指定数据区域和返回区域在该区域中的位置。
例如,在 A列中显示了“销售 1 部”的前十名销售人员,如果要查找从上到下第5个销售人员,则需要在C2单元格中输入公式“=INDEX(A2 : A11,5)”,按【Enter】键即可。
如果需要提取数据区域中的某行或某列数据,则需要将第 2 参数“row_num”或第 3 参数“[column_num]”设置为 0(零)。
例如,在“销售排名”表格中提取销售 3 部前十名的销售人员的姓名,就需要先选择存放结果的 H2 : H11 单元格区域,输入公式“=INDEX(A2 : F11,0,4)”,按【Ctrl+Shift+Enter】组合键,将公式转换为数组公式即可。
2 引用形式
INDEX 函数的引用形式有 4 个参数,第 1 参数可以由多个单元格区域组成,第 2 参数和第 3参数与数组形式中的第 2 参数和第 3 参数相同,第 4 参数用来指定需要返回第几个区域中的单元格。
INDEX 函数的引用形式与数组形式的区别在于,引用形式可以有多个选择区域,而且可以指定返回第几个选择区域。
当然,只有一个区域时,两种形式没有区别。
INDEX 与 MATCH 双剑合璧,实现 3 种查询
在数据查询中,MATCH 函数单独使用的情况并不多,大多数情况下都是与其他函数搭配使用,特别是与 INDEX 函数的搭配,被称为是一对黄金组合,能够实现大多数情况下的数据查询,大大提高数据查询效率。
例如,下图为某公司员工信息表,要查找某员工的联系电话,除可以使用前面讲解的 LOOKUP 函数外,还可以使用 INDEX 和 MATCH 函数来实现。
如果要查找“王雪佳”的联系电话,则只需要在 I2 单元格中输入“王雪佳”,再在 J2 单元格中输入公式“=INDEX(G:G,MATCH(I2,B:B,0))”,按【Enter】键即可。
使用 INDEX 和 MATCH 函数组合查询非常灵活,不受原始数据列顺序的影响。
例如,要根据电话号码查找对应的员工姓名,在下图所示表格的 I2 单元格中输入需要查询的电话号码,再在 J2 单元格中输入公式“=INDEX(B:B,MATCH(I2,G:G,0))”,按【Enter】键即可查找出已知联系电话对应的员工姓名。