使用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
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Spire.Doc 教程:用C 识别Word中的合并字段名称
![](https://img.aspxhome.com/file/2023/3/34233_0s.png)
win10为什么装不回win7?win10装不回win7的解决方法
![](https://img.aspxhome.com/file/2023/3/47743_0s.jpg)
如何在Word中输入11至20带圈字符?
![](https://img.aspxhome.com/file/2023/0/33910_0s.jpg)
word如何实现复制文本框到多页?
![](https://img.aspxhome.com/file/2023/4/29074_0s.png)
重装系统后怎么还原旧系统?重装系统后想还原回旧系统要怎么弄?
![](https://img.aspxhome.com/file/2023/1/47251_0s.jpg)
1分钟学会 9个非常使用的Word文本操作技巧
![](https://img.aspxhome.com/file/2023/1/24031_0s.jpg)
Word文档如何添加带滚动条的文本框 Word滚动条文本设置方法
![](https://img.aspxhome.com/file/2023/7/22557_0s.gif)
Word表格 批量写入操作技巧
Excel通过身份证号识别性别
![](https://img.aspxhome.com/file/2023/0/37740_0s.png)
word文档不能选中,不能复制,不能编辑,是怎么回事
![](https://img.aspxhome.com/file/2023/1/35301_0s.png)
iOS 15有哪些新功能?iOS 15新功能预测
![](https://img.aspxhome.com/file/2023/6/45926_0s.gif)
Excel如何批量将单元格中的数值提取出来?
![](https://img.aspxhome.com/file/2023/7/a141687_0s.gif)
win10电脑ip地址怎么改?win10电脑ip地址修改教程
![](https://img.aspxhome.com/file/2023/5/47985_0s.png)
excel如何调整文档的显示比例?
![](https://img.aspxhome.com/file/2023/6/41316_0s.jpg)
如何在Word 2016中删除样式格式?
![](https://img.aspxhome.com/file/2023/6/23566_0s.jpg)
Win10电脑显卡驱动安装失败怎么办?显卡驱动安装失败解决办法
![](https://img.aspxhome.com/file/2023/9/49989_0s.png)
office E5刷API脚本分享以及教程
![](https://img.aspxhome.com/file/2023/7/16537_0s.png)
Word 中怎么排版?这么多种办法有一款能应用于您的场景
![](https://img.aspxhome.com/file/2023/6/17176_0s.png)
word 中编号与正文之间过大的间隔距离恼火不已,不要上火,今天我将为大家一一分解
Win10系统怎么自定义系统还原点?
![](https://img.aspxhome.com/file/2023/9/51029_0s.png)