Excel多工作薄多表查找：Vlookup函数的高阶用法

编按：平常接触最多的是用VLOOKUP在单个表中查找，是最基础的用法。今天说它的巅峰表现：跨多表跨多文件（工作簿）查找。

前面说了跨多表汇总求和（有兴趣的可看文末推荐），今天说说用Vlookup跨多表和跨多文件（工作簿）查询。

1、从最简单的跨单个表、跨单个文件开始

1）跨单个表

譬如：在sheet1表查询 “成绩表”中的成绩。

公式：=VLOOKUP(A2,成绩表!A:E,5,0)

2）跨单个文件查找

譬如，在两个不同文件（工作簿）中进行查询。

公式：=VLOOKUP(A2,[1组.xlsx]Sheet1!$A$2:$E$10,5,0)

解析：

相比跨单个工作表查询，区别是引用部分增加了用方括号括起来的文件名“[1组.xlsx]”。

注：

（1）跨文件查询，被引用的文件必须打开，否则出错。

（2）查询结束，可以选择性粘贴为值把查询结果固定下来。

2、跨多表：被查对象在多个表中都存在

譬如：刘海鸥的成绩分别记载在4张月成绩表中，现在查询他每月的成绩。

公式：=VLOOKUP($B$1,INDIRECT($A3&"月成绩!A:E"),5,0)

解析：相比跨单个工作表中查询，用INDIRECT函数实现动态引用工作表。

3、跨多表：被查询对象不确定在哪个工作表中

譬如：在6张不同组别的工作表中查询不知具体是哪组的人员成绩。

长但易理解的公式：

=IFERROR(VLOOKUP(A2,'1组'!A:E,5,0),

IFERROR(VLOOKUP(A2,'2组'!A:E,5,0),

IFERROR(VLOOKUP(A2,'3组'!A:E,5,0),

IFERROR(VLOOKUP(A2,'4组'!A:E,5,0),

IFERROR(VLOOKUP(A2,'5组'!A:E,5,0),

VLOOKUP(A2,'6组'!A:E,5,0))))))

解析：

一个一个的试着查。首先在1组中查，若结果是正常值则查询结束，若结果是错误值（表示1组中没有被查人员）则在2组中查，依此类推。

短但嵌套复杂的公式：

=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT(ROW($1:$6)&"组!A:A"),A2),ROW($1:$6)&"组")&"!A:E"),5,0)

解析：

①COUNTIF(INDIRECT(ROW($1:$6)&"组!A:A"),A2)，统计被查询人在每张工作表中的出现次数，结果是一组0和1的数据。0表示工作表没有被查人员，1表示有。

②LOOKUP(1,0/①,ROW($1:$6)&"组")，返回包含被查人员的工作表名。

③INDIRECT(②&"!A:E")，引用含被查人员的工作表数据

④VLOOKUP(A2,③,5,0)，查找总分

4、跨多个文件查找

两种情况。

1）被查询人员在每个文件中都有

譬如，4个文件中分别记录了同一批人员不同月的成绩。

如查刘海鸥各月总分：

公式:

=VLOOKUP($B$1,INDIRECT("["&$A3&"月成绩.xlsx]sheet1!A:E"),5,0)

2）被查询人员不确定在哪个文件中

譬如，有6个文件，分别记录了不同组别的人员成绩。

现在需要查询人员的总分。

公式：

=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT

("["&ROW($1:$6)&"组"&".xlsx]sheet1!a:a"),A2),"["&ROW($1:$6)&"组"&".xlsx]sheet1!")&"a:e"),5,0)

关于VLOOKUP跨多表跨多文件查找就说这么多，若还有不明白的欢迎留言,小窝为你解答。

另外VLOOKUP升级了用法，譬如第一参数支持数组、第三参数支持数组，如此可以一个公式查找多个条件返回多个值。

做Excel高手，快速提升工作效率，部落窝教育Excel精品好课任你选择！