当前位置:首页 > 办公 > Excel教程 > 正文内容

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

酷网3周前 (10-07)Excel教程25

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

下面是3个示例工作表:

:工作表Sheet1

:工作表Sheet2

:工作表Sheet3

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

:主工作表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

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/39462.html

分享给朋友:

相关文章

Excel函数公式教程:怎么按条件累计求和?

Excel函数公式教程:怎么按条件累计求和?

excel累加求和在我们时间工作中非常常用。我们看下面的案例,B列是当日营业额,要求在C列计算出累加营业额。比如7月2日的累加营业额就是:7月1日和7月2日的营业额之和。以此类推,累计求和。 excel累计求和,可以套...

使用Excel制作美观大方的帕累托图的方法

使用Excel制作美观大方的帕累托图的方法

  帕累托图又叫排列图、主次图。是一种常见的分析图表,是按照发生频率大小顺序绘制的直方图,表示有多少结果是由已确认类型或范畴的原因所造成。那如何使用Excel制作美观大方的帕累托图呢?...

SUMIF函数其实很简单

SUMIF函数其实很简单

条件求和 SUM函数一般用于全部数据的求和,但如果要对同类的数据分开进行求和怎么办呢?看看下面这个例子吧! 分别求和计算出电视机、洗衣机、空调的销售额。 步骤:输入公式【=SUMIF($A$2:$A$10,E2,$B$2...

Excel中怎么圈选无效数据?Excel圈选无效数据的方法

Excel中怎么圈选无效数据?Excel圈选无效数据的方法

  Excel是我们 经常使用的办公软件之一,它不仅仅功能强大而且操作简洁,你知道Excel中怎么圈选无效数据的吗?接下来我们呢一起往下看看Excel圈选无效数据的方法吧。  ...

Excel合并单元格无法筛选,怎么办?

Excel合并单元格无法筛选,怎么办?

你在对合并单元格进行筛选时,是不是只能筛选出第一项?如下动图: 解决方法 1、选择合并单元格,复制到另一列,备用; 2、选中原合并单元格,【开始】——【合并单元格】——【取消合并单元格】; 3、CTRL+G,打开定位窗...

Excel图表多页打印,每页上都要有标题,怎么办?

Excel图表多页打印,每页上都要有标题,怎么办?

原来的一位学生,现在在某公司做文员,老板让她打印公司最近三个月的销售报表,数据很多,打印了很多页,呈给老板,老板一皱眉,说:重新打一份吧,除了第一页的数据有标题行,知道数据含义以外,其他页面都不知道数据是啥意思! 多页打...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。