VLOOKUP函数在多个工作表中查找相匹配的值

办公教程导读

收集整理了【VLOOKUP函数在多个工作表中查找相匹配的值】办公软件教程，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含1611字，纯文字阅读大概需要3分钟。

办公教程内容图文

图1：工作表Sheet1

图2：工作表Sheet2

图3：工作表Sheet3

示例要求从这3个工作表中从左至右查找，返回Colour列中为“Red”对应的Amount列中的值，如下图4所示。

图4：主工作表Master

数组公式如下：

=VLOOKUP($A3,INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT(“‘”&Sheets&”‘!B:B”),$A3)>0,0))&”‘!B1:D10″),3,0)

其中，Sheets是定义的名称：

名称：Sheets

引用位置：={“Sheet1″,”Sheet2″,”Sheet3”}

在公式中使用的VLOOKUP函数与平常并没有什么不同，我们首先需要确定在哪个工作表中进行查找，因此我们使用的函数应该能够操作三维单元格区域，而COUNTIF函数就可以。公式中的：

COUNTIF(INDIRECT(“‘”&Sheets&”‘!B:B”),$A3)

转换为：

COUNTIF(INDIRECT(“‘”&{“Sheet1″,”Sheet2″,”Sheet3″}&”‘!B:B”),$A3)

转换为：

COUNTIF(INDIRECT({“‘Sheet1’!B:B”,”‘Sheet2’!B:B”,”‘Sheet3’!B:B”}),$A3)

INDIRECT函数指令Excel将这个文本字符串数组中的元素转换为单元格引用，然后传递给COUNTIF函数，同时单元格A3中的值作为其条件参数，这样上述公式转换成：

{0,1,3}

分别代表工作表Sheet1、Sheet2、Sheet3的列B中“Red”的数量。

因为我们想得到第一个匹配的结果，所以将该数组传递给MATCH函数：

MATCH(TRUE,COUNTIF(INDIRECT(“‘”&Sheets&”‘!B:B”),$A3)>0,0)

转换为：

MATCH(TRUE,{0,1,3}>0,0)

转换为：

MATCH(TRUE,{FALSE,TRUE,TRUE},0)

结果为：

2

因此，将在工作表列表中的第2个工作表即Sheet2中执行VLOOKUP操作。

现在，将上面的结果作为参数值传递给INDEX函数：

INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT(“‘”&Sheets&”‘!B:B”),$A3)>0,0))

转换为：

INDEX(Sheets,2)

转换为：

INDEX({“Sheet1″,”Sheet2″,”Sheet3”},2)

结果为：

Sheet2

这里，需要使用INDIRECT函数进一步构造来生成传递给VLOOKUP函数的单元格区域，因此：

=VLOOKUP($A3,INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT(“‘”&Sheets&”‘!B:B”),$A3)>0,0))&”‘!B1:D10″),3,0)

转换为：

=VLOOKUP($A3,INDIRECT(“‘Sheet2″&”‘!B1:D10”),3,0)

转换为：

=VLOOKUP($A3,INDIRECT(“‘Sheet2′!B1:D10”),3,0)

转换为：

=VLOOKUP($A3,’Sheet2’!B1:D10,3,0)

得到结果：

55