NO.17：认识INDEX MATCH组合函数

什么叫做1+1>年薪100000？说的就是……

过了30岁还不会用这个组合函数，活该被辞退！

学了这个组合函数，工资立马翻三倍！

凭借这个组合函数，新同事一夜之间成功征服老板

VLOOKUP遇到它，立刻就被秒到渣都不剩！

看到这个神技，吓得我赶紧学习Excel

 

本章概要：

1，INDEX函数的语法

2，INDEX会点名

3，一个比VLOOKUP厉害百倍的组合

4，INDEX和VLOOKUP的区别

5，练手文件

1，

大家好，我们今天来聊INDEX函数。

 

Excel世界有400多个工作表函数，INDEX原本是其中最不起眼的几个之一。它出身贫苦，上天也没给它多大的本事，但给了他一副好脸蛋，曾经有个绰号因扎吉，后来因扎吉老了，它就给自己换了个新绰号叫李易峰……

 

咳，说正事。

 

照例先说INDEX的官方语法。

 

INDEX有两种语法形式，第2种基本用不着，所以看过就当没看过。

 

第一种：

=INDEX(array,row_num, [column_num])

 

翻译成汉语就是：

=INDEX(查找范围，查找范围的第几行？查找范围的第几列？)

 

第二种：

INDEX(reference，row_num，column_num，area_num)

 

翻译：看了也用不着的家伙，莫搭理。

 

……

 

2，

 

INDEX上学那会，凭着一副好脸蛋，被班主任任命为班长。这班长毕竟不是通过能力选上的，所以就没什么能力，只会简单的点点名。

 

举个小栗子。

         

如上图。

 

老师说，INDEX，你去把A列第2行的同学叫起来，上课看星星，对得起国家吗？

 

INDEX应一声，就去了。

 

他在心里想，去哪找？去A列。A列第几行？第2行。A列里的第几列？老师没说，那就默认是当前列吧。

 

=INDEX(A:A,2)

 

结果是看见星光

 

找完了，老师又说，INDEX，干得不错嘛，再去把第2行里第2列的同学叫起来，上课做春梦，对得起女朋友吗？

 

INDEX应了一声，又去了。

 

他在心里想，去哪找，第2行，那人在第二行的第几行？老师没说啊，老师为什么又没说？算了，就默认是当前行吧。第二行的第几列呢？第2列。

 

=INDEX(2:2,0,2)

 

结果是西门庆

 

这时候，INDEX忽然灵机一动，想起来将它创造出来的、那个懒到刷个牙都要花上三个月时间的微软工程师，不禁一阵恶心。接着，他想起来那工程师给他说过的一句话，当它的查找范围是单行时，如果省略第3参数，则第2参数默认为列，而不是原本的行。

 

于是它就换了种叫法：

 

=INDEX(2:2,2)

 

结果当然还是西门庆

 

西门庆：INDEX你有毒吧？没事当着武松的面喊我两次干嘛？

 

找完了，老师又说，班长，你去把班里第8行第3列的同学给我叫起来，上课谈恋爱——就算了，不知道人家是名花有主的吗？赵家人，他惹得起我惹不起。

 

INDEX脚不沾地，就去做事了。

 

=INDEX(A:C,8,3)

 

结果是燕青。

 

燕青被当众点了名，自尊心受到很大的伤害，第二天就带着他的同桌李师师私奔了。

 

 

 

3

 

INDEX班长做的久了，点名的事儿越来越麻利。但是，糟糕的是，他点名得让老师报坐标。老师本来就懒，时间久了，就有点受不了。

 

有一天，老师说，INDEX，你去把C列的李白给我叫起来，天天喝酒睡觉，早晚死在月亮里……

 

INDEX很高兴就去了。

 

他心想，去哪里找？去C列，C列里的第几行？老师没说，C列里的第几列？老师还是没说！老师为啥都没说？没说第几行第几列那去哪里找？…………

 

………？……

 

……？？…

 

……

……INDEX混沌了，他又想起那个刷个牙都要磨蹭3个月的微软工程师，他记得那人说过，没有坐标，他基本啥都干不了。

 

于是INDEX的班长职位就被撤了，后来的班长是班花VLOOKUP。

 

INDEX本来有点儿暗恋VLOOKUP，但男人嘛，事业为重，岂能在意儿女情长？经此一事，INDEX就对VLOOKUP由爱转恨，暗暗发誓早晚有一天要把VLOOKUP给……比下去。

 

过了没多久，班上来了个转校生，名字叫MATCH。

 

