Excel中的黄金搭档match+index函数,实现对数据的高级查找

点击上方「蓝字」关注我们

在excel中的查找函数中vlookup应该是用得最多的一个函数。

vlookup是最广为人知的查找函数，但它有一个致命的缺陷：只能查找返回值在查找列右侧的数据，并且查找列必须在首列。而index+match 函数组合完美地解决了vlookup的这个痛点。

相对于vlookup函数，index + match具有以下三个优点：

1、查找更加的灵活

index + match可以实现向左向右向上向下四个方向都可以进行查找。

2、查询效率更高

相对于vlookup固定的列号，用index + match组合函数，数据执行插入或删除列时候不会破坏公式。

3、性能更好

在处理大数据量时，计算速度通常比vlookup更快。

一、函数语法解析

1、match 函数

match 函数的作用是返回指定数值在指定数组中的位置，返回值为一个整数。

语法：=match(查找值, 查找区域, [匹配类型])

查找值：你要找的东西（如“小明”）。

查找区域：在哪一行或哪一列中找（必须是一维的，即必须是一列或一行，如：A1:A10或A1:G1）。

匹配类型：

0：精确匹配（最常用）。

1：小于，查找区域必须升序排列。

-1：大于，查找区域必须降序排列。

示例：

=match("小明", A1:A10, 0) 

返回“小明”在A1:A10这个区域中排第几行。

如下图数据所示，返回值为3。

2. index 函数

index 函数地作用是返回表格或区域中指定行和列交叉处的值。

语法：=index(数组, 行号, [列号])

数组：你要返回值的范围。

行号：在数组中的第几行。

列号：在数组中的第几列（如果数组是多列的话）。

示例：

=index(A1:A10, 3) 

返回A1:A10区域中第3行的值。

如下图数据所示，返回值为“小明”。

二、组合公式使用案例

将match和index两者结合，用 match 找位置，用 index 取数据：

=index( 要返回值的列/区域 , match( 查找值 , 查找列/区域 , 0 ) )

案例 1：

基础的单条件查找（代替vlookup）

场景：根据“姓名”查找对应的“得分”。（查找列在左侧，返回值在右侧）

公式：

=index(B2:B11,match(C2,A2:A11,0))

• match(C2,A2:A11,0)：在A2:A11中找c2（比如“小强”）在第几行，结果是4。

• index(B2:B11, 3)：返回B2:B11区域中第4行的值，即99。

案例 2：

向左查找（vlookup 做不到的）

场景：根据“姓名”查找“学号”。（查找列在右侧，返回值在左侧）

需求：根据D2单元个中选择的姓名，找到对应的学号。

公式：

=index(A2:A11,match(D2,B2:B11,0))

通过match在B列找到D2单元格中姓名对应的位置（如：D2为“小强”，则对应位置为4），然后通过index函数从A列取第4行的值。

案例 3：

多条件查找（双条件）

场景：在多个班级有重名的学生，需要精准获取指定班级里指定姓名的得分。即根据班级和姓名两个条件查询得分。

公式（数组公式，早期excel需按ctrl+shift+enter，excel 365/2021直接回车）：

=index(D2:D21,match(1,(B2:B21=G3)*(C2:C21=G2),0))

解析：

1、(B2:B21=G3)*(C2:C21=G2)会生成一组0和1组成的数组（只有当两个条件都满足时为1）。

2、match(1, 这个数组, 0)找到同时满足两个条件的那一行。

3、index从D2:D21列取出该行的值。

案例 4：

二维表交叉查询

场景：学生成绩表，行是姓名，列是科目。需要查询某学生的某科成绩。

公式：

=index(D2:F11,match(I3,B2:B11,0),match(I4,D1:F1,0))

解析：

1、第一个match确定行号（获取指定姓名在B2:B11的行号）。

2、第二个match确定列号（获取指定科目所在D1:F1的列号）。

3、index根据行号和列号，从D2:F11区域中取出交叉点的值。

三、优势总结

1、列位置无关性

不需要像vlookup那样数第几列，也不怕中间插入删除列。

2、查找方向灵活

可以查找右侧的数据，也可以查找左侧的数据。

3、性能优异

match只在一行或一列中查找，比vlookup在整个表格中查找要快。

4、支持多条件

可以轻松扩展为多条件查找。

5、支持横向和纵向

不仅可以垂直查找，也可以水平查找（配合hlookup的场景，但这里match可以处理行列两个维度）。

四、常见错误及解决

#N/A：

原因：match找不到数据。

解决：检查查找值是否存在，或数据中是否有看不见的空格（使用trim函数清理）。

#VALUE!：

原因：index的行号或列号超出了数组范围。

解决：检查match返回的位置是否小于1或大于数组的维度。

#REF!：

原因：引用的单元格区域无效（例如，删除了公式中引用的列）。

动动你们发财的小手，在Excel里自己练习一下，会更容易快速掌握哦。

长按加入office之家微信交流群