VLOOKUP函数如何在多个工作表中查找相匹配的值
发布时间:2022-10-28 02:24:46
我们给出了基于在多个工作表给定列中匹配单个条件来返回值的解决方案。本文使用与之相同的示例,但是将匹配多个条件,并提供两个解决方案:一个是使用辅助列,另一个不使用辅助列。
下面是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
猜你喜欢
- word编号2下级变为2.1我们在编辑Word文档时,我们经常用到Word文档的自动编号功能,但是Word默认的编号样式为一级编号,即编号为
- 由于某些软件的版本支持问题,Win10系统2004版本的能够使用,而Win10系统20H2版本的不能使用,所以就有小伙伴就想知道有没有什么办
- 第一步:在Excel中,单击选中“B2”单击格,执行“窗口”→“拆分”命令(如图1),接下来便会出现图2所示的效果,如果没有出现图2所示效果
- 对于比较熟悉Word的小伙伴来说,Word中的各个图标基本上都认识,比如格式刷图标,加粗字体图标,调整字体大小的图标,设置居中的图标,设置缩
- 所谓连字符主要用于设置英文Word文档中行尾的英文单词的断开位置,用户可以通过使用自动断字、手动断字、插入可选连字符或插入不间断连字符功能控
- 在使用word的时候我们经常需要处理的文档比较长而且又复杂,当需要将文档展示给别人看的时候。我们可以折叠除了要重点关注部分之外的所有内容。现
- 1.先将Excel表格“文件”-->另存为,然后选择“CSV”格式;然后将“CSV”格式文件用“记事本”来打开2.打开后将文件另存为“
- 很多朋友读喜欢使用word来保存编辑文档,不过有些时候word文档会出现图片加载很慢的问题,所以就会有朋友隐藏不重要的图片来做到更快的阅读效
- Word表格数据如何排序?在我们平常Word表格的操作中,会需要对表格内的数据进行排序,那么应该如何操作呢?其实方法很简单,下面小编就教给大
- Win10 20H2在更新过程中总是会遇到各种各种的问题导致更新失败,那么Win10 20H2更新错误怎么修复?小编给大家列出几种详细的修复
- 说到Word编号,我想很多人都会使用了,但有一个用法却很少人知道,那就是自动填充数据,比如说,横向添加序号或竖向添加。今天跟大家讲讲这个编号
- excel怎么制作美观又实用的考勤表?为了能更好的管理员工,很多公司都会让员工考勤,考勤表怎么才能做得又好看又实用呢?下面我们就来看看exc
- 在网上下载好隶书字体,再将隶书字体文件移动到电脑系统的文字管理文件夹中。这样就能在word中使用隶书字体,具体操作请参照以下步骤。1、在网上
- Excel2016表格中怎么制作柱形悬浮图表?excel表格中想要将数据做成柱形图,然后将图框隐藏做成悬浮图标,下面我们就来看看详细的教程,
- 默认情况下,新建工作簿时只包含3张工作表,用户可以根据需要在excel2003工作簿中添加新工作表,或者将不需要的工作表删除,还可以将工作表
- 在Word中,进行特殊排版的时候,会给文档增加一些底纹和边框,使得文档看起来美观不少。可是这个底纹和边框要怎么样来设置呢?一起来看一下吧!W
- Windows Biometric Service一般用于电脑的指纹识别功能,该功能可以让系统识别并使用 指纹解锁、人脸识别、Windows
- 借助Word2010提供的SmartArt功能,用户可以在Word2010文档中插入丰富多彩、表现力丰富的SmartArt示意图,操作步骤如
- word软件已经是用户再熟悉不过的办公软件了,用户现在遇到需要编辑文档的情况时,就会选择使用这款办公软件来解决,当用户在编辑文档时,会插入多
- excel在工作簿中定义了较多的名称时,可以使用以下两种方法快速地选择名称所对应的单元格区域。下面以“设置名称引用.xlsx”工作簿为实例简