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

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

以下是三个示例工作表：

图1:工作表1。

图2:工作表2。

图3:工作表3。

该示例要求在这三个工作表中从左到右搜索，并返回“金额”列中与“颜色”列中的“红色”和“2012年”对应的值，如下图4中的第7行和第11行所示。

图4:主工作表的主人。

解决方案1:使用辅助色谱柱。

前一篇文章中给出的公式可以适当修改，以处理这里的情况。首先，在每个工作表的数据区左侧插入一个辅助列。此列中的数据是要通过连接找到的两列中的数据。这样，用于获得该值的数组公式(单元格C7)如下：

=VLOOKUP(A7)“B7，间接(“”)索引(Sheets，MATCH(TRUE，COUNTIF(INDIRECT(“”“Sheets””)！A:A ")、A7 " " B7(0，0))" '！A1:D10)，4，0)

其中工作表是定义的名称：

名称：工作表

参考位置：={“sheet 1”、“sheet2”、“sheet 3”}

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

解决方案2:不要使用辅助柱。

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

名称：Arry1。

引用位置：=match (true，counts(间接(“‘sheets’”)！B:B ")，$A11，间接(“‘Sheets’”’！C:C ")，$B11)0，0)

名称：Arry2。

参考位置：=row(间接(" 1:10"))-1。

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

=INDEX(间接(“”)INDEX(Sheets，Arry1)”'！D1:D10 ")，MATCH(1，(T(OFFSET(间接)(' " INDEX(Sheets，Arry1)" '！B1 ")、Arry2、))=$A11)*(N(OFFSET(间接)(“'”INDEX(Sheets，Arry1)”'！C1 ")、Arry2、))=$B11)、0))

让我们看看公式是如何工作的。先看名字Arry1:

=MATCH(真，COUNTIFS(间接)(“‘Sheets’”’！B:B ")，$A11，间接(“‘Sheets’”’！C:C ")，$B11)0，0)

可转换为：

=MATCH(真，COUNTIFS(间接({“”sheet 1！B:B "、“‘sheet 2’！B:B "、“‘sheet 3’！B:B"})，“红色”，间接({“‘sheet 1’！C:C "、" ' Sheet2 "！C:C "、" ' Sheet3 '！C:C"})，2012)0，0)

转换为：

=MATCH(真，{0，0，1}0，0)

结果是：

三

指示在工作表列表中的第三个工作表(即工作表3)中搜索。

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

间接(“‘索引(工作表，数组1)”)！D1:D10”)

转换为：

间接(“‘INDEX(Sheets，3)”)”！D1:D10”)

转换为：

间接(“‘INDEX’({“sheet 1”，“Sheet2”，“sheet 3”}，3)”)！D1:D10”)

转换为：

间接(“”“Sheet3”“”！D1:D10”)

转换为：

间接(“Sheet3！D1:D10”)

结果是：

Sheet3！D1:D10

作为参数传递给INDEX函数的数组值：

=INDEX(Sheet3！D1:D10，MATCH(1)，(T(OFFSET(间接)(' " INDEX(Sheets，Arry1)" '！B1 ")、Arry2、))=$A11)*(N(OFFSET(间接)(“'”INDEX(Sheets，Arry1)”'！C1 ")、Arry2、))=$B11)、0))

同样，在公式中：

间接(“‘索引(工作表，数组1)”)！B1”)

获取：

Sheet3！B1

在公式中：

间接(“‘索引(工作表，数组1)”)！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(INternet(" 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(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

转换为：

{"颜色"、"红色"、"蓝色"、"蓝色"、"红色"、"紫色"、"蓝色"、"黄色"、"绿色"、"红色" }=$A11

转换为：

{"颜色"、"红色"、"蓝色"、"蓝色"、"红色"、"紫色"、"蓝色"、"黄色"、"绿色"、"红色"}="红色"

得到：

{假，真，假，假，真，假，假，假，假，假，真}

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

N(OFFSET(Sheet3！B1、Arry2、)

其结果将为：

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

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

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

结果为：

{假，假，假，真，真，真，真，假，假，假}

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

=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，TRUE})*({FALSE，FALSE，FALSE，FALSE，FALSE，FALSE，TRUE，TRUE，TRUE，TRUE，TRUE，TRUE，FALSE，FALSE})，0))

转换为：

=INDEX(Sheet3！D1:D10，MATCH(1，{0，0，0，0，1，0，0，0}，0))

转换为：

=INDEX(Sheet3！D1:D10，5)

结果为

32