一招鲜，吃遍天之三：巧妙搭配的 INDEX 和 MATCH 函数

INDEX 函数和 MATCH 函数结合，能够实现任意方向的数据查询，使数据查询更加灵活简便。

1　使用 INDEX 函数进行检索

INDEX 函数能够在一个区域引用或数组范围中，根据指定的行号或（和）列号来返回值或引用。INDEX 函数的语法有引用和数组两种形式。数组形式语法如下。

第 1 参数为检索的单元格区域或数组常量。如果数组只包含一行或一列 , 则相应的 row_num 或 column_num 参数是可选的。如果数组具有多行和多列 , 并且仅使用 row_num 或 column_num，则 INDEX 返回数组中整个行或列的数组。

 2 参数 row_num 代表数组中的指定行，函数从该行返回数值。如果省略 row_num，则需要有第 3 参数 column_num。

第 3 参数 column_num 为可选参数，代表数组中的指定列，函数从该列返回数值。如果省略该参数，则需要有第 2 参数 row_num。引用形式语法如下。

第 1 参数 reference 是必需参数，为一个或多个单元格区域的引用，如果需要输入多个不连续的区域，则必须将其用小括号括起来。第 2 参数 row_num 是必需参数，为要返回引用的行号。第 3 参数 [column_num] 是可选参数，为要返回引用的列号。第 4 参数 [area_num] 是可选参数，为要选择返回用引用的区域。

如下图所示，A1:D4 单元格区域中是需要检索的数据。

以下公式为返回 A1:D4 单元格区域中第 3 行和第 4 列交叉处的单元格，即 D3 单元格的值 12。

以下公式为返回 A1:D4 单元格区域中第 3 行单元格的和，即 A3:D3 单元格区域的和 42。

以下公式为返回 A1:D4 单元格区域中第 4 列单元格的和，即 D1:D4 单元格区域的和 40。

以下公式返回 (A1:B4,C1:D4) 两个单元格区域中的第二个区域第 3 行第 1 列的单元格，即 C3单元格。由于 INDEX 函数的第 1 参数是多个区域，因此用小括号括起来。

根据公式需要，INDEX 函数的返回值可以为引用或是数值。例如，如下第一个公式等价于第二个公式，CELL 函数将 INDEX 函数的返回值作为 B1 单元格的引用。

而在以下公式中，则将 INDEX 函数的返回值解释为 B1 单元格中的数字。

2　单行（列）数据转换为多行多列

ROW 函数可以生成垂直方向连续递增的自然数序列，COLUMN 函数可以在水平方向上生成连续递增的自然数序列。ROW 函数和 COLUMN 函数组合可以生成指定规则的序列，结合 INDEX函数，可以实现将单列或单行数据转换为多行多列。

如下图所示，A2:A13 单元格区域为零件库存基本信息，从 A2 单元格起，每 3 个单元格为一组。要求将 A2:A13 单元格区域的单列数据转换为 C2:E5 单元格区域的形式，每个零件占 1 行 3 列。 

在 C2 单元格输入以下公式，将公式复制到 C2:E5 单元格区域。

公式中的“3*ROW(A1)-3+COLUMN(A1)”部分，计算结果为 1，公式向下复制时，ROW(A1)依次变为 ROW(A2)、ROW(A3)……公式计算结果分别为 4，7，10……即生成步长为 3 的递增数列。公式向右复制时，COLUMN(A1) 依次变为 COLUMN(B1)、COLUMN(C1)……计算结果为 2， 3……即生成步长为 1 的递增数列。“3*ROW(A1)-3+COLUMN(A1)”部分生成的结果如下图所示。

最后用 INDEX 函数，根据以上公式中生成的数列提取 A 列中对应单元格的内容，实现将单列数据转换为多行多列数据的目的。

3　使用 MATCH 函数返回查询项的相对位置

MATCH 函数用于根据指定的查询值，返回该查询值在一行（一列）的单元格区域或数组中的相对位置。若有多个符合条件的结果，MATCH 函数仅返回第一次出现的位置。其函数语法如下。

第 1 参数 lookup_value 为指定的查找对象。第 2 参数 lookup_array 为可能包含查找对象的单元格区域或数组，这个单元格区域或数组只可以是一行或一列，如果是多行多列则返回错误值 #N/A。第 3 参数 [match_type] 是可选参数，为查找的匹配方式。当第 3 参数为 0、1 或省略、-1 时，分别表示精确匹配、升序模式下的近似匹配和降序模式下的近似匹配。如果简写第 3 参数的值，仅以逗号占位，表示使用 0，也就是精确匹配方式，如“MATCH('ABC',A1:A10,0)”等价于“MATCH('ABC',A1:A10,)”。在精确匹配模式下，MATCH 函数的第 1 参数支持使用通配符。

例 1 ：当第 3 参数为 0 时，第 2 参数不需要排序。以下公式返回值为 3。其含义为在第 2 参数的数组中，字母“A”第一次出现的位置为 3。

例 2 ：当第 3 参数为 1 或省略第 3 参数值时，第 2 参数要求按升序排列，如果第 2 参数中没有具体的查找值，将返回小于第 1 参数的最大值所在位置。以下两个公式返回值都为 2，由于第 2 参数没有查询值 4，因此以小于 4 的最大值也就是 3 进行匹配。3 在第 2 参数数组中是第 2 个，因此结果返回 2。

例 3 ：当第 3 参数为 -1 时，第 2 参数要求按降序排列，如果第 2 参数中没有具体的查找值，将返回大于第 1 参数的最小值所在位置。以下公式返回值为 3，由于第 2 参数中没有查询值 5，因此以大于 5 的最小值也就是 6 进行匹配。6 在第 2 参数数组中是第 3 个，因此结果返回 3。

第 3 参数为 0 的精确匹配时，第 1 参数的查找值中可以使用通配符。