INDEX MATCH经典组合，从入门到放弃

卢子：函数与公式的神奇之处我认为在于同一道题目，可以有多种解法，曾经我试过查找符合条件的数据，写了30多种组合，不过其中有一半是凑数用的。在刚开始学习的时候，要尽量了解更多种用法，当你熟练以后就选择你认为最适合你的那一种方法就可以，其他可以忽略。每个时期你对同一问题的想法都会不停的改变，就如我刚开始很喜欢用Vlookup查找，接着发现Lookup好用，后来发现Index+Match组合变幻莫测，感觉很多事情离开这个组合都很难做到一样。

网友：既然你这么说了，就学学看这个组合有多经典，多认识几个函数也是好事。

卢子：其实公式就跟小朋友玩的积木一样，按需要的模型找到合适的小形状堆积而成。只要你将写公式当成在玩积木，在玩的同时就不知不觉学好公式了。下面通过几个例子看看公式是怎么堆积而成的。

Match前面我们已经知道，作用：获取项目在区域中的排位。现在来看看Index语法，是对区域的行列号交叉值的引用。

=INDEX(区域,行号,列号)

通过一个简单的小例子来说明Index的用法。

在查询表中查询产品的价格。

型号所在行号，为第8行。

=MATCH(B3,D1:D10,0)

规格所在列号，为第3列。

=MATCH(B7,D1:G1,0)

产品价格，就是第8行跟第3列的交叉单元格即70。

=INDEX(D1:G10,B4,B8)

将前面三条公式组合起来，经典的组合就这么诞生了。

=INDEX(D1:G10,MATCH(B3,D1:D10,0),MATCH(B7,D1:G1,0))

网友：原来利用这个组合，多条件查询这么简单。

卢子：现在来了解下这个组合的扩展运用。

例子1 对行列号汇总。

查找行号。

=MATCH(C3,E1:E10,0)

行汇总。

=SUM(INDEX(F2:H10,C4,0))

两个合并。

=SUM(INDEX(F2:H10,MATCH(C3,E2:E10,0),0))

刚开始不熟练这个组合，可以先拆开，然后再组合起来，这样便于理解。看一下帮助的说明：

注解

如果同时使用参数 Row_num 和 Column_num，函数 INDEX 返回 Row_num 和 Column_num 交叉处的单元格中的值。

如果将 Row_num 或 Column_num 设置为 0（零），函数 INDEX 则分别返回整个列或行的数组数值。若要使用以数组形式返回的值，请将 INDEX 函数以数组公式形式输入，对于行以水平单元格区域的形式输入，对于列以垂直单元格区域的形式输入。若要输入数组公式，请按 Ctrl+Shift+Enter。

注释在 Excel Web App 中，不能创建数组公式。

Row_num 和 Column_num 必须指向数组中的一个单元格；否则，INDEX 返回错误值 #REF!。

如果行列号或者列号设置为0，函数分别返回整个列或行的数组数值。这也就是说刚才为什么将Index的第3参数设置为0，就是为了引用正行的数据。

有了前面的基础，我们现在一步到位求列总计

=SUM(INDEX(F2:H10,0,MATCH(C8,F1:H1,0)))

例子2 对区域汇总

Sum函数的区域可以理解为

=SUM(开始单元格:结束结束单元格)，如=SUM(G16:H21)

只要将区域转换成上面的形式就行，知道开始跟结束单元格，就能汇总。

开始单元格为97。

=INDEX(F16:H24,MATCH(C16,E16:E24,0),MATCH(C18,F15:H15,0))

结束单元格为79。

=INDEX(F16:H24,MATCH(C17,E16:E24,0),MATCH(C19,F15:H15,0))

汇总的区域就是这两个数字组成的区域G17:H21，组合起来。

=SUM(INDEX(F16:H24,MATCH(C16,E16:E24,0),MATCH(C18,F15:H15,0)):INDEX(F16:H24,MATCH(C17,E16:E24,0),MATCH(C19,F15:H15,0)))