MATCH这家伙我们上一篇介绍过，它可以在单行或者单列的范围内，快速搜索指定值，并返回该值在查找范围内的首个匹配结果的位置序号——对INDEX来说，这简直就是天赐CP。

 

         

去C列把李白给抓出来？

 

=INDEX(C:C,MATCH('李白',C:C,0))

 

MATCH函数找出李白在C列的位置序号，也就是坐标了，INDEX直接按图索骥，就把李白给抓出来了。

李白：杜甫救我……啊……月亮……

INDEX对MATCH一见倾心，把所有的零花钱都请了MATCH喝酒（台湾五粮液），喝醉了就一起过夜，一夜鸟语花香之后，两人宣布出……    柜柜柜   ……组合，并打出口号，一个比VLOOKUP强大百倍的函数组合。

 

从此函数世界多了很多惊爆眼球的宣传标题。

 

什么叫做1+1>年薪100000？说的就是……

过了30岁还不会用这个组合函数，活该被辞退！

学了这个组合函数，工资立马翻三倍！

凭借这个组合函数，新同事一夜之间成功征服老板

VLOOKUP遇到它，立刻就被秒到渣都不剩！

看到这个神技，吓得我赶紧学习Excel

……

 

标题虽然十分雷人，但市场风气如此，也不好说什么。

 

好在INDEX和MATCH组合也确实是有真本事的。VLOOKUP能做的事，他俩都能做。VLOOKUP做起来比较困难的，甚至不能做的，他俩也能做。所谓比VLOOKUP强大百倍，也算是名副其实了。

 

还是举两个小栗子。

 

学过VLOOKUP函数后，我们都知道VLOOKUP在逆向查询上非常费力。

 

INDEX+MATCH函数就没有这样的烦恼。

         

如上图所示，需要在E列查询D2姓名的ID。

VLOOKUP函数是这样的：

=VLOOKUP(D2,IF({1,0},B:B,A:A),2,0)

这是一个数组公式，运算效率和书写体验都奇差无比。

而INDEX+MATCH组合是这样的：

=INDEX(A:A,MATCH(D2,B:B,0))

MATCH函数找出D2的值在B列的序列号，INDEX在A列直接按该序号点名抓人。

该公式书写简洁，运算效率也是函数里出类拔萃的。

……

         

如上图所示，需要查询Excel属于几班？

这是横向查询，VLOOKUP只能纵向查询，对此无能为力。

INDEX+MATCH轻松搞定：

=INDEX(1:1,MATCH('Excel',2:2,0))

结果是三班。

4，

那么，INDEX+MATCH的组合到底比VLOOKUP强在哪里呢？或者说，两者之间的差别到底是什么？

 

大体说起来，有3点。

 

1），

 

VLOOKUP的查询依据列只能是首列，换句话说，它只能在查询范围的首列查找某个匹配值，然后再依此向右偏移列数取结果。

INDEX+MATCH则不一样，有MATCH帮忙，它可以将任意列或行作为查询依据列/行，这就自由了。因为自由，所以强大。INDEX用实践证明了，什么叫权利诚可贵，爱情价更高，若为自由故，两者皆可抛……

 

2），

 

一般来说，INDEX+MATCH组合的计算效率优于VLOOKUP函数。

 

3），

 

打个响指，有句话先说在前头，对大部分人来说，第3点通常来说不重要，了解就好。

 

当查找区域是单元格时，VLOOKUP只能返回单元格的值，而INDEX返回的是单元格引用。

所谓值，就是“看见星光”、'李白'这样的单元格内的数据。而单元格引用，我们在数据类型里也讲过了，简单说就是单元格自身。而一个单元格包含了很多属性，比如单元格内的值、单元格的地址、单元格的行高、单元格的格式等等。

 

举个例子。

 

         

 

比如：=INDEX(A:A,2)

 

返回的是什么呢？

 

有朋友说返回的是A2单元格的“看见星光”啊。

 

准确来说，其实并不是。

 

INDEX返回的是A2单元格的引用。只是由于“值”是单元格的默认显示属性，所以我们眼睛看到的只是“看见星光”——脸红。谢谢您的眼里只有我。

 

比如我想知道A列的看见星光的单元格地址是什么？

 

=CELL('address',INDEX(A:A,2))

 

INDEX函数返回“看见星光”所在单元格，CELL函数计算其地址，结果为$A$2

看见星光所在的单元格的列宽是多少？

=CELL('width',INDEX(A:A,2))

 

再举一个实际应用的例子：按指定名称，动态引用员工照片。