比vlookup还强大的函数 有没有和vlookup一样的函数可以使用 收藏 0

比Vlookup更厉害的查找函数，这对CP函数你知道吗？

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

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

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

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

它们就是 INDEX+MATCH 组合。

具体的应用场景如下：

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

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

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

在【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 的疑问呢？

欢迎给我们留言哦~

比Vlookup更厉害的查找之王，这个函数你知道吗？

小E为同学们准备了40+函数大全，

获取直接在公种号【秋叶Excel】回复【头条】即可~

小李是一位 Excel 函数初级玩家，最近刚学习了 Lookup 函数。

这一天，小李看到同事正在为社区的一张信息表犯愁：

如下图，根据姓名查询年龄。

手痒痒的她，突发奇想，要用刚学的 Lookup 函数在同事面前大展身手！！！

❶ 首先选中数据区域 B2:D12，以「姓名」列作为「主要关键字」进行「升序」排序。

❷ 然后输入以下公式，就搞定了！

=LOOKUP(F3,B3:B12,D3:D12)

小李告诉同事，凡是碰到这类查询问题，都可以按这个模式套用：

=LOOKUP(lookup_value, lookup_vector, [result_vector])

=LOOKUP(查找值，查找区域，返回结果区域)

例如案例中，G3 单元格的公式：

=LOOKUP(F3,B3:B12,D3:D12)

LOOKUP 函数从查找区域 B3:B12(也就是姓名列)，找到与 F3 相同的值\”杨山\”；

然后在返回结果区域（也就是年龄这列），找到\”杨山\”对应的年龄位置，返回结果：85。

敲黑板：

在使用 LOOKUP 函数时，【查找区域】必须要升序排序。

否则可能得不到正确结果。

如下图：明明\”杨山\”的年龄是 85，公式结果却是 27！

看看！

不按升序办事，后果很严重！

前面我们介绍的是 LOOKUP 函数在精确匹配上的查找，其实它还可以……

我们接着看案例！

下面这份表，需要用如下规则，为年龄划分组别：

童年：0~6 岁；少年：7~17 岁 ；

青年：18~40 岁 ；中年：41~65 岁 ；

老年：66 岁以后。

❶ 首先按照年龄划分的规则，为 LOOKUP 函数设置参数：

查找值：D3，数值为 74

查找区域（升序排序）：G$3:G$7

返回区域：H$3:H$7

❷ 在 E3 单元格输入公式，然后下拉填充：

=LOOKUP(D3,G$3:G$7,H$3:H$7)

如下图所示：

在数轴中，我们先将「查找区域」的数值作为分段点，与之对应的「返回区域」中的文本放在数轴下方。

查找值为 74，也就是位于该数轴的最右侧，由于 Lookup 函数将在「查找区域」中查找小于或等于第 1 参数「查找值」的 最大数值，也就是 66；

而 66 对应的返回区域为老年，所以函数公式中的最终结果为老年。

简言之，就是在数轴中查找小于等于本身的最大分段点，再返回区域中与之对应的结果。

总结一下：

❶ 在使用 Lookup 函数时，必须对「查找区域」按「升序」进行排序，否则无法得到正确的结果。

❷ Lookup 函数将在「查找区域」中查找 小于或等于 第 1 参数查找值的 最大数值；

如果 「查找值」 小于「查找区域」中的最小值，那么 Lookup 函数将返回错误值 #N/A。

❸ Lookup 函数可以用在模糊匹配上，进行分组匹配。

我们知道，使用 Lookup 函数还需要事先对第 2 参数进行排序，那 Lookup 函数究竟有没有直接在乱序中查找的方法呢？

下一篇文章我们继续来探讨~

欢迎在留言区与我们进行讨论。

小E为同学们准备了40+函数大全，

获取直接在公种号【秋叶Excel】回复【头条】即可~

实锤！比Xlookup还好用的函数真的来了

前一段时间，微软突然推出XLookup函数，在各大论坛、微信群和学习群炸开了锅，实实在在的火了一把，可谓来势汹汹，欲取代VLookup而后快。可是有一句古话说得好：强中自有强中手，一山还比一山高，当WLookup被微软推出后，XLookup瞬间走下神坛退居二线。

笔者研究了一下，Wlookup的大致用法如下。

= Wlookup(查找内容，查找值范围，返回值范围，查找模式)

其中查找模式有四种：

-2 是区间查找

-1 是一对多查找

0 查找最后一个

N 查找第N个符合条件的值

看起来这个函数的功能与LOOKUP十分相似，应该属于LOOKUP的加强版。这么有吸引力的新函数，大家怎样来使用它，下面为大家列举几个：

一、单条件查找

=Wlookup(A14,A2:A9,C2:C9)

二、多条件查找

=Wlookup(A14&B14,A2:A9&B2:B9,C2:C9)

注：多条件查找需要用&连接

三、查找第N个符合条件的值

= Wlookup(查找内容，查找值范围，返回值范围，N)

如查找表中第2个林质的考勤分数

=Wlookup(B14,B2:B9,C2:C9,2)

补充：若要查找最后一个，直接将函数中的参数2更改为0就OK了。

四、一对多查找

如下图所示在C14单元格中查找出林质的所有考勤数据，公式为：=Wlookup(B14,B2:B9,C2:C9,-1)

五、按行上下查找

=Wlookup(A6,A1:D1,A2:D2)

六、从左向右查找

如下图所示根据A14单元格中的名字向右查找序号，公式为：=Wlookup(A14,B2:B9,A2:A9)

本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com