VLOOKUP进阶——你可能所不知道的VLOOKUP用法

看见星光

=====================写在前面====================

VLOOKUP函数，号称函数家族中的大众情人，是最简单最实用的函数之一，也是我们最常用的三个函数之一（其余两个是霸主级的SUM和IF），关于它的每一寸肌肤（男生）每一块肌肉（女生），想必均都了如指掌，熟捻于心了。但是，您是否真正了解他的心呢？

因为这句反问，勾起伤心往事的，男生请走开，女生拥抱安慰，哈哈。

接下来我们有三个问题。

第一个，VLOOKUP能否查询符合条件的多个数值？就像经典数组套路INDEX+SMALL+IF那样？

第二个，VLOOKUP能否进行条件求和？就像SUMIF那样？

第三个，VLOOKUP第一参数，能否支持数组？

这三个问题，想必很多新人都会这样回答——不行……吧……？

我以前也这么认为。

但，这是真的吗？

——这当然不是真的。

这个帖子，咱们希望通过四个例子，扩展大家对于VLOOKUP函数的理解，更新大家对于VLOOKUP固定而死板的印象。

例子一，VLOOKUP按指定次数重复数据。

例子二，VLOOKUP查询符合条件的多个结果。

例子三，VLOOKUP条件求和以及生成内存数组。

例子四，VLOOKUP在字符串提取中的应用。

帖子进了知识树，所以看帖子的人总还是会有吧，所以我还是更新下例题附件吧：

您可能所不知道的VLOOKUP.zip (24.47 KB, 下载次数: 13182)

2014-9-1 00:09 上传

点击文件名下载附件

再增加一个附件：入门VLOOKUP

（年前给同事培训做的一个小东西，仅供练习消遣，真正的入门贴，还是看知识树哈）

入门VLOOKUP.rar (124.65 KB, 下载次数: 13273)

2014-8-22 13:04 上传

点击文件名下载附件

补注：

有些朋友看不明白多维数组部分，可以抹黑公式，按F9查看计算过程，如果实在茫无头绪，不妨置之不理，只要能看懂辅助列部分，相信便是有所收获，呵呵，对吧？（我每次发呵呵，都忍不住想起胡剑么么哒，晕）。

看见星光

第一节：VLOOKUP按指定次数重复数据

如下图，有这样一道题，要求按照B列的指定次数，重复C列的班级名称，结果放入E列。

看到这道题，好学的表亲们，大概会立刻想起祝老师的某个基础操作教程动画，函数了得的亲们，会立刻想起某个多维数组套路。

但咱们这里只想VLOOKUP。

如果用VLOOKUP，这题怎么做？

很简单。

只需要两步。

第一步，A2输入公式：=A1+B2，向下填充

第二步，E2输入公式：=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),E3)&""，向下拖动。

然后……结果……

如下图：

我们结合两个公式，解释下其中过程。

第一个公式：A1+B2，是计算相关次数的累计值，比较好理解。

第二个公式：=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),E3)&""，看起来是常用的VLOOKUP套路，但其实有两个很有意思的地方。

其一，VLOOKUP的查找值——ROW(A1)。在公式的下拉过程中，通过查找1，2，3，4，5，（电脑配音，12345，上~山~打~老~虎）……来返回结果。

其二，屏蔽VLOOKUP错误值的方式。如果VLOOKUP查找不到相关数值，比如此例中的1和2，通常会返回错误值#N/A，而我们通过IFERROR，使它返回公式所在单元格的下一个单元格的值。

比如，我们在E2输入公式，VLOOKUP函数的错误值则返回E3，公式向下拖动，E3的错误值返回E4……如此类推，直至VLOOKUP函数返回正确值——则之前通过IFERROR函数判断为错误值的单元格，自然统一更新为相应的正确值（……脑海里播放多米诺骨牌从依次跌倒到依次站起的画面）。

然后再进行新一轮循环判断、数据更正。

