Index函数和match函数组合应用

在excel中，除了vlookup函数常用来查找引用外，index函数和match函数组合也可用来做查找引用工作，这组函数有效弥补了vlookup函数查找目标不在查找范围数据首列的缺陷。

图一：

工作：从《申报单位》中找到《成功单位》的“所在乡镇”和“所属科室”，填入《成功单位》“所在乡镇”和“所属科室”列。

Index函数

用途：返回表格或区域中的数值或对数值的引用。

Index有两种形式：数组和引用。数组形式通常返回数值或数值数组；引用形式通常返回引用。

语法：index（数据区域，第几行，第几列）。

解析：index函数就是从数据区域中，返回第几行第几列的单元格中的数值。就好比，在文件交换中心，有一个由许多方方正正一般大小的格子组成的文件柜，每个单位都拥有自己的一个专属小格子，例如我们XX局的文件在第2行，第3列的柜子里，我要拿文件的话就是干了index的活儿。具体到上面的图一工作就是从《申报单位》找到我们要的数值。怎样告诉index我们要找的数据在哪一行哪一列呢？让match来详说具体位置吧。

Match函数

用途：返回在指定方式下与指定数值匹配的数组中元素的相应位置。它查找匹配元素的位置，而非元素本身。

语法：match（查找目标值，查找数据表，匹配类型）

解析：查找目标值为需要在数据表中查找的数值，它可以是数值（或数字、文本或逻辑值）、对数字、文本或逻辑值的单元格引用，实际工作中通常为单元格引用。

查找数据表是可能包含所要查找的数值的连续单元格区域，可以是数组或数组引用，通常为一行或一列数据。

匹配类型为数字-1、0或1，说明excel如何在查找数据表中查找目标值。如果匹配类型为1，函数match查找小于或等于查找目标值的最大数值；如果匹配类型为0，函数match查找等于查找目标值的第一个数值；如果匹配类型为-1，函数match查找大于或等于查找目标值的最小数值。

具体到图例，第一个单位详细名称“济宁中农生物技术有限公司”在《申报单位》的第几行？计算公式（单元格L3中）为=MATCH(H3,B3:B31,0)，返回其在第16行。

图二：

“所在乡镇”为在《申报单位》表列标题的第几列？计算公式（单元格L2）为=MATCH(J2,B2:E2,0)，第一列为“企业名称”，第二列为“行业类别”，第三列为“所在乡镇”，第四列为“所属科室”。

Match函数单独使用其实没有什么用，它的存在主要是为了给他人做中介。常出现在Vlookup函数和Index函数中，以Index尤甚。我们现在就来看看match函数作为中介人如何给index函数介绍对象的吧。

图四：

在图四中，我们可以看到在J3单元格中输入index函数和match函数的组合公式：=INDEX($B$3:$E$31,MATCH($H3,$B$3:$B$31,0),MATCH(J$2,$B$2:$E$2,0))

解析：

$B$3:$E$31为在其中查找返回数值的数据区域，相当于文件交换中心的文件柜；

MATCH($H3,$B$3:$B$31,0)为“济宁中农生物技术有限公司”在《申报单位》表中的第几行；

MATCH(J$2,$B$2:$E$2,0)“所在乡镇”为在《申报单位》表列标题的第几列；

两个Match函数分别指明了查找目标值在第几行，第几列，相当于手指着文件柜，告诉index，“Hi，伙计，在这个格子里有你要的文件。”

Index函数公式中，第二个参数设定行，所以绝对引用（锁定）列；第三个参数设定列，所以绝对引用（锁定）行，即“要行锁列，要列锁行”。

把公式复制向右向下复制填充，得到下图五。

图五：

图五中我们可以看到有错误值#N/A。原因是《申报单位》和《成功单位》中的详细名称有略微不同，目测有的带（变更），故需用到模糊匹配。我们在J16单元格中输入修正公式=INDEX($B$3:$E$31,MATCH("*"&$H16&"*",$B$3:$B$31,0),MATCH(J$2,$B$2:$E$2,0))

图六：

解析：我们对index函数中的第二个参数，match函数的查找目标值进行模糊处理。

查找目标值"*"&$H16&"*"包含通配符“*”。Match函数第一个参数允许使用通配符“*”来表示包含的意思，把*放在字符的两边，即“*”&字符&“*”，其中&是对字符进行连接的意思。

我们把《申报单位》的详细名称列移动到表格的最后一列，结果如下图七，对查找数据没有任何影响，这就是说，我们不必要像vlookup函数要求的那样查找目标值必须在首列。

图七

现在我们来看看match和Vlookup函数组合如何实现查找引用功能的。match函数在Vlookup中主要是用于Vlookup的第三个参数，也就是确定列序号。请看下图八。J3单元格中的公式为=VLOOKUP($H3,$B$3:$E$31,MATCH(J$2,$B$2:$E$2,0),0)。

图八：

但是当我们把《申报单位》的“企业名称”列移动到表格的最后一列时，就会出现错误：

图九