怎么V前面一列的数据?
问题描述如下图,有工号,想用工号Vlookup出来部门,但是原数据部门在工号的左边,怎么办?解决概述方法1...
问题描述
如下图,有工号,想用工号Vlookup出来部门,但是原数据部门在工号的左边,怎么办?
解决概述
方法1 凉拌
方法2 Lookup
方法3 Index+Match
方法4 Vlookup+数组
友情提示:以下内容过多,小编推荐方法3。
操作方法
【方法1 凉拌】
把B列剪切到C列之后。——吐槽:用得着你教我?
【方法2 Lookup】
J2单元格 =LOOKUP(I2,$C:$C,$B:$B) [注:为展示清楚省去具体区域,不影响结果]
然后向下填充
注意!此方法要求C列必须升序排序,且I列搜索值在C列均存在。二者有其一不满足则不可用。
【方法3 Index+Match】
J2单元格 =INDEX($B:$B,MATCH(I2,$C:$C,0))
然后向下填充
【方法4 Vlookup+数组】
J2单元格 =VLOOKUP(I2,IF({1,0},$C:$C,$B:$B),2,0)
然后向下填充
原理分析
【方法2 =LOOKUP(I2,$C:$C,$B:$B)原理】
注意用的函数是Lookup,没有V,函数这么用:
=Lookup(查找的值,查找的范围,返回值的范围)
也就是说,先通过查找的值(工号20001)找到其范围C列所在行数:在第2行;那么返回值的范围B列第2行就是法务部。
注意:使用Lookup函数时,需确保查找的值在查找范围内是存在的,否则Lookup采取模糊匹配。这也是笔者不首要推荐的原因。
【方法3 =INDEX($B:$B,MATCH(I2,$C:$C,0)原理】
其实就是把Lookup的拆分成了2步。
1、Match(查找的值,查找的范围,行为),其返回的是在范围第几行。
就是查找到值(工号20001)在范围C列第几行,行为写0代表精确匹配。——返回值为2
2、INDEX(范围,行数,列数),其中行数、列数可以缺省。
就是查找范围(B列),第2行的值是什么。
小结:MATCH(I2,$C:$C,0)返回2,INDEX($B:$B,2,0)返回"法务部"。
INDEX举例,下表输入=INDEX(A17:E20,2,3)返回什么?答案是20017。你数对了么,在相对的第2行、第3列。
【方法4 =VLOOKUP(I2,IF({1,0},$C:$C,$B:$B),2,0)原理】
难点在于IF({1,0},$C:$C,$B:$B)这个的含义,这是一个数组公式,返回的是一个新的数组。
在任意空白处将这部分公式写入,按ctrl+shift+回车得到如下图示。
所以这时候Vlookup其实就是在上面这个表中Vlookup了。
网上也有学者将IF({1,0},$C:$C,$B:$B)称之为将B列和C列互换位置,我更喜欢称之为重构数组,第一列放入C列,第二列放入B列。
此方法也可以用Choose来实现。
知识拓展
今天知识点过多,就用吐槽来收尾吧。吐槽建议:
为了方便大多数人,请微软考虑下Vlookup可否最后一个参数为负数,这样就可以往左边数数了,谢谢!
查看历史Vlookup知识,请返回上级页面回复“Vlookup”
喜欢这篇文章吗?接下来你有两个选择:
——收藏!留着以后用!
——转发朋友圈,告诉他们这个so easy!
关注 Excel实务
微信扫一扫关注公众号