关注【秋叶 Excel】👆发送【插件】

免费领 Excel 必备 4 大插件

作者：明镜在心

在我们日常制表过程中，经常会用到查找引用相关数据的工作。

其实，这也是大部分小伙伴接触最多的工作之一。

比如：根据员工姓名查找对应的业绩、根据某一商品的货号查找对应的销量等等。

今天，我就给大家介绍一对非常强大的函数组合来实现这个功能。

它们就是 INDEX+MATCH 组合。

具体的应用场景如下：

秋叶Excel

和秋叶一起学Excel，免费获取Excel模板大全、插件合集！还有Excel真人视频、图文教程，助你轻松掌握Excel函数、图表、透视表……一起成为办公效率达人！

691篇原创内容

公众号

正向查找

这里的正向指的是：从左向右查找引用目标值。

也就是查找值在左边，返回值在右边。

如下图，是一季度公司员工的业绩表，我们现在想根据姓名查找对应的业绩。

在【F2】单元格写入如下公式：

=INDEX(C2:C7,MATCH(E2,B2:B7,0))

公式解析：

先用 MATCH 函数查找刘彦在【B2:B7】这个单元格中所在的位置，返回数字 2；

然后将这个数字 2 做为 INDEX 函数的第二参数，意思是返回 INDEX 第一参数【C2:C7】单元格区域中的第 2 个位置单元格中的值。

👉 MATCH 函数有三个参数：

MATCH（查找值，查找区域，查找类型）

MATCH 函数专门用于查找一个值，在一个区域或者数组中所在的位置，并返回一个数字。

通常将返回的这个数字作为其他函数的参数使用。

这里将返回的数字作为 INDEX 的参数使用。

如果第三参数的查找类型为 0，则表示精确查找。

👉 INDEX 通常也有三个参数：

INDEX（返回的区域，行号，列号）

因为第一参数是单行或者单列，所以第三参数也可以省略。

如果不省略的话，公式也可以写成这样：

=INDEX(C2:C7,MATCH(E2,B2:B7,0)，1)

反向查找

这里的反向指的是：从右向左查找引用目标值。

也就是查找值在右边，返回值在左边。

还以上面的例子为例，比如，我们想查找刘彦的员工编号是多少。

公式如下：

=INDEX(A2:A7,MATCH(E2,B2:B7,0))

公式解析：

这个公式的意思跟正向查找的公式差不多，只是更改了 INDEX 函数的第一参数，将返回的区域变成【A2:A7】区域。

由此可见，无论是正向查找还是反向查找，思路是一样的。

只是把 INDEX 函数的第一参数的区域更改成需要返回的区域即可。

双向查找

这里的双向指的是：从水平和垂直两个方向查找引用目标值。

如下图：是一季度员工工资表汇总，现在需要查找刘彦 3 月的工资是多少。

公式如下：

=INDEX(B3:D8,MATCH(G3,A3:A8,0),MATCH(H2,B2:D2,0))

▲ 左右滑动查看

公式解析：

先用第一个 MATCH 函数查找刘彦在【A3:A8】这个单元格中所在的垂直位置，返回数字 2；

然后使用第二个 MATCH 函数查找 3 月在【B2:D2】这个区域中所在的水平位置，返回数字 3；

最后将这两个数字分别做为 INDEX 函数的第二和第三参数，意思是返回 INDEX 第一参数【B3:D8】区域中的第 2 行第 3 列这个相交叉单元格中的值，即：8606。

因为这里的双向查找需要涉及两个方向上的位置，故我们这里使用两个 MATCH 函数来分别定位行和列的位置。

多条件查找

多条件查找是指：根据 2 个或者更多的条件来查找返回对应的目标值，这个在我们日常工作中也经常会用到。

如下图：是一张各地区各货号的销量表，我们想查找南京地区货号为 BH003 的销售量是多少。

条件一：南京；

条件二：货号为 BH003。

在【G2】单元格写入如下公式：

=INDEX(C2:C7,MATCH(E2&F2,A2:A7&B2:B7,0))

▲ 左右滑动查看

公式解析：

公式的基本框架跟上面一样，只是先用「&」文本连接符将两个条件连接一个新的条件形式；

然后按照上面正向查找的思路进行查找，并返回【C2:C7】区域中的销售量。

MATCH 函数查找「南京 BH003」在新生成的数组中的位置：

MATCH("南京 BH003",{"南京 BH001";"南京 BH002";"南京 BH003";"上海 BH001";"上海 BH002";"北京 BH001"},0)

▲ 左右滑动查看

返回数字 3，然后将这个数字 3，做为 INDEX 函数的第二参数，意思是返回 INDEX 第一参数【C2:C7】单元格区域中的第 3 个位置这个单元格中的值，也就是 72。

因为这个是数组公式，所以该公式需要按【Ctrl+Shift+Enter】三键结束。公式外面的大括号会自动生成。

对于使用 Office 365 的小伙伴们可以不用按三键结束。

当然这里的多条件查找，也可以同时应用在水平和垂直两个方向上。

另外，不论有多少个条件，只要先用「&」文本连接符将多个条件连接一个新的条件，然后应用上面的套路查找就行。

区间查找

区间查找指的是：如果查找值处于某一个区间内，就返回对应的目标值。

如下图，需要根据业绩区间来计算员工的奖金数。

在【G2】单元格写入如下公式：

=INDEX(J2:J5,MATCH(F2,{0;41;81;121},1))

▲ 左右滑动查看

公式解析：

这个公式的特别之处在于 MATCH 函数的第二和第三参数的应用。

如果第三参数是 1 或者省略, MATCH 函数将查找小于或等于第一参数的最大值，并且第二参数中的值必须以升序排序。

这里查找小于 65 的最大值是 41，41 在这个常量数组（{0;41;81;121}）中的位置是 2，即返回数字 2，然后作为 INDEX 第二参数，最终返回结果 2000。

当然，这里我们也可以将这个常量数组写在一个辅助列里面，可能会更好理解。

如下图：

模糊查找

模糊查找就是通过结合使用通配符（「？」问号和「*」星号）来实现查找目标值。

如下图：我们想查找姓徐开头的，并且是三个字符的员工编号是多少。

公式如下：

=INDEX(A2:A7,MATCH(E2&"??",B2:B7,0))

▲ 左右滑动查看

公式解析：

这里关键在于 MATCH 函数支持通配符的用法。

大家一定要注意下：

「？」问号通配符，代表任意单个字符。

「*」星号通配符，代表匹配任意多个字符。

这里我们用了两个「？？」问号，就代表任意两个字符。

PS：这里的问号和星号都是英文半角的噢！

今天，我们一起学习了两个强大的函数组合（INDEX+MATCH）来实现工作中常用的 6 种查找引用功能：

❶ 正向查找；

❷ 反（逆）向查找；

❸ 双向查找；

❹ 多条件查找；

❹ 区间查找；

❺ 模糊查找。

你平时用得最多的一种查找是什么呢？你有哪些关于 Vlookup 的疑问呢？

欢迎给我们留言哦~