其实公式又好像是牛，光看牛是不知道牛内部的结构，只有操刀将牛分解才知道牛的内部构造。很多时候我们看到别人写的公式很长很长，不知道什么意思，就可以用庖丁解牛法解读。

来看Index跟其他函数的高级组合，让我们一起操刀，当一回庖丁，将牛大卸八块，好好理解下它的内部结构。

例子3 将左边的格式变身成右边的形式

提取不重复地市

=INDEX(A:A,SMALL(IF(MATCH($A$2:$A$19,$A$2:$A$19,0)=ROW($A$2:$A$19)-1,ROW($A$2:$A$19),4^8),ROW(A1)))&""

根据不重复地市获取所有区县对应值

=INDEX($B:$B,SMALL(IF($A$2:$A$19=$D2,ROW($A$2:$A$19),4^8),COLUMN(A1)))&""

网友：这么长，还没把他大卸八块，自己就先晕倒了。

卢子：当初我看到这两条公式也吓晕了，不过后来转念一想，公式拆开每个函数我都会，组合起来我应该也可以弄懂才对。

网友：也对哦，不能先被困难吓倒。

卢子：那我们就来庖丁解牛，呵呵。

先来看看1,2,3，庖丁解牛1。

=MATCH($A$2:$A$19,$A$2:$A$19,0)，得到每个地市在数据源第一次出现的位置，如1

=ROW($A$2:$A$19)-1，获取1到N的序列号，如2

=MATCH($A$2:$A$19,$A$2:$A$19,0)=ROW($A$2:$A$19)-1，将第一次出现的问题跟序号比较，如果一样就显示TRUE，否则显示FALSE，如3

接着看4,5，庖丁解牛2。

为了便于解读将MATCH($A$2:$A$19,$A$2:$A$19,0)=ROW($A$2:$A$19)-1设置为牛1

=IF(牛1,ROW($A$2:$A$19),4^8)，通过牛1知道，排位跟序号相同就是TRUE，不同就是FALSE。通过IF将相同的显示本身的序号，不同的显示4^8即65536，03版允许的最大行数，这一行通常是没有数据的，也可以将4^8改成任意一个比较大的数，如10000。最后获得由本身行号跟65536组成的区域，如4

=SMALL(IF(牛1,ROW($A$2:$A$19),4^8),ROW(A1))，SMAll(区域,N)，就是将数据升序排序，也就是说将第一次出现的地市的序号放在最前面，如5

经过这两次庖丁解牛，已经完成了80%的工作了，只需再解牛一次即可搞定。

最后看6,7，庖丁解牛3。

=INDEX(A:A,牛2)，获得序号的对应值，65536因为是空单元格，引用过来就是0，如6

=INDEX(A:A,牛2)&””，将引用过来的0转变成空文本，这样看起来美观点，如7

本来还想将公式大卸八块，现在才七块就搞定了，看来公式还不够长。

网友：卢子你还真幽默，解牛三次，大切成七块，厉害。

卢子：有了这次的剖解，下面这条公式就变得简单多了，重点看不同的地方即可。

=INDEX($B:$B,SMALL(IF($A$2:$A$19=$D2,ROW($A$2:$A$19),4^8),COLUMN(A1)))&""

$A$2:$A$19=$D2就是区域$A$2:$A$19跟$D2的比较，返回TRUE跟FASLE

IF($A$2:$A$19=$D2,ROW($A$2:$A$19),4^8)让符合条件的显示本身行号，否则显示4^8

SMALL(IF($A$2:$A$19=$D2,ROW($A$2:$A$19),4^8),COLUMN(A1))，因为公式是向右拖拉，COLUMN(A1)可以水平获得序号，从而得到前N个最小值

=INDEX($B:$B,SMALL(IF($A$2:$A$19=$D2,ROW($A$2:$A$19),4^8),COLUMN(A1)))&""让符合条件的值显示出来，不符合的显示空

网友：没想到这么长的公式还能听懂，真的佩服我自己。

卢子：通过这几回的讲解，公式与函数常见的用法，跟公式编写、解读的技巧都讲得差不多，剩下的就靠我们自己灵活运用了。要学会选择合适自己的方法。