Excel常用函数之VLOOKUP函数查找多个工作表的数据
从近期与大家的交流看,还有很多朋友对VLOOKUP函数不是很熟悉。因此,准备详细的介绍一下这个函数的使用。VLOOKUP函数功能非常强大,使用场景也变化很大,因此准备分为几篇来介绍:
VLOOKUP函数精解(已发,详情阅读这里)
VLOOKUP排错(已发,详情阅读这里)
VLOOKUP函数动态返回不同的列(已发,详情阅读这里)
VLOOKUP函数使用多个条件(已发,详情阅读这里)
VLOOKUP函数返回多个值
VLOOKUP函数反向查找
VLOOKUP函数查找多个工作表的数据
今天是第7篇,VLOOKUP查找多个工作表中的数据。
01
场景介绍
VLOOKUP从一个区域中查找相应的数值,已经是非常熟悉的应用了。但是,有的时候,我们需要查找的区域可能在多个不同的工作表上:
但是这个产品与大类的对应表存在多个表上:
上面是A工厂产品的资料表,还有一些在B工厂的表格中:
还有一些产品在其他工厂的资料表中。
如果我们写VLOOKUP公式,只能返回一个表格区域的结果。
02
使用VLOOKUP查找多个表
我们需要使用一个复杂的公式。
首先,在表格中创建一个区域,输入所有的工作表名称:
然后在C2单元格中输入公式:
=VLOOKUP(B3,INDIRECT("'"&INDEX($H$3:$H$5,MATCH(1,--(COUNTIF(INDIRECT("'"&$H$3:$H$5&"'!$B$3:$C$100"),B3)>0),0))&"'!$B$3:$C$100"),2,FALSE)
注意这是一个数组公式,所以需要按Ctrl+Shift+Enter来输入。然后填充到整列,得到结果:
下面简单解释一下这个公式:
=VLOOKUP(B3,INDIRECT("'"&INDEX($H$3:$H$5,MATCH(1,--(COUNTIF(INDIRECT("'"&$H$3:$H$5&"'!$B$3:$C$100"),B3)>0),0))&"'!$B$3:$C$100"),2,FALSE)
公式中紫色背景的部分就是我们添加的辅助区域,用于记录存放产品资料的工作表名称。所以,下面公式中紫色背景部分:
=VLOOKUP(B3,INDIRECT("'"&INDEX($H$3:$H$5,MATCH(1,--(COUNTIF(INDIRECT("'"&$H$3:$H$5&"'!$B$3:$C$100"),B3)>0),0))&"'!$B$3:$C$100"),2,FALSE)
就是去分别计算每个工作表中是否包含B3这个单元格的值,如果包含单元格的值的话,计数就大于0,否则返回0
=VLOOKUP(B3,INDIRECT("'"&INDEX($H$3:$H$5,MATCH(1,--(COUNTIF(INDIRECT("'"&$H$3:$H$5&"'!$B$3:$C$100"),B3)>0),0))&"'!$B$3:$C$100"),2,FALSE)
这样,这个逻辑表达式就会返回每个工作表是否含有要查找的值,比如,这里的结果就是:{1;0;0},
然后Match函数就会找到那个包含查找值的序号:
=VLOOKUP(B3,INDIRECT("'"&INDEX($H$3:$H$5,MATCH(1,--(COUNTIF(INDIRECT("'"&$H$3:$H$5&"'!$B$3:$C$100"),B3)>0),0))&"'!$B$3:$C$100"),2,FALSE)
这里的结果就是1,也就是第一个工作表包含要查找的值。
于是,下面的阴影部分就找到了这个工作表名称:
=VLOOKUP(B3,INDIRECT("'"&INDEX($H$3:$H$5,MATCH(1,--(COUNTIF(INDIRECT("'"&$H$3:$H$5&"'!$B$3:$C$100"),B3)>0),0))&"'!$B$3:$C$100"),2,FALSE)
然后,INDIRECT函数引用相应区域:
=VLOOKUP(B3,INDIRECT("'"&INDEX($H$3:$H$5,MATCH(1,--(COUNTIF(INDIRECT("'"&$H$3:$H$5&"'!$B$3:$C$100"),B3)>0),0))&"'!$B$3:$C$100"),2,FALSE)
剩下的就是一个非常简单的VLOOKUP了。
这是一个复杂的IF公式,即使对于很多Excel老手来说,也不是一眼就能明白这个公式,更不用说遇到问题时自己写出这样的公式了。
所以,我一般不推荐使用这种公式解决问题。这里的公式主要是写在这里以备紧急情况下你可以参考,解决燃眉之急。
更好的做法是学习使用其他的方法,这样你就可以自己解决这个问题。
你能想到不使用公式就解决这个问题的办法吗?提示一下,使用Power Query。如果你想到了怎么做,可以留言告诉我。