vlookup函数最高级的应用:多表多文件查找

时间:2019-11-20
阅读:

关于vlookup函数的教程本站已发过入门+初级+进阶+高级的。在网上也可以搜到很多关于vlookup的教程,具体详见:vlookup函数 - vlookup函数的使用方法_vlookup函数的操作实例。但这些教程中都缺了vlookup的一个关键应用:跨多表多文件查找。今天本文将讲述了vlookup函数最高级的应用:多表多文件查找。

一、跨多工作表查找

【例】工资表模板中,每个部门一个表。

在查询表中,要求根据提供的姓名,从销售~综合5个工作表中查询该员工的基本工资。

分析:

如果,我们知道A1是销售部的,那么公式可以写为:

=VLOOKUP(A2,销售!A:G,7,0)

如果,我们知道A1可能在销售或财务表这2个表中,公式可以写为:

=IFERROR(VLOOKUP(A2,销售!A:G,7,0),VLOOKUP(A2,财务!A:G,7,0))

意思是,如果在销售表中查找不到(用iferror函数判断),则去财务表中再查找。

如果,我们知道A1可能在销售、财务或服务表中,公式可以再次改为:

=IFERROR(VLOOKUP(A2,销售!A:G,7,0),IFERROR(VLOOKUP(A2,财务!A:G,7,0),VLOOKUP(A2,!A:G,7,0)))

意思是从销售表开始查询,前面的查询不到就到后面的表中查找。

如果,有更多的表,如本例中5个表,那就一层层的套用下去。这也是我们今天提供的VLOOKUP多表查找

方法1:

=IFERROR(VLOOKUP(A2,服务!A:G,7,0),IFERROR(VLOOKUP(A2,人事!A:G,7,0),IFERROR(VLOOKUP(A2,综合!A:G,7,0),IFERROR(VLOOKUP(A2,财务!A:G,7,0),IFERROR(VLOOKUP(A2,销售!A:G,7,0),"无此人信息")))))

------------------------------------------

如果你想简化一下公式,以适合在更多的表中查谒,再提供一个思路,只是公式简单了,理解起来却难了。这里你只需要学会怎么修改公式套用就可以了。

方法2:

=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"销售";"服务";"人事";"综合";"财务"}&"!a:a"),A2),{"销售";"服务";"人事";"综合";"财务"})&"!a:g"),7,0)

你只需要修改以下部分,就可以直接套用

A2:查找的内容

{""}:大括号内是要查找的多个工作表名称,用逗号分隔

a:a :本例是姓名在各个表中的A列,如果在B列则为b:b

a:g :vlookup查找的区域

7:是vlookup第3个参数,相对应的列数。你懂的。

公式思路说明:

1、确定员工是在哪个表中。这里利用countif函数可以多表统计来分虽计算各个表中该员工存在的个数。

2、利用lookup(1,0/(数组),数组) 结构取得工作表的名称

3、利用indirec函数把字符串转换成单元格引用。

4、利用vlookup查找。

二、跨多文件查找

跨多个文件查找,估计你搜遍网络也找不到,这也是首次编写跨多文件查找公式。其实原理和跨多表查找一样,也是借助lookup等函数实现。

文件夹中有N个仓库产品表格,需要在“查询”文件完成查询

仓库表样式

在查询表中设置公式,根据产品名称从指定的文件中sheet1工作表查询入库单价

=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT("["&{"仓库1";"仓库2";"仓库3"}&".xlsx]sheet1!a:a"),A2),"["&{"仓库1";"仓库2";"仓库3"}&".xlsx]sheet1")&"!a:b"),2,0)。

补充:

vlookup函数的多文件查找,同样可以用iferror+vlookup的模式,公式虽然长,但容易理解且公式不容易出错。如果你有一定基础,倒可以试试第2种方法。

另外,如果工作表或excel文件有几十个或更多,就需要使用宏表函数Get.workbook来获取所有工作表的名称和用Files获取所有excel文件名称,然后应用到公式中。

返回顶部
顶部