VLOOKUP在多个工作表中查找相匹配的值
发布时间:2022-03-18 23:12:41
我们给出了基于在多个工作表给定列中匹配单个条件来返回值的解决方案。本文使用与之相同的示例,但是将匹配多个条件,并提供两个解决方案:一个是使用辅助列,另一个不使用辅助列。
下面是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
猜你喜欢
- 我们有时候在使用电脑经常会感觉C盘太小不够使用,并且C盘太小也会影响电脑运行速度,那么除了删除掉无用文件之外还有什么方法可以扩大C盘呢?下面
- word怎么设计立体的心形盒子?word中想要设计一个心形的立体容器,该怎么设计呢?下面我们就来看看word设计立体心形盒子的教程,需要的朋
- 不管是Word入门新手还是老手,相信这几个技巧会让你更加熟练Word,操作起来得心应手!数字转换成大写在一些表格中常常会用到大写数据,如果一
- 在Excel中,“--”是一种负负运算,可以将文本的格式变成数值的格式,在Excel中有很多妙用,今天给大家分享3种使用例子。1、批量去除0
- 当用户需要突出显示大于、小于、等于、介于某个数值或数据区域的数据,或是需要突出显示数值位居前几项以及高于、低于平均值的数据时,使用条件格式中
- Win10系统电脑无线网络列表找不到了怎么办?许多用户在更新了电脑系统之后,发现电脑无法连接无线网络,甚至连无线网络的网络列表都消失不见。下
- word怎么给图片添加文字?word中的图片需要添加文字,该怎么添加?下面我们就来看看word中给图片添加文字的教程,需要的朋友可以参考下我
- 在Excel中如果遇到时间差要怎么计算,两个时间怎么进行相减呢?1、通过上下班时间计算工作时间,在目标单元格上输入公式:=TEXT(H2-C
- 对于含有图片数量较多的Word2010文档,为了提高Word文档翻页速度,用户可以启用Word2010的“显示图片框”功能对于含有图片数量较
- 在一般的word中,可以进行如下操作:首先将光标定位至待提升为标题的文本,按Alt+Shift+←键,可把文本提升为标题,且样式为标题1,再
- 操作方法1、选择“审阅”—“翻译”—“翻译屏幕提示”,即开启翻译屏幕提示功能。 2、将鼠标停留在单词上,系统会自动
- Excel怎么做公司开销汇总的数据透视图?excel中的公司开销汇总表格想要制作成数据透视图,该怎么制作呢?下面我们就来看看详细的教程,需要
- 相信有很多用户都使用Word来进行一些文档的编辑,不过我们在很使用这款软件的时候难免会遇到一些不熟悉的地方,例如不清楚如何使用Word去制作
- 有时我们需要将数字转换成角度,或者直接录入角度,那么怎么操作呢?下面小编就为大家介绍Excel如何批量转换数字为度数方法,一起来看看吧&nb
- 在我们利用Word制作考试卷的时候难免会输入一些下划线,如“填空题”中间会留一点空格下划线,让学生能够填写答案;如文字下面加下划线,能够更好
- 在excel里,我们可能有时候不需要一些数据显示出来,需要隐藏起来,下面是几种单元格数据隐藏的3种方法,3种方法操作不同,原理不同,但都起到
- 各种书卷形的特效效果相信小伙伴们都看到过,这种特效比较逼真,和古代真正的书卷相比相差无几,看起来古色古香,很多小伙伴都比较喜欢。其实,不需要
- 有时我们在Excel单元格中输入数字过长时,就会出现显示的数字并不是我们输入的数字,这时就会给我们造成一些很大的影响。其实只需要简单几步就可
- Office Word中输入①②③④⑤⑥⑦⑧⑨⑩符号的办法方法非常简单,在利用Word输入带圈数字的情况时,如果这些数字在10以
- Win10系统怎么去除广告?Win10系统的小广告可以说是无孔不入,无论是在开始菜单、应用列表,还是在锁屏界面,几乎都有各种广告的身影,如果