我们都很清楚如何使用VLookup进行单一的匹配查询,那么有没有办法实现多重匹配查询呢?比如下图所示:我们需要在F2:F4的区域分别返回查询A2:B6区域,对应产品A在B列的第1,2,3个值。
那么怎么实现呢?我们接下来进行一步步分解!
构建INDEX, AGGREGATE的组合函数!
最外层Index函数:Index函数可以返回一个序列中指定位置的数据。如图,我们可以返回在B2到B6区域,排序第1的数。
第2个A出现在第4行,因此我们继续输入函数,将之前的1换成4,就能返回222了!
那么接下来的问题就转化为:如何智能的返回A在序列A2:A6的序号1,4,5呢? 接下来就要隆重推出Aggregate函数了!它于数组和ROW函数结合就能实现这个目的!
这个看似复杂的函数,我们怎么理解它呢?
它的第一个参数function_num, 通过输入不同的值,我们可以选取不同的计算逻辑,这里因为我们要按顺序从小到大选择匹配的值,因此我们选择SMALL函数,它的参数为15。
第二个参数option,可以让我们选择是否忽略隐藏和错误值,此时我们选择3输入。
第三个参数需要录入分析的数组,我们在这里使用了一个很复杂的数组公式,其中($A$2:$A$6=$E$2)会产生一个(TRUE,FALSE,FALSE,TRUE,TRUE)的数组,将其除以自身,会转换成一个 (1, #div0, #div0, 1, 1)的新数组,让我们再看(ROW($A$2:$A$6)-ROW($A$1)这个数组,它会形成一个从1开始的顺序序列 (1,2,3,4,5),与之前的数组相乘,便得到了下图H2:H6的数组序列了!
将这个顺序序列嵌入到之前的Aggregate函数中作为第3个参数,这三个参数就很好的确定了需要分析的集合为(1, #div0, #div0, 4, 5),从当中从小到大选择排位第k的数据,且忽略隐藏和错误!那么这里的k就是我们的第4个参数!
这里的k我们使用ROWS($A$2:A2)函数,rows函数能返回所选区域的行数,通过锁定$A$2, 我们通过下拉可以增加区域的行数,进而让k能够从1变成2,变成3。。。。。。
整合以后就是完整版的Aggregate函数了!
最后我们把aggregate函数替换之前Index函数的第二个参数,就形成了最终公式!怎么样,快来练习吧!
更进一步
在Excel2016里,还有一种更为简单的办法,让我们在下期节目介绍!
联系客服