|
从近期与大家的交流看，还有很多朋友对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。如果你想到了怎么做，可以留言告诉我。
|
|
|
