Vlookup函数多返回值处理
使用Vlookup函数进行查找,提高了工作效率,但是Vlookup函数只能返回一个值,但是如果查找的结果是多个值,Vlookup函数就无能为力了,下面就该数组公式登场了。
题目如下:
使用EXCEL公式找出下列表格中,所有零件名称为A的零件数量:
零件名称 | 数量 |
A | 1 |
A | 2 |
B | 3 |
B | 4 |
A | 5 |
CC | 6 |
CC | 7 |
DD | 8 |
F | 9 |
A | 10 |
F | 11 |
K | 12 |
分析:
零件A对应的数量有1、2、5、10,仅仅使用Vlookup函数不能求取所有结果,可以使用INDEX的数组公式解决问题,公式如下:
{=INDEX($B$1:$B$13,SMALL(IF($A$2:$A$13=$G$2,ROW($2:$13),1000),COLUMN(A1)))&""}
这是H2单元格的公式,后面的公式直接向右拉动即可,当出现#REF!时表示没有更多的返回值了。
注意数组公式,需要在公式输入完成后,必须按下CTRL+SHIFT+ENTER。
本公式用了三层函数嵌套。
第一层IF函数,用一个值($G$2)在一个序列中($A$2:$A$13)查找,找到了返回行号(也是一个序列,可以简单理解为数组),如果没有找到,就返回一个非常大的值(1000)。
第二层SMALL函数,在返回的行号中,分别按照从小到大的顺序找到第一个、第二个、第三个值,COLUMN(A1)、COLUMN(B1)、COLUMN(C1)分别返回列号,也就是1、2、3这样的数值。
第三层INDEX函数,在数量列(B列)中,依据返回的行号,得到对应单元格的值。
再次提醒:公式输入完成后,必须按下CTRL+SHIFT+ENTER。