excel函数怎样一对多查询

1、打开excel2010，打开如图所示表格，我们需要查询美国的大学。

2、我们首先使用IF函数查询美国大学是位于第几行。

=IF(C2:C13=E1,row(C2:C13))

如果C2:C13中哪一个单元格等于E1（E1也就是“美国”），

就利用row(C2:C13)，返回那一格所在的行数。

这实际上是一个数组公式，返回的是一组数

在编辑栏选中“IF(C2:C13=E1,row(C2:C13))”，按F9，就可以看到计算的结果

{2;3;4;5;FALSE;FALSE;FALSE;FALSE;10;FALSE;FALSE;FALSE}

意思是第2，3，4，5，10行是“美国”，其余行不是“美国”，显示为FALSE

3、注意：因为公式完成后我们需要向下复制，所以需要把单元格相对位置改成绝对位置。IF(C2:C13=E1,row(C2:C13))需要改成

IF($C$2:$C$13=$E$1,ROW($C$2:$C$13))

4、接下来我们怎样把“美国”所在的行挑选出来，

即把第2，3，4，5，10行挑选出来，忽略掉FALSE，

我们可以使用SMALL函数

SMALL(IF($C$2:$C$13=$E$1,ROW($C$2:$C$13)),ROW(A1)）

SMALL函数可以求出一组数中的第K个最小值（可以是第一个最小值，可以是第2个最小值，也可以是第３个最小值，也可以是第Ｋ个最小值）

但SMALL只处理数值，所以非数值的FAlSE就会被剔除，

这样我们就能够把“美国”所在的行数挑选出来。

5、注意：ROW(A1)在SMALL函数中只是为了得到一个递增序数

ROW(A1)=1,往下拖动复制的时候，

ROW(A2)=2，ROW(A3)=3，ROW(A4)=4，

所以ROW(A1)中的A1单元格并没有什么特殊的意义，不是非得用A1，其实使用B1,C1,D1,就算是Z1结果也是一样，因为使用ROW（Z1）函数得出的结果也是１，往下拖动也会递增序数。

6、通过SMALL(IF($C$2:$C$13=$E$1,ROW($C$2:$C$13)),ROW(A1)）

把“美国”所在的行数挑选出来之后，

我们就可以得用INDEX函数把美国的大学挑选出来。

INDEX(B:B,SMALL(IF($C$2:$C$13=$E$1,ROW($C$2:$C$13)),ROW(A1)))

查询B:B，也就是查询B列（大学名称列）

美国大学所在行我们已经挑选出来，也就是

SMALL(IF($C$2:$C$13=$E$1,ROW($C$2:$C$13)),ROW(A1)）

7、＝INDEX(B:B,SMALL(IF($C$2:$C$13=$E$1,ROW($C$2:$C$13)),ROW(A1)))

这是一个数组公式，完成之后需要按“Ctrl+Shift+Enter”确认。结果变成

{=INDEX(B:B,SMALL(IF($C$2:$C$13=$E$1,ROW($C$2:$C$13)),ROW(A1)))}

8、向下拖动复制公式，美国大学查询出来后会出现“#NUM！”，这是因为在前面得出的数组{2;3;4;5;FALSE;FALSE;FALSE;FALSE;10;FALSE;FALSE;FALSE}中

FALSE是无法被SMALL函数处理的。所以最终结果会提示“#NUM！”。

我们需要添加一个IFERROR函数来解决这个问题

=IFERROR(INDEX(B:B,SMALL(IF($C$2:$C$13=$E$1,ROW($C$2:$C$13)),ROW(A1))),"")

意思就是如果刚才的函数出现错误，则显示为“”，也就是显示为空白。

添加IFERROR后，按Ctrl+Shift+Enter”确认，

往下拖动复制，就会发现“#NUM！”变成了空白。

9、如果大学的数目有所增加，变成前100排名，或者前1000排名，我们只需要把函数稍微更改一下就可以了，把$C$2:$C$13改成$C$2:$C$10000，函数可以统计$C$3:$C$10000范围内的数据，右边的公式预先往下拖动，左边添加数据后，右边就会自动计算出结果。

至此，我们就完成了excel函数一对多查询。