【excel怎么寻找函数】你知道比Vlookup更强大的查询函数、CP函数吗？

在日常表格制作过程中，经常会找到参考相关数据的任务。

事实上，这是大多数小伙伴接触最多的事情之一。

例如，以员工姓名为基准查找相应的业绩，以商品的产品编号为基准查找相应的销售量，等等。

今天我要介绍一个非常强大的函数组合来实现这个功能。

这就是“索引矩阵组合”(INDEX MATCH combination)。

具体的应用方案如下：

01正向查找

其中正向是从左到右查找参考大象值。

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

下图是第一季度公司职员的业绩表。我们现在想以名字找到相应的业绩。

在[F2]单元格中写入以下公式：

=index (c23360 C7，match (E2，b2: B7，0))

解析公式：

首先，使用MATCH函数找到储存格[B2:B7]中弹性的位置，然后传回数字2。

然后使用此数字2作为INDEX函数的第二个参数。也就是说，返回INDEX的第一个参数[C2:C7]单元格区域中第二个位置单元格的值。

MATCH函数有三个参数：

MATCH(查找值、查找区域、查找类型)

MATCH函数用于查找区域或数组中的值、位置和数字。

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

返回的数字用作INDEX的参数。

第三个参数的查寻类型为0表示精确查询。

INDEX通常有三个参数：

索引(返回的区域、行号、列号)

第一个参数是单行或单列，因此第三个参数也可以省略。

如果不省略，公式也可以这样写。

=index (c23360 C7、match (E2、b2: B7、0)、1)

02反向查找

其中反向是指从右到左查找参考大象值。

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

以上面的例子为例。例如，我们想找到遗嘱的职员号码是多少。

公式如下：

=index(a 23360 a 7、match (E2、b2:b7、0))

解析公式：

这个公式和正向查询的公式具有相似的意义。只需更改INDEX函数的第一个参数，将返回的区域更改为[A2:A7]区域。

因此，无论是正向还是反向查询，思维方式都是一样的。

只需将INDEX函数第一个参数的区域更改为需要返回的区域。

03双向查询

其中，双向表示在水平和垂直方向上查找参照大象值。

下图：第一季度员工工资表摘要。现在要找到遗嘱三月的工资是多少。

bbdc05c?from=article.detail&_iz=31825&index=3" width="640" height="286"/>

公式如下：

=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 函数来分别定位行和列的位置。

04 多条件查找

多条件查找是指：根据 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 的小伙伴们可以不用按三键结束。

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

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

05 区间查找

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

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

在【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。

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

如下图：

06 模糊查找

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

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

公式如下：

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

公式解析：

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

大家一定要注意下：

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

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

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

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

07 总结

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

❶ 正向查找；

❷ 反（逆）向查找；

❸ 双向查找；

❹ 多条件查找；

❹ 区间查找；

❺ 模糊查找。

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

欢迎给我们留言哦~