如何利用Vlookup函数获取学号中的班级信息。换言之,咱们源数据中放着姓名性别学号班级等信息,而在另一张表格中一定有学号信息,但其他信息就未必有,这需要我们将缺失的信息自动同步过去。使用vlookup函数的确非常简单,今天我就再次来剖析一下这个函数吧。
一、利用Vlookup函数返回班级名称
如下图所示,A表为源数据表,B表则是需要填入缺失班级信息的表格。我们如何快速完成这个同步呢?
上图的情形是非常简单的,利用基本的vlookup函数就可以搞定了。vlookup函数共计4个参数:
=vlookup(lookup_value,Table_array,col_num,type)
A表和B表首列都是学号信息,因此我们可以通过利用B表中的学号(lookup_value)到A表(table_array)中进行查询,然后返回A表中从左右往右数第4列的信息(col_num),z只有B表中的学号在A表中存在时,才返回正确的结果(为0,即精确匹配)。因此正确的公式写法为:
=VLOOKUP(H4,$A$4:$D$15,4,0)
因为公式需要向下复制,而查询的范围永远都是A表,因此我们在列号和行号加美元($)将其固定住(绝对引用)。
按照这个思路分析,其实本公式还有其他写法,你看出来了吗?
参考答案:=VLOOKUP(I4,$B$4:$D$15,3,0)
二、利用vlookup返回多列数据
如下图所示,A表为数据源表,C表则为需要填入信息的表格,我们如何将A表中的内容快速同步到C表中呢?
通过前面的例子的讲解,我相信完全可以通过vlookup函数做到了,只是做法可能会比较笨,演示如下:
需要同步三列信息,因此写了3条公式,确实够慢的:
=VLOOKUP(A22,$A$4:$D$15,2,0)=VLOOKUP(A22,$A$4:$D$15,3,0)=VLOOKUP(A22,$A$4:$D$15,4,0)
那么我们可以一条就可以完成所有信息的同步吗?答案当然是可以的。通过观察上述三条公式我们可以发现,三条公式唯一的不同就在于第3参数的不同,如果能利用其它函数得到2,3,4这3个数字,这个公式不就简化了。因为公式是要向右复制的,因此,我们可以利用column函数来办到,由于数字是从2开始的,因此我们得到:
=vlookup(A22,$A$4:$D$15,column(b1),0)
然而当我们向右拖动的公式,会发现除了姓名列得到正确的结果,其他列的内容均出现了#NA,这是为什么呢?
点击C2单元格,我们发现公式竟然变成了,
=vlookup(B22,$A$4:$D$15,column(C1),0)
第1参数变成了B22,我们希望的是公式复制到C2单元格,第1参数依然是A22,因此我们需要在列号前加一个美元符号($),这样当公式向右复制时就不再出错了。最终的公式如下:
=VLOOKUP($A22,$A$4:$D$15,COLUMN(B1),0)
坦白说,前面这种情况算是简单的,因为A表和C表的列的顺序是完全一致的,但假如顺序不一致的,使用上述公式显然就会出错了,那么遇到这种情况下,我们该怎么办呢?
我们需要找一个函数来代替column函数。那么这个函数就是match函数(这个函数限于篇幅,我暂不做介绍),演示如下:
=VLOOKUP($A22,$A$4:$D$15,MATCH(B$21,$A$3:$D$3,0),0)
大家可以仔细琢磨一下这个公式。
三、利用vlookup函数灵活返回多列数据
如下图所示,如何将A表的数据同步到D表中,也许很多同学会说非常简单。不就是利用vlookup函数来做吗?
公式没有问题啊,为什么会出错呢?这是因为vlookup函数要求lookup_value在源数据表中只能位于最左侧,而且我们返回的列序号是以lookup_value为起点从左往右数的。那么遇到D表这种情况我们该如何办呢?我们需要利用函数将table_array中的lookup_value列调整至最左侧。最终公式如下:
{=VLOOKUP(I22,IF({1,0},$B$4:$B$15,$A$4:$A$15),2,0)}
我们利用if函数将第2参数中的lookup_value列即学号列调整至了最左侧,然后也就达到了目的。但要注意的是,此公式为数组公式,因此输入完公式后,我们需要按住Ctrl+Shift+Enter组合键完成数组公式的录入,外层的{}可不是直接输入的哦。