韩老师曾经写过一篇：Excel | VLOOKUP一对多查找：不连续相同内容对应的多个数据一次提取，今天有朋友说：这个公式写起来好麻烦啊！
我们的“office天天学”群里有位高手朋友说他更喜欢INDEX+SMALL,那韩老师今天就把INDEX+SMALL这一神组合完成一对多查找的方法给大家讲一讲。
结果是这样的：
公式实现
在F2单元格输入公式：
=INDEX(B:B,SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1)))&""，以三键组合结束。
公式分步解析
我们以查找“张二”的消费记录为例来分析：
第一步：
IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B))
用IF函数，建立一新的数组，这一新的数组建立的规则是：
如果A$2:A$13区域中的单元格内容等于E2单元格内容，则返回该单元格所在的行，否则返回整个工作表的行数。
所以：此部分返回的数组是：
{1048576;3;1048576;1048576;1048576;7;1048576;1048576;10;1048576;1048576;13}
可以看到：凡是A列单元格内容等于张二的，返回的都是对应的行数，不等于张二的，返回的都是工作表的行数1048576。
第二步：
SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1)
在第一步形成的数组中，查找第第一小的数值。
用ROW(A1)做SMALL函数的第二个参数，即第几小。
ROW(A1)是一个动态的数值，公式往下填充一行，行数加1，即当公式在F2单元格时，是ROW(A1)，当公式填充到F3单元格是，是ROW(A2)，当到F4单元格时，是ROW(A3)……
这样，就在第一步的数组中找到了第1、2、3、4小的值，即3、7、10、13。
第三步：
INDEX(B:B,SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1)))
当公式在F2单元格时，返回B列第3行的值，即张二的第一次消费记录7478。因为公式中IF部分是数组计算，所以公式以三键组合结束。
公式向下填充，得到B列第7、10、13行的值。
第四步：
INDEX(B:B,SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1)))&""
在最后加上&""，这一步是容错处理。用空单元格与空文本合并返回空文本的特性，将超出结果数量的部分不显示出来。
网友评论