与 30万 粉丝一起学Excel
VIP学员的问题,新表在旧表的基础上做了一些更改,导致列标题顺序不一样,现在要根据身份证号码,将所有数据都引用过来。
新表
旧表
这个问题跟前几天的案例有点像《4条简单好用的公式,每月能节省2个小时》,再来复习一遍,如果懂了,就容易处理。
根据姓名查找所有列的对应值
=VLOOKUP($G2,$A:$E,COLUMN(B1),0)
先来看最基本的查找,然后再说明楼上的公式。
=VLOOKUP($G2,$A:$E,2,0)
语法:
=VLOOKUP(查找值,查找区域,返回区域第几列,0)
基本工资在区域的第2列,因此第三参数写2。
同理,岗位工资在区域的第3列,写3。
=VLOOKUP($G2,$A:$E,3,0)
同理,值班补助在区域的第4列,写4。
=VLOOKUP($G2,$A:$E,4,0)
同理,生活补贴在区域的第5列,写5。
=VLOOKUP($G2,$A:$E,5,0)
目前列数比较少,手工修改下也挺快的,如果列数很多,就不太方便。因此COLUMN就派上用场,专门右拉生成数字2、3、4、5。
最后将COLUMN嵌套进去,就是完整的公式。
今天的案例是顺序不一样,无法用COLUMN,不过可以借助MATCH,可以查找标题在旧表里面的第几列,如果没有就显示错误值#N/A。
=MATCH(C$1,旧表!$B$1:$O$1,0)
因此就用VLOOKUP+MATCH组合查找列标题顺序不一样的,查找不到的再嵌套IFERROR让它显示空白。
=IFERROR(VLOOKUP($B2,旧表!$C:$O,MATCH(C$1,旧表!$C$1:$O$1,0),0),"")
右边的全部查找出来了,现在剩下姓名。姓名在身份证号码左边,用LOOKUP更合适,经典查找模式是不区分方向。
=LOOKUP(1,0/(B2=旧表!C:C),旧表!B:B)
有不少粉丝一直想搞清楚这条公式的来龙去脉,说句实话,完全没必要。当年跟这个语法的发明者聊过,他说了,除非你是要成为Excel专家,对于普通人,只要记住这个套路就行,照样可以解决问题。
套路:只需懂得改红色部分就行。
=LOOKUP(1,0/(查找值=查找区域),返回区域)
对于MATCH,平常讲得很少,再举2个小案例说明。
1.查找身份证号码在第几行
=MATCH(A2,E:E,0)
2.查找最后一行的行号
这个昨天用LOOKUP解决。
=LOOKUP(1,0/(A:A<>""),ROW(A:A))
其实MATCH也有类似于LOOKUP这种1,0/的语法,用在这里更加简洁。这是数组用法,要按Ctrl+Shift+Enter三键结束。
=MATCH(1,0/(A:A<>""))
平常发的文章,多看几遍,以后遇到类似的案例,才懂得改成自己要的。
联系客服