最后的&””，是函数里常用的屏蔽零值的技法，以便在VLOOKUP公式下拉过界时，返回的零值显示为空白。

以上两个公式，除了VLOOKUP(ROW(A1)……)的技巧外，还利用了函数的另外一个技巧，我们姑且称之为上下其手。

何谓上下其手？简而言之，便是拿公式所在单元格的上下单元格结果为己用。

第一个公式，=A1+B2,是上手，拿公式所在单元格的上一个单元格的值为己用。很多人比较熟悉常用，已经很了解了。

第二个公式，=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),E3)&""，是下手，拿公式所在单元格的下一个单元格的值为己用。大家用的可能就比较少了。因为少用，所以才显得比较有意思。大家有闲时，不妨多想下，兴许别有收获哈。

==========我是似水温柔的分割线==========

通过辅助列的方式，我们实现了按指定次数重复数据。

下面咱们要做的，便是丢掉辅助列，直接用一个公式得出结果。

即，我们需要把A列累计次数求和的数据，放入VLOOKUP公式的查找范围中，以便直接得出所需要的结果。

我们可以使用这样的公式：

SUMIF(OFFSET($B$2,,,ROW($1:$4),),"<>")

这是一个累计求和的多维数组套路，类似的套路还有MMULT、INDIRECT、SUBTOTAL等。

这个公式，是通过OFFSET函数，制作多维求和统计范围，比如B2:B2,B2:B3,B2:B4……

最后使用SUMIF进行求和。

我们把这一段放入VLOOKUP函数中：

E2=IFERROR(VLOOKUP(ROW(A1),IF({1,0},SUMIF(OFFSET($B$2,,,ROW($1:$4),),"<>"),$C$2:$C$5),2,0),E3)&""

如此，这个公式便正式写完了。

当然，如果用LOOKUP，公式可以简洁：

=LOOKUP(ROW(A1),SUMIF(OFFSET($B$1,,,ROW($1:$5),),"<>")+1,$C$2:$C$6)&""

真是晕菜了，这一节，我发了N次，一直提示我有不良信息，我开始以为是上下其手，但最后发现……

是上山打~老~虎。。

好吧，爱~老~虎油，不让说。

老~虎也不行。

唉，老~虎，老~虎，你炽热地发光, 照得夜晚的森林灿烂辉煌（可怜的布莱克）……

看见星光

第二节：VLOOKUP查询符合条件的多个结果。

通过第一节的内容，我们初步认识了VLOOKUP(ROW(A1),……)的技巧。

这一节，我们需要利用这个技巧，回答开篇所提到的第一个问题。

VLOOKUP能否查询符合条件的多个数值？就像经典数组套路INDEX+SMALL+IF那样？

如上图，我们需要提取C列符合F1班级的姓名，放入E4:E15。

通常我们会使用INDEX+SMALL+IF的数组套路：

E4=INDEX(C:C,SMALL(IF($B$1:$B$15=F$1,ROW($1:$15),4^8),ROW(A1)))&""

如果使用VLOOKUP，我们应该怎么做？

其实也简单。

我们还是如第一节那般，先采用辅助列的方式。

A2=COUNTIF(B$2:B2,F$1)

向下填充。

E4=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),"")

向下填充。

结果……如下：

这里，咱们依然利用了VLOOKUP(ROW(A1)……)的技巧。

第一个公式：=COUNTIF(B$2:B2,F$1)

我们使用COUNTIF函数，配合相对引用的原理，统计班级的累计重复次数。

第二个公式：=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),"")

我们通过VLOOKUP查询ROW(a1)（1，2，3，4，5，上山打老……），来返回与之相对应的C列姓名结果，最后外套IFERROR函数，屏蔽VLOOKUP查询不到结果而返回的错误值，使之返回空白。

