作者:乔山办公网日期:
返回目录:excel表格制作
EXCEL中使用INDEX+SMALL+IF来进行一对多查询的组合方法
在EXCEL中处理数据查询时,经常要使用VLOOKUP或LOOKUP函数。但有时符合条件的查询结果有多条,这就需要用一对多查询,这时再使用VLOOKUP或LOOKUP就不是太容易能实现了,这种情况可以使用INDEX + SMALL + IF组合函数来实现这个功能。
如下图所示,要根据左边表中的"手机"这个条件,查询提取B列所有符合要求的数据。
使用INDEX + SMALL + IF组合函数的常用公式是:
在F2单元格中输入数组公式
=IFERROR(INDEX(B:B,SMALL(IF(A$1:A$8=F$1,ROW($1:$8)),ROW(A1))),""),按CTRL + SHIFT + ENTER完成输入,再向下填充公式即可。
公式中的IF(A$1:A$8=F$1,ROW($1:$8)),其机理是先判断A$1:A$8的值是否和F1单元格的值相同,如果相等则返回A列所对应的行号,否则返回FALSE。整个IF函数返回的数组公式结果是:{FALSE;2;FALSE;4;5;FALSE;FALSE;8}。
函数SMALL(IF(A$1:A$8=F$1,ROW($1:$8)),ROW(A1))的作用是对IF的返回值进行取数,随着公式的填充,依次提取第1、2、3、4……个最小值,由此得到符合要求的行号。
最后使用INDEX函数,以SMALL函数提取的行号作为索引值,在B列取出相应的数据。
随着公式向下填充,后面的行号已经不再符合要求,SMALL函数最后所得的结果是错误值#NUM,为了避免公式显示错误值,使用了IFERROR函数进行处理,使之返回一个空文本。