这里分享一个对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法：
【INDEX函数与MATCH函数嵌套使用技巧】=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))
以上是数组公式 ， 需三键结束 ， 下拉 。
推荐阅读
- 使用Excel函数计算职工退休年龄的方法
- Countif函数公式设置大全
- VLOOKUP函数的一个小技巧
- 使用VLOOKUP函数查找返回多列数据的方法
- 使用VLOOKUP函数提取符合条件的多个结果的方法
- 使用VLOOKUP函数实现从右向左查找的方法
- 使用VLOOKUP函数实现Excel数据按所处区间划分等级
- 查找引用类excel函数——VLOOKUP函数的使用方法
- Subtotal函数–筛选后数据统计的必备函数
- IF函数让你的选择不再困难