在数据量大时，我们使用INDEX+SMALL数组查询数据，难免卡机，此时不妨使用VLOOKUP+辅助列的方式，当然，辅助列我们不能再使用低效函数COUNTIF了，我们可以使用这样的公式：

=(B2=$F$1)+A1

（感谢Bodhidharma老师指正错误之处哈）

==========我是往事如烟的分割线==========

理解了辅助列的意义，加深了VLOOKUP(ROW(A1),……)技巧的理解，我们下面要做的，依然是丢掉辅助列，把辅助列的内容，放到公式中，直接使用一个公式得出结果。

我们依然可以使用OFFSET对COUNTIF的统计范围进行多维引用，比如：

=COUNTIF(OFFSET(B$2,,,ROW($1:$14)),F$1)

这个公式的意思，是使用COUNTIF对B2:B2,B2:B3,B2:B4……直至B2:B15的范围内，分别统计F1数值的重复次数，得出来的结果，自然是和辅助列是一致的。

我们将这一段公式，放入VLOOKUP函数公式中：

=IFERROR(VLOOKUP(ROW(A1),IF({1,0},COUNTIF(OFFSET($B$2,,,ROW($1:$14)),$F$1),$C$2:$C$15),2,0),"")

如此，这个公式也便正式写完了。

==========我是如烟往事的分割线==========

当然，如果您确实了解透彻了VLOOKUP的心，关于VLOOKUP查询符合条件的多个数值，我们其实也可以写成这样：

=IFERROR(VLOOKUP($F$1,OFFSET($B$1:$C$1,SMALL(IF($B$1:$B$15=$F$1,ROW($1:$15)-1),ROW(1:1)),,15),2,0),"")

或者这样：

=IFERROR(VLOOKUP($F$1,INDIRECT("b"&SMALL(IF($B$1:$B$15=$F$1,ROW($1:$15)),ROW(1:1))&":c15"),2,0),"")

我们结合第二个函数套路来稍微解释下此中过程。

SMALL(IF($B$1:$B$15=$F$1,ROW($1:$15)),ROW(1:1))

IF函数判断B1:B15的值，是否等于F1，并返回相对应的行数序号ROW(1:15)，或者FALSE。(为什么将IF的假值留白，而不是像许多学友那样习惯性的输入4^8之类？因为这里没有必要撒，逻辑值天生就比数值大不是……)

SMALL函数，按IF函数的结果，在公式下拉的过程中，依次从小到大取数，即ROW(1:1),ROW(2:2)，取得最小值，第二小值……。

INDIRECT函数，搭配SMALL所取得的结果，完成对VLOOKUP查找范围从大到小的限定。

比如此例中的INDIRECT(“B”&13&”:C15”),INDIRECT(“B”&14&”:C15”)……。

由于VLOOKUP天生只取首个匹配结果，所以咱们通过查找范围的精确限定，便可以使它依次取得所有符合条件的结果……

最后外套IFERROR函数，屏蔽错误值，使之返回空白。

...

..

.

好啦，现在，咱们可以很清楚的知道，关于VLOOKUP无法提取符合条件多个数值的说法，是不正确的。呵呵。（我每次发呵呵，都会想起胡剑么么哒，唉）

看见星光

第三节：VLOOKUP条件求和以及T/N+IF{1}技巧建立内存数组的一个应用小例。

这一节，我们来回答开篇所提到的第二个以及第三个问题：

VLOOKUP能否进行条件求和？就像SUMIF那样？

VLOOKUP第一个参数能否支持数组引用？

如下图，有这样一道题，需要在E1，求出A列存在的D3:D6班级的成绩之和。

解这道题的方法有很多种，我们通常使用SUMIF：

数组：=SUM(SUMIF(A1:B5,D4:D6,B1))

或者：

数组：=SUM((A2:A5=TRANSPOSE(D4:D6))*B2:B5)

如果用VLOOKUP，又怎么做呢？

我们可以写成这样：

E1=SUM(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0))

这个公式不需要按数组三键。

