我们对数据进行查询时,经常会使用 VLOOKUP 函数。但有时我们提取符合条件的结果是多个,而不是一个,这时候 VLOOKUP 就犯难了。
举个例子如下图,左侧 A1:C10 是一份学员名单表,现在需要根据 F1 单元格的“EH 图班”这个指定的条件,在 F2:F10 单元格区域中,提取该班级全部学员名单。
F1 的值是“EH 图表班”,需要在 F2:F10 单元格区域得到图表班相关成员的人名。接下来就分享一个函数查询方面的万金油套路:
INDEX+SMALL+IF
F2 单元格输入以下数组公式,按住 Ctrl+Shift 键不放,再按回车键,然后向下填充:=IFERROR(INDEX(B:B,SMALL(IF(A$1:A$10=F$1,ROW($1:$10)),ROW(A1))),””)
公式讲解
IF(A$1:A$10=F$1,ROW($1:$10)) 这部分,先判断 A1:A10 的值是否等于 F1,如果相等,则返回 A 列班级相对应的行号,否则返回 FALSE,结果得到一个内存数组:{FALSE;2;3;FALSE;FALSE;FALSE;FALSE;8;FALSE;10} 再来看这部分:SMALL(IF(A$1:A$10=F$1,ROW($1:$10)),ROW(A1))SMALL 函数对 IF 函数的结果进行取数,随着公式的向下填充,依次提取第 1、2、3……n 个最小值,由此依次得到符合班级条件的行号。随后使用 INDEX 函数,以 SMALL 函数返回的行号作为索引值,在 B 列中提取出对应的姓名结果。当 SMALL 函数所得到的结果为错误值 #NUM 时,意味着符合条件的行号已经被取之殆尽了,此时 INDEX 函数也随之返回一个错误值,为了避免公式返回一个错误值,最后使用 IFERROR 函数进行规避,使之返回一个空文本 ””。
其它说明
很多时候,一些朋友喜欢把 INDEX+SMALL+IF 的套路写成:=INDEX(B:B,SMALL(IF(A$1:A$10=F$1,ROW($1:$10),4^8),ROW(A1)))&”” 或 =INDEX(B:B,SMALL((A$1:A$10<>F$1)/1%+ROW($1:$10),ROW(A1)))&”” 这两个套路,通过引值真空单元格搭配 &””的方法,很巧妙的规避了错误值的出现,而且公式的长度得到了精简,是 IFERROR 函数未出现前处理错误值的常用技巧。只是当公式的查找结果为数值或者日期时,这个方法会把数值变成文本值,并不利于数据的准确呈现以及再次统计分析。比如一个简单的 SUM 求和,对于此类文本数据的统计都是麻烦的,原因是大部分统计函数都忽略文本值,不予计算。所以通常还是建议大家使用 IFERROR 函数来处理错误值。