使用VLOOKUP函数在多个工作表中查找相匹配的值
发布时间:2022-06-18 02:17:51
在某个工作表单元格区域中查找值时,我们通常都会使用VLOOKUP函数。但是,如果在多个工作表中查找值并返回第一个相匹配的值时,可以使用VLOOKUP函数吗?本文将解这个技术。
最简单的解决方案是在每个相关的工作表中使用辅助列,即首先将相关的单元格值连接并放置在辅助列中。然而,有时候我们可能不能在工作表中使用辅助列,特别是要求在被查找的表左侧插入列时。因此,本文会提供一种不使用辅助列的解决方案。
下面是3个示例工作表:
:工作表Sheet1
:工作表Sheet2
:工作表Sheet3
示例要求从这3个工作表中从左至右查找,返回Colour列中为“Red”对应的Amount列中的值,如下所示。
:主工作表Master
数组公式如下:
=VLOOKUP($A3,INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT(“‘”&Sheets&”‘!B:B”),$A3)>0,0))&”‘!B1:D10″),3,0)
其中,Sheets是定义的名称:
名称:Sheets
引用位置:={“Sheet1″,”Sheet2″,”Sheet3”}
在公式中使用的VLOOKUP函数与平常并没有什么不同,我们首先需要确定在哪个工作表中进行查找,因此我们使用的函数应该能够操作三维单元格区域,而COUNTIF函数就可以。公式中的:
COUNTIF(INDIRECT(“‘”&Sheets&”‘!B:B”),$A3)
转换为:
COUNTIF(INDIRECT(“‘”&{“Sheet1″,”Sheet2″,”Sheet3″}&”‘!B:B”),$A3)
转换为:
COUNTIF(INDIRECT({“‘Sheet1’!B:B”,”‘Sheet2’!B:B”,”‘Sheet3’!B:B”}),$A3)
INDIRECT函数指令Excel将这个文本字符串数组中的元素转换为单元格引用,然后传递给COUNTIF函数,同时单元格A3中的值作为其条件参数,这样上述公式转换成:
{0,1,3}
分别代表工作表Sheet1、Sheet2、Sheet3的列B中“Red”的数量。
因为我们想得到第一个匹配的结果,所以将该数组传递给MATCH函数:
MATCH(TRUE,COUNTIF(INDIRECT(“‘”&Sheets&”‘!B:B”),$A3)>0,0)
转换为:
MATCH(TRUE,{0,1,3}>0,0)
转换为:
MATCH(TRUE,{FALSE,TRUE,TRUE},0)
结果为:
2
因此,将在工作表列表中的第2个工作表即Sheet2中执行VLOOKUP操作。
现在,将上面的结果作为参数值传递给INDEX函数:
INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT(“‘”&Sheets&”‘!B:B”),$A3)>0,0))
转换为:
INDEX(Sheets,2)
转换为:
INDEX({“Sheet1″,”Sheet2″,”Sheet3”},2)
结果为:
Sheet2
这里,需要使用INDIRECT函数进一步构造来生成传递给VLOOKUP函数的单元格区域,因此:
=VLOOKUP($A3,INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT(“‘”&Sheets&”‘!B:B”),$A3)>0,0))&”‘!B1:D10″),3,0)
转换为:
=VLOOKUP($A3,INDIRECT(“‘Sheet2″&”‘!B1:D10”),3,0)
转换为:
=VLOOKUP($A3,INDIRECT(“‘Sheet2′!B1:D10”),3,0)
转换为:
=VLOOKUP($A3,’Sheet2’!B1:D10,3,0)
得到结果:
55
猜你喜欢
- 1.打开数据表,点击插入,对象2.选中word图片。3.在弹出的对话框中随意输入字母A4.然后将字母选黑,选择格式,中文版式,带圈字符5.然
- Excel如何查看公式明细?excel表格中的数据是公式算出来的,想要显示算出数据的公式,该怎么显示呢?下面我们就来看看Excel表格显示公
- 今天就把office 2013的安装使用详细步骤教程给大家制作了出来,同样此office 2013安装教程目前只针对与本站下载的office
- 很多在准备论文的网友,最头痛的除了论文的撰写之外,还有就是论文目录的编写。有时候论文内容再精彩,也可能因为论文目录编写得不好,被论文辅导老师
- 为了更加充分且合理的利用好设备的内存空间,将一些不重要的、不常用的系统服务给禁用掉会是个不错的选择。那么,在Win10 1909系统中要怎么
- Word文档页码怎么设置?近期有用户在在word文档中插入页码时,不想让封面,或者目录等等显示页码,那该如何设置呢?下面小编就为大家介绍Wo
- EXCEL怎么自动填充表格数据长度为填充指定位数?有时候数据要求必须是6位数或某个固定的位数,该怎么把长度不够的数据前面自动填充0到指定的长
- 当用户需要突出显示大于、小于、等于、介于某个数值或数据区域的数据,或是需要突出显示数值位居前几项以及高于、低于平均值的数据时,使用条件格式中
- Excel怎么凸出特定值?Excel表格中数据很多,想要根据条件来凸出数据,该怎么设置呢?下面我们就来看看Excel筛选符合数据的教程,需要
- 在Word中,其实要完成一个表格,还要经过很多单元格的合并和拆分。这些操作对于使用过Word的朋友来说,都不算难,但是对于刚学
- 问:我是一名学生,现在老师上课一般都用PPT课件上课,我打算把老师课件打印出来复习。但是PPT直接打印效果不好,而且包含一些不需要的图片等等
- 硬盘分区可以有效帮助用户对电脑硬盘进行规划,也方便用户管理。但是最近有win10用户反应自己电脑的固态硬盘分区不见了,这是怎么回事?还可以恢
- 人民币符号相信大家都见过,可能大家见到的是多种表现方式的人民币符号,如RMB、CNY、¥等均表示人民币符号,那么word人民币符号怎么打?下
- 在我们使用手机的时候经常会用到闹钟这个功能,起到了一定的提醒用户的作用,那么在电脑中也是可以设置闹钟的,Win10系统的电脑就有这个设置闹钟
- 在Word2010中进行查找操作时,默认情形下每次只显示一个查找到的目标。用户也可以通过抉择查找选项同时显示所有查找到的内容。同时显示的目标
- 许多小伙伴买了新电脑,高兴地打开了电脑,却发现电脑网速很慢,体验很差,新电脑一般都能排除病毒和垃圾的原因。但也可以试试,还有可能是电脑设定了
- 升级 watchOS 7 后,Apple Watch 会通过一系列引导,让你打开睡眠功能。我们也可以在 Apple Watch 的「睡眠」应
- Word 2003 让图片快速显示大家在使用Word 2003时,会发现一个问题,如果文章内容多,里面插入的图片显示会很慢,那有什么办法来解
- 当你将手上的设备升级到 iOS 14 之后,就会惊喜地发现,Siri 再也不会跳出来占据整个设备屏幕了,而是将自己放置在屏幕下方,等待和聆听
- Excel中数据太多会导致混乱一片,最终导致我们找数据时非常困难。不妨试试给重要的单元格添加简介和说明,这样以后只需要将鼠标放到某个单元格上