我们来简单了解下这个公式。

重点是VLOOKUP的查找值，T(IF({1},D4:D6))。

我们知道D4:D6，是需要进行查找统计的班级名称，那么为什么要在其外套T和IF函数？或者，我们反过来想，为什么不套T和IF函数，VLOOKUP就只对查询范围的第一个数值（金庸班）进行查询呢？

我们可以这么简单的理解。

T/N+IF组合，是让VLOOKUP函数的第一参数，接受数组形式，因此返回相应的内存数组。

如此，VLOOKUP方能对每一个查找值进行查询统计。

具体解释参见小翟斑竹的贴子：http://club.excelhome.net/thread-1115878-1-1.html

如果为了避免错误值的问题，比如D4:D6出现了查询范围不曾出现的班级名称：天仙班，公式可以修改为：

数组：=SUM(IFERROR(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0),0))

=========我是温暖恰春的分割线=========

再看一道题。

如上图，对A列存在的D4:D6的班级进行求和，班级重复的只计算一次，答案是305。

我们通常使用这样的数组公式：

=SUM(SUMIFS(B:B,A:A,D4:D6)/COUNTIF(A:A,D4:D6))

或者：

=SUM(N(INDIRECT("b"&MATCH(D4:D6,A1:A9,))))

其实我们也可以使用VLOOKUP：

=SUM(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0))

依然由于VLOOKUP天生就只取首个匹配的结果的缘故，所以咱们也就不需要对重复数据进行二次处理。

如果要屏蔽错误值，依然要增加IFERROR：

数组=SUM(IFERROR(VLOOKUP(T(IF({1},D4:D6)),A:B,2,0),0))

=========我是恰春温暖的分割线=========

综合以上两个问题，咱们不难发现，在条件求和方面，VLOOKUP和SUMIF还是有所不同的。

如果未加以处理，VLOOKUP只对第一次出现的数据进行计算，这是它的短处，当然，未必不是它的长处。

如果未加以处理，SUMIF会对所有数据进行求和，不论重复与否，这是它的长处，当然，未必不是它的短处。

=========我只是分割线=========

T/N+IF{1}技巧建立内存数组的一个应用小例

如图，判定D列姓名的相对累计重复次数（中文名和英文名如果是同一个人的名字，则同样视为重复）

这道题如果用辅助列，会很简单。

直接把名字统一转换为中文或者英文，再使用COUNTIF进行重复次数计算。

比如E2公式：=IFERROR(VLOOKUP(D2,A:B,2,0),D2)，下拉后将名字统一更换为中文；再使用公式=COUNTIF($E$2:E2,E2),下拉后便可得出正确结果。

但如果不用辅助列呢？

如果我们继续之前的解题思路，将查询的名字，统一更换为中文或者英文，再进行重复次数的计算，我们依然可以使用VLOOKUP函数。

比如公式：F2=SUM(N(IFERROR(VLOOKUP(T(IF({1},$D$2:D2)),A:B,2,0),$D$2:D2)=IF(CODE(D2)<91,VLOOKUP(D2,A:B,2,),D2)))

IFERROR(VLOOKUP(T(IF({1},$D$2:D2)),A:B,2,0),$D$2:D2)，是在公式下拉的过程中，将第二行到公式所在行的D列姓名，统一转换为中文，并以可以计算的内存数组的形式保存相关值。

IF(CODE(D2)<91,VLOOKUP(D2,A:B,2,),D2),是将D列需要判定重复次数的值，统一转化为中文。

SUM(N……)是统计第一个公式的内存数组值等于第二个公式返回值的次数，即相关名字的重复次数。

...

..

.

看见星光

第四节：VLOOKUP在字符串提取中的使用小例。

我们依然用题来说事哈。

下面这道题，我们需要提取A列单元格内第一个数值，结果如B列。

我们通常使用数组公式：

