【Excel函数】你真的会用VLOOKUP吗?教你一招,5分钟把人家一天的工作干完!
本文作者:小敏
本文编辑:竺兰
我们秋叶 Excel 训练营的同学提了一个问题,看看你知不知道~
截图看不清?
没瓜系,重要的是这个问题:
用 VLOOKUP 从多表中提取信息,怎么弄?
这个问题,嗯,毕竟是从我们训练营的同学口中问出来的,还是很有水准的~
先来简单回顾一下 Vlookup 函数:
VLOOKUP 有四个参数——
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
❶ lookup_value:查找值;
❷ table_array:查找区域(去哪儿找);
❸ col_index_num:返回的值的列数;
❹ range_lookup:精确查找 or 模糊查找。
四个参数中 1 和 4 是非常明确的,主要问题就在 2 和 3 上,2 的结构如果是一定的,那么 3 也是确定的数值。
了解完 Vlookup 用法,我们来聊一聊如何跨多个表查找值。
一个表一个表找
分别用 Vlookup 公式从两个表里取就好了嘛~
可是,我们童鞋说:我也不知道这些数据它们都在哪个表里啊
太麻烦了!
好吧,那就看第 2 种方法吧~
合并多表一起找
不知道数据分别在哪个表,那我们干脆把所有的数据都放到一个表里去,让 Excel 自己找!
我们的助教老师们真的是太敬业了,给出了一种又一种办法。
直到帮童鞋解决问题。
甚至还有老师在事后看到,还在想有没有更快的解决方法。
你可别说,还真有!
如果多个数据表不整合在一起,就不能跨多表查找数据了吗?
当然可以啊~
假设我们要从多个部门的分表中,查找到某个工号的员工姓名。
如果这个工号是「人力资源部」的,那我们就可以写公式:
=VLOOKUP(A2,人力资源部!A:B,2,0)
可是,不凑巧,这个工号不是人力资源部的,所以找不到,显示 #N/A 错误。
Vlookup 遇见这种错误不要慌,我们有办法。
用一个屏蔽错误值的函数——Iferror 函数,就能把错误值换成我们要的。
于是乎,我们可以在这个出现错误值的函数外面套一个 IFERROR。
IFERROR(value,value_if_error)
❶ value:需要做判断的值;
❷ value_if_error:如果值错误要返回什么内容。
当出现错误值的时候,继续在「研发部」里看看能不能找到——
-
=IFERROR(VLOOKUP(A2,人力资源部!A:B,2,0),VLOOKUP(A2,研发部!A:B,2,0))
再出现错误值的时候,继续在「财务部」里看看能不能找到——
-
=IFERROR(IFERROR(VLOOKUP(A2,人力资源部!A:B,2,0),VLOOKUP(A2,研发部!A:B,2,0)),VLOOKUP(A2,财务部!A:B,2,0))
这样一层层套下去,直到把所有表都查找完,总能找到你要的值。
如果全部找完还是 #N/A 错误,那就是真没数据了。
所以用这样一个多层屏蔽 #N/A 错误值的思路,我们就可以在多个表中进行数据查找啦~
但是!
掰掰手指一算,
3 个表要用 Vlookup 函数 3 次,Iferror(3-1)次;
4 个表要用 Vlookup 函数 4 次,Iferror(4-1)次;
......
这个公式能不能简化一下呢?
能!
引用 Indirect 函数指定查找区域
如果你嫌上面的公式太长,那可以套用这个公式:
-
=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"人力资源部";"研发部";"财务部"}&"!A:A"),A2),{"人力资源部";"研发部";"财务部"})&"!A:B"),2,0)
不过这个公式的理解成本比较高,会用就可以了~
你只要会修改这些参数,就可以套用该公式:
A2:Vlookup 的查找值;
{}数组里的内容:多个工作表名称,用逗号分隔;
A:A:查找值在各个表中的哪一列,需要确定各个表的该列是否存在这个查找值;
A:B:vlookup 的查找区域;
2:返回值的列数,姓名是在 A:B 区域中的第 2 列。
不过快速的方法也有它的大前提:多个工作表的格式必须一致。
怎么样?解决问题的方法是不是有很多?
小白有小白的解决方法;懂一点 Excel 和懂更多 Excel 的人,解决方法又会有不同。
问题同样都能解决,但是解决问题的速度是大不相同。