一对多查找，用 Vlookup 函数太Out了！

我们以前介绍过用Vlookup函数完成一对多查找：

【例】如下图所示，要求在F列查找“张明城”的个人消费记录 

数组公式：

{=VLOOKUP(F$1&ROW(A1),IF({1,0},$B$2:$B$10&COUNTIF(INDIRECT('b2:b'&ROW($2:$10)),F$1),$C$2:$C$10),2,)}

估计只有十分之一的同学能看懂上面的公式原理，真的需要这么复杂吗？NO! 其实我们可以不用Vlookup函数的：

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

公式解析：

• IF(B$2:B$10=F$1,ROW($2:$10))：如果B列的姓名和F1的姓名相同，就返回它的行号。不相同的返回FALSE

•  Row(a1)：是返回A1的行号1，如果向下复制会变为 Row(a2)，返回2，其实用它的目的是当公式向下复制时可以生成序号：1，2，3...然后取符合条件的第1个行号，第2个行号...

• SMALL(): 从符合条件的行号中从小到大，逐个提取符合条件的行

• INDEX() :根据取得的行号从C列提取值

• { }：数组公式（含有逐一运算的公式）需要按ctrl shift enter 输入大括号（一定要是自动生成的，不能手输入大括号）。

这么难，学这个公式有什么用？当然有用！

很多同学问，输入总表怎么能自动生成分表，而且修改总表分表也可以自动修改？用index match组合公式根据条件提取数据生成分表。

=IFERROR(INDEX(消费明细!A:A,SMALL(IF(消费明细!$B$2:$B$10=$B$1,ROW($A$2:$C$10)),ROW(消费明细!A1))),'')

注：这里用IFERROR函数屏蔽公式错误值

兰色说：如果工作中经常复杂的求和、核对、查找难题，建议学习一下Excel函数嵌套和数组运算。这些公式看着复杂无比，其实懂得函数的运算套路，一点都不难的。至少比上大学时学的微积分，不知道要简单多少倍。