B2=MID(A2,MATCH(1=1,ISNUMBER(-MID(A2,ROW($1:$99),1)),),1)

上面这个公式，通过ISNUMBER和MID组合，来判断单元格内每一个字符是否是数值，再通过MATCH函数，对首个数值的位置进行定位，最后通过MID函数来取值。

如果我们用VLOOKUP来处理呢？

我们可以写成这样：

数组：=VLOOKUP(,MID(A2,ROW($1:$99),1)*{0,1},2,)

这个公式，依然利用MID函数，把单元格内的字符拆成个体，分别乘以0和1，如此则产生两列数据，一列由MID(A2,ROW($1:$99),1)*0得来，另外一列由MID(A2,ROW($1:$99),1)*1得来。

我们知道文本*0，是错误值，数值*0，结果为0。

于是当我们利用VLOOKUP，查找第一列的0值，得出来的结果，便是首个0值所对应的数值——即我们所需要的结果。

这个技巧，并不仅仅局限于提取首个数字的使用，比如一个稍微复杂的示例：

如上图。数据区域是一些数据，有的人名后有电话号码，有的人名后没有电话号码，现在要求把没有电话号码的人名增补电话号码，增补的电话号码从哪来呢？向下数，从距离最近的拥有电话号码的人名那儿来，（关系再远，比如表大爷，毕竟也是一家人不是？）
结果如C列。
我们可以使用这样的公式：
=B3&IF(COUNT(--MID(B3,ROW($1:$52),11)),,VLOOKUP(,MID(PHONETIC(B3:$B$36),ROW($1:$201),11)*{0,1},2,0))

IF(COUNT(--MID(B3,ROW($1:$52),11))，是判断单元格内是否有电话号码。
VLOOKUP(,MID(PHONETIC(B3:$B$36),ROW($1:$201),11)*{0,1},2,0))
PHONETIC函数，将数据区域捏合为一个数据，MID函数，从中提取手机号码，最后通过VLOOKUP(,数据*{0,1},2,)的技巧，将MID的提取结果，分别乘以0和1，如此前所言，文本乘0，为错误值，数值乘0，结果为0，最后通过VLOOKUP来取得首个匹配结果，便是距离最近的手机号码。最后有B3黏合提取的电话号码。

http://club.excelhome.net/thread-1149155-1-1.html

…

..

.

后记：

这篇帖子，只是分享思路和技巧，并不是建议每类问题用vlookup去解决。术业有专攻，每个函数，均有长处和短处，而且，数据应该适应函数，而不是函数来适应数据，不管什么时候，数据录入的规范性，都是最重要的哈。

再后记：

第一次写这类分享文，从早上9点钟动笔时的信心满满，到中午11.30草草结束时的垂头丧气，这中间的过程，真他妈的苦。如果不是忌惮旁边MM的心理承受能力，俺真想砸桌子骂脏话。妹的，俺果然还是适合讲故事，不适合玩技术分析……嗯，只希望这篇破烂东西，可以开拓大家对于VLOOKUP函数的视野，拓展下思维方式，嗯，祝安。。。

sw=andy

前排旺位招租！！！
多谢楼主，先顶再看！！

满坛皆为吾师

看见星光 发表于 2014-8-19 13:02
LOOKUP应该是最强大的函数之一，不管是数组运转还是技巧方面，VLOOKUP能做的，它通常也能做到，而且很多时 ...

星光大侠,Vlookup是我最喜欢的函数,没有之一...除了sum以外这是我接触的第二个函数,感触深刻.

yi860214

谢谢分享。

看见星光

满坛皆为吾师 发表于 2014-8-19 13:08
星光大侠,Vlookup是我最喜欢的函数,没有之一...除了sum以外这是我接触的第二个函数,感触深刻.

居然敢说你是在认识IF那妞之前认识的VLOOKUP！赤裸裸的喜新厌旧哇！！哈哈，有朵花了，先送了。

下里巴巴

牛！牛！学习！