我们对数据进行查询时, 经常会使用 VLOOKUP 函数. 但有时, 我们会碰到这样的问题, 提取符合条件的结果是多个, 而不是一个, 这时候 VLOOKUP 就犯难了.
举个例子, 如下图, 左侧 A1:C10 是一份学员名单表, 现在需要根据 F1 单元格的 "EH 图班" 这个指定的条件, 在 F2:F10 单元格区域中, 提取该班级全部学员名单.
今天说一个函数查询方面的万金油套路: INDEX+SMALL.
F2 单元格输入以下数组公式, 按住 Ctrl+Shift 键不放, 再按回车键, 然后向下填充:
=INDEX(B:B,SMALL(IF(A$1:A$10=F$1,ROW($1:$10),4^8),ROW(A1))),"")
这个公式看起来可就比上面那个 VLOOKUP 的解法苗条养眼多了, 坦白的说, 很搭俺星光十年后的匪号 -- 小清新.
公式讲解
IF(A$1:A$10=F$1,ROW($1:$10),4^8)
这部分, 先判断 A1:A10 的值是否等于 F1, 如果相等, 则返回 A 列班级相对应的行号, 否则返回 4^8, 也就是 65536, 一般情况下, 工作表到这个位置就没有数据了.
结果得到一个内存数组:
{65536;2;3;65536;65536;65536;65536;8;65536;10}
SMALL 函数对 IF 函数的结果进行取数, 随着公式的向下填充, 依次提取第 1,2,3......n 个最小值, 由此依次得到符合班级条件的行号.
随后使用 INDEX 函数, 以 SMALL 函数返回的行号作为索引值, 在 B 列中提取出对应的姓名结果.
当 SMALL 函数所得到的结果为 65536 时, 意味着符合条件的行号已经被取之殆尽了, 此时 INDEX 函数也随之返回 B65536 单元格的引用, 结果是一个无意义的 0, 为了避免这个问题, 可以在公式后面加上一个小尾巴 &""利用 &"" 的方法, 很巧妙的规避了无意义 0 值的出现, 只是当查找结果为数值或日期时, 这个方法会把数值转变为文本值, 并不利于数据的准确呈现以及再次统计分析.
来源: http://www.bubuko.com/infodetail-3092672.html