VLOOKUP函数在Excel中的这种秘密功能，不告诉你一辈子也找不到！

个人微信号 | （ID：LiRuiExcel520）

微信服务号 | 跟李锐学Excel（ID：LiRuiExcel）

微信公众号 | Excel函数与公式（ID：ExcelLiRui）

Excel查找引用函数VLOOKUP很多人都用过吧，但大家都是用它查询数据，它还有一种秘密功能，你知道吗？

今天要讲的就是VLOOKUP不为人知的秘密技术，按要求自动生成符合要求的数据，帮助我们自动排布数据，比如生成自动排班表。

下面案例是某企业的值班安排表，里面包含了值班员工姓名和安排好的值班天数。

统计要求如下：

1、根据左侧的值班安排表，在右侧自动生成每天的值班员工列表；

动图演示如下图所示。

黄色区域的数据都是用公式自动计算生成的，这里用的就是VLOOKUP函数。

你肯定好奇这是怎么实现的？下文分解。

构建思路：

首先咱们一起来构建一下思路，当前案例要求按姓名和值班次数生成每日值班表，目前的数据源还缺少什么？

这就自然想到了数据源中已有值班姓名和值班天数，还缺少对应关系，即没有哪个日期对应哪个值班姓名，如果有了这个就可以直接查找调用了。

到这一步时，你会发现现有条件无法支持直接得到每个日期和对应姓名，那么我们可以换个思路，使用倒推法，退而求其次，继续想，根据现有条件能统计到什么？

从这个现有条件，我们虽然不能知道每个员工的值班开始天数，但是能统计到每个员工的值班截止天数，比如李锐1值班到第5天，李锐2值班到第7天（5+2=7），李锐3值班到第9天（5+2+2=9），......李锐5值班到第15天。

好了，思路来了，我们可以按照员工最后值班的天数查找对应的值班姓名，当然，前提是数据源中包含这个每人值班截止天数的信息，加个辅助列即可

在原始数据左侧插入列，用于标识每人的值班截止天数，在B2单元格输入以下公式。

=SUM(C$2:C2)

（下图为辅助列公式）

一句话解析：

用SUM函数配合混合引用生成值班天数的累加值，即每个人值班的截止天数。

现在好了，有了这个辅助列，我们就可以在右侧的对应天数是5/7/9/10/15时直接VLOOKUP调取对应的员工姓名了。

那么现在还差中间的那些天数，如何查找对应姓名呢？继续看下面的解决方案。

解决方案：

先接着上一节思路把第15天值班的当值员工姓名用公式查找出来，后面再顺藤摸瓜就轻松多了。

在F15单元格输入以下公式，向上填充公式到F2单元格。

=IFERROR(VLOOKUP(E16,$A$2:$C$6,2,),F17)

注意，这里为了让你更容易理解，所以先在下方写公式，然后向上填充，效果如下图所示。

在F16的单元格VLOOKUP第一参数是15，肯定可以找得到左侧15对应的姓名是李锐5，那么F15的单元格呢？

F15单元格VLOOKUP第一参数是14，肯定找不到，会返回错误值，再次用IFERROR函数返回当前单元格下方的数据。

一句话解析：

由于下方的第15天值班姓名已经出来了，所以只要第14天从左侧没有找到对应姓名，说明还是这个人值班，所以从当前公式所在单元格的下方单元格取值就行了。这个思路非常巧妙，你如果一下没懂请回顾上方说明，顺着我的思路再次突破下自己的思维壁垒。

理解后，我们再价格容错判断，形成最终公式。

在F2单元格输入以下公式，并向下填充。

=IF(E2>MAX($A$2:$A$6),"",IFERROR(VLOOKUP(E2,$A$2:$C$6,2,),F3))

一句话解析：

先用IF判断将可能返回0的结果返回空，再利用倒推法使用VLOOKUP查找，生成所需的每日值班人员列表。

此案例公式虽然并不复杂，但思路颇为精妙，如若能完全理解，将会对你的功力提升大有裨益，如一时不能参透也属正常，可以先收藏起来日后再看。

如果想全面、系统体系化提升Excel函数公式技术，别错过下面几套超清视频系列课↓