未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
INDEX函数与MATCH函数嵌套使用技巧

2017.09.28

      这里分享一个对INDEX函数+MATCH函数嵌套的小总结~~

      首先呢,INDEX函数+MATCH函数嵌套的最基本格式如下:

      index(序列1,match(值,序列2,0))

      其含义为:

      根据“值”在序列2中的位置,得出序列1中相同位置的值(模糊查找时,最后参数也可能是1、-1)。

      这个看似和vlookup函数很像,但它不需要源数据的严格对齐,也不需要查找值必须在首列,更为灵活一些~

      实例如下:

      这样的反向查找,其实用VLOOKUP、LOOKUP、INDEX+MATCH都是可以做到的,3个公式分别如下:

      VLOOKUP法:

      =VLOOKUP(E3,IF({1,0},$C$2:$C$9,$B$2:$B$9),2,FALSE)

      LOOKUP法:

      =LOOKUP(1,0/($C$2:$C$9=E3),$B$2:$B$9)

      INDEX+MATCH法:

      =INDEX($B$2:$B$9,MATCH(E3,$C$2:$C$9,0))

      这个基本实例中,序列1就是B2:B9,序列2是C2:C9,查找值是E3。以上,可见3者异同。

      除了以上的基本情景,INDEX+MATCH嵌套也可能会用在其他的怪异需求中。

      比如下面的例子:

      要将B列内容各自重复C列指定的次数,形成一个新的序列。

      首先,我们想到用INDEX来提取B列内容:

      =INDEX($B$14:$B$17,X)

      只是,这个X应该是什么呢?

      它得是一个在下拉的时候,前6个取值是1,然后2个2,然后5个3,然后3个4,这样的序列。

      我们自然想到,先将频次序列{6,2,5,3}变成累计序列{6,8,13,16},然后从0开始对它进行模糊查找,如下:

      =MATCH(ROW(A1)-1,{6,8,13,16},1)

      (下拉)

      这时,得到了这样一个序列:

      {#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,1,1,2,2,2,2,2,3,3,3}

      这个序列,离我们想要的序列还有两点不同:1都变成了错误值,后面的取值也都少了1;据此可以在后面+1,前面增加IFERROR:

      =IFERROR(MATCH(ROW(A1)-1,{6,8,13,16},1)+1,1)

      (下拉)

      此时,成功获得了想要的序列X,那么将它替换回原公式,即:

      =INDEX($B$14:$B$17,IFERROR(MATCH(ROW(A1)-1,{6,8,13,16},1)+1,1) )

      (下拉)

      如此,目的实现。

      最后,还有一个问题,即公式中的累计序列如何计算得到?

      这可以用offset选定区域并求和获得:

      =SUBTOTAL(9,OFFSET($C$14,,,ROW($1:$4),1))

      (此为数组公式)

      即将C14分别向下1、2、3、4格的区域求和。

      用以上公式替代掉{6,8,13,16},则最终的公式为:

      =INDEX($B$14:$B$17,IFERROR(MATCH(ROW(A1)-1,SUBTOTAL(9,OFFSET($C$14,,,ROW($1:$4),1)),1)+1,1))

      以上是数组公式,需三键结束,下拉。

本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报
从APP上打开文章,阅读全文并永久保存 查看更多类似文章
来自:oO三皮Oo  > 学习资料
举报
[荐]  原创奖励计划来了,万元大奖等你拿!
猜你喜欢
类似文章
Index Match嵌套那些事儿
Match函数 | 完美Excel
72个反向查找的公式套路,都看明白的就成精了!
一篇文章带你全面掌握Excel中的各种数据查询知识与技巧
史上最强巅峰对决,还不快来围观
Excel中的VLOOKUP函数,8种使用技巧与你分享
更多类似文章 >>
生活服务
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!