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

我们给出了基于在多个工作表给定列中匹配单个条件来返回值的解决方案。本文使用与之相同的示例，但是将匹配多个条件，并提供两个解决方案：一个是使用辅助列，另一个不使用辅助列。

下面是3个示例工作表：

图1：工作表Sheet1

图2：工作表Sheet2

图3：工作表Sheet3

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

图4：主工作表Master

解决方案1：使用辅助列

可以适当修改上篇文章中给出的公式，使其可以处理这里的情形。首先在每个工作表数据区域的左侧插入一个辅助列，该列中的数据为连接要查找的两个列中数据。这样，获取值的数组公式（单元格C7）如下：

=VLOOKUP(A7&” ”&B7,INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT(“‘”&Sheets&”‘!A:A”),A7&” ”&B7)>0,0))&”‘!A1:D10″),4,0)

其中，Sheets是定义的名称：

名称：Sheets

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

这个公式的运行原理与上文相同，可参见《Excel公式技巧16：使用VLOOKUP函数在多个工作表中查找相匹配的值（1）》。

解决方案2：不使用辅助列

首先定义两个名称。注意，在定义名称时，将活动单元格放置在工作表Master的第11行。

名称：Arry1

引用位置：=MATCH(TRUE,COUNTIFS(INDIRECT(“‘”&Sheets&”‘!B:B”),$A11,INDIRECT(“‘”&Sheets&”‘!C:C”),$B11)>0,0)

名称：Arry2

引用位置：=ROW(INDIRECT(“1:10”))-1

在单元格C11中的数组公式如下：

=INDEX(INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!D1:D10”),MATCH(1,(T(OFFSET(INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!B1”),Arry2,,,))=$A11)*(N(OFFSET(INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!C1”),Arry2,,,))=$B11),0))

下面来看看公式是怎么运作的。首先看看名称Arry1：

=MATCH(TRUE,COUNTIFS(INDIRECT(“‘”&Sheets&”‘!B:B”),$A11,INDIRECT(“‘”&Sheets&”‘!C:C”),$B11)>0,0)

可以转换为：

=MATCH(TRUE,COUNTIFS(INDIRECT({“‘Sheet1’!B:B”,”‘Sheet2’!B:B”,”‘Sheet3’!B:B”}),”Red”,INDIRECT({“‘Sheet1’!C:C”,”‘Sheet2’!C:C”,”‘Sheet3’!C:C”}),2012)>0,0)

转换为：

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

结果为：

3

表明在工作表列表的第3个工作表（即Sheet3）中进行查找。

因此，在单元格C11的公式中的：

INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!D1:D10”)

转换为：

INDIRECT(“‘”&INDEX(Sheets,3)&”‘!D1:D10”)

转换为：

INDIRECT(“‘”&INDEX({“Sheet1″,”Sheet2″,”Sheet3″},3)&”‘!D1:D10”)

转换为：

INDIRECT(“‘”&”Sheet3″&”‘!D1:D10”)

转换为：

INDIRECT(“‘Sheet3’!D1:D10”)

结果为：

Sheet3!D1:D10

传递到INDEX函数中作为其参数array的值：

=INDEX(Sheet3!D1:D10,MATCH(1,(T(OFFSET(INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!B1”),Arry2,,,))=$A11)*(N(OFFSET(INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!C1”),Arry2,,,))=$B11),0))

同样，公式中的：

INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!B1”)

得到：

Sheet3!B1

公式中的：

INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!C1”)

得到：

Sheet3!C1

现在，单元格C3中的公式变为：

=INDEX(Sheet3!D1:D10,MATCH(1,(T(OFFSET(Sheet3!B1,Arry2,,,))=$A11)*(N(OFFSET(Sheet3!C1,Arry2,,,))=$B11),0))

由于这里的两个公式结构：

T(OFFSET(Sheet3!B1,Arry2,,,))=$A11

和

N(OFFSET(Sheet3!C1,Arry2,,,))=$B11

相似，因此只解释其中一个的工作原理。

先看看名称Arry2：

=ROW(INDIRECT(“1:10”))-1

由于将在三个工作表中执行查找的范围是从第1行到第10行，因此公式中使用了1:10。

上述公式转换为：

{1;2;3;4;5;6;7;8;9;10}-1

得到：

{0;1;2;3;4;5;6;7;8;9}

该数组被传递给OFFSET函数作为其rows参数，这样：

OFFSET(Sheet3!B1,Arry2,,,)

将会生成：

Sheet3!B1

Sheet3!B2

Sheet3!B3

…

Sheet3!B10

因此，公式：

T(OFFSET(Sheet3!B1,Arry2,,,))=$A11

转换为：

T(OFFSET(Sheet3!B1,{0,1,2,3,4,5,6,7,8,9},,,))=$A11

转换为：

T({Sheet3!B1,Sheet3!B2,Sheet3!B3,Sheet3!B4,Sheet3!B5,Sheet3!B6,Sheet3!B7,Sheet3!B8,Sheet3!B9,Sheet3!B10})=$A11

转换为：

{“Colour”,”Red”,”Blue”,”Blue”,”Red”,”Violet”,”Blue”,”Yellow”,”Green”,”Red”}=$A11

转换为：

{“Colour”,”Red”,”Blue”,”Blue”,”Red”,”Violet”,”Blue”,”Yellow”,”Green”,”Red”}=”Red”

得到：

{FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE}

注意，如果你在这里使用的是N函数：

N(OFFSET(Sheet3!B1,Arry2,,,))

其结果将为：

{0,0,0,0,0,0,0,0,0,0}

当然，也不能够单独只使用OFFSET函数：

OFFSET(Sheet3!B1,Arry2,,,)

其结果将为：

{#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}

同样地，公式中的：

N(OFFSET(Sheet3!C1,Arry2,,,))=$B11

转换为：

{0,2010,2010,2012,2012,2012,2012,2011,2014,2011}=2012

结果为：

{FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE}

好了！现在可以将上面得到的中间结果放到主公式中：

=INDEX(Sheet3!D1:D10,MATCH(1,(T(OFFSET(Sheet3!B1,Arry2,,,))=$A11)*(N(OFFSET(Sheet3!C1,Arry2,,,))=$B11),0))

转换为：

=INDEX(Sheet3!D1:D10,MATCH(1,({FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE})*({FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE}),0))

转换为：

=INDEX(Sheet3!D1:D10,MATCH(1,{0,0,0,0,1,0,0,0,0,0},0))

转换为：

=INDEX(Sheet3!D1:D10,5)

结果为

32