VLOOKUP函数如何在多个工作表中查找相匹配的值

一、VLOOKUP函数如何在多个工作表中查找相匹配的值

1、我们给出了基于在多个工作表给定列中匹配单个条件来返回值的解决方案。本文使用与之相同的示例，但是将匹配多个条件，并提供两个解决方案：一个是使用辅助列，另一个不使用辅助列。

2、示例要求从这3个工作表中从左至右查找，返回Colour列中为“Red”且“Year”列为“2012”对应的Amount列中的值，如下图4所示的第7行和第11行。

3、可以适当修改上篇文章中给出的公式，使其可以处理这里的情形。首先在每个工作表数据区域的左侧插入一个辅助列，该列中的数据为连接要查找的两个列中数据。这样，获取值的数组公式（单元格C7）如下：

4、=VLOOKUP(A7&””&B7,INDIRECT(“‘”&INDEX(Sheets,MATCH(TRUE,COUNTIF(INDIRECT(“‘”&Sheets&”‘!A:A”),A7&””&B7)>0,0))&”‘!A1:D10″),4,0)

5、引用位置：={“Sheet1″,”Sheet2″,”Sheet3”}

6、这个公式的运行原理与上文相同，可参见《Excel公式技巧16：使用VLOOKUP函数在多个工作表中查找相匹配的值（1）》。

7、首先定义两个名称。注意，在定义名称时，将活动单元格放置在工作表Master的第11行。

8、引用位置：=MATCH(TRUE,COUNTIFS(INDIRECT(“‘”&Sheets&”‘!B:B”),$A11,INDIRECT(“‘”&Sheets&”‘!C:C”),$B11)>0,0)

9、引用位置：=ROW(INDIRECT(“1:10”))-1

10、=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))

11、下面来看看公式是怎么运作的。首先看看名称Arry1：

12、=MATCH(TRUE,COUNTIFS(INDIRECT(“‘”&Sheets&”‘!B:B”),$A11,INDIRECT(“‘”&Sheets&”‘!C:C”),$B11)>0,0)

13、=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)

14、表明在工作表列表的第3个工作表（即Sheet3）中进行查找。

15、INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!D1:D10”)

16、INDIRECT(“‘”&INDEX(Sheets,3)&”‘!D1:D10”)

17、INDIRECT(“‘”&INDEX({“Sheet1″,”Sheet2″,”Sheet3″},3)&”‘!D1:D10”)

18、INDIRECT(“‘”&”Sheet3″&”‘!D1:D10”)

19、INDIRECT(“‘Sheet3’!D1:D10”)

20、传递到INDEX函数中作为其参数array的值：

21、=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))

22、INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!B1”)

23、INDIRECT(“‘”&INDEX(Sheets,Arry1)&”‘!C1”)

24、=INDEX(Sheet3!D1:D10,MATCH(1,(T(OFFSET(Sheet3!B1,Arry2,,,))=$A11)*(N(OFFSET(Sheet3!C1,Arry2,,,))=$B11),0))

25、T(OFFSET(Sheet3!B1,Arry2,,,))=$A11

26、N(OFFSET(Sheet3!C1,Arry2,,,))=$B11

27、相似，因此只解释其中一个的工作原理。

28、由于将在三个工作表中执行查找的范围是从第1行到第10行，因此公式中使用了1:10。

29、该数组被传递给OFFSET函数作为其rows参数，这样：

30、T(OFFSET(Sheet3!B1,Arry2,,,))=$A11

31、T(OFFSET(Sheet3!B1,{0,1,2,3,4,5,6,7,8,9},,,))=$A11

32、T({Sheet3!B1,Sheet3!B2,Sheet3!B3,Sheet3!B4,Sheet3!B5,Sheet3!B6,Sheet3!B7,Sheet3!B8,Sheet3!B9,Sheet3!B10})=$A11

33、{“Colour”,”Red”,”Blue”,”Blue”,”Red”,”Violet”,”Blue”,”Yellow”,”Green”,”Red”}=$A11

34、{“Colour”,”Red”,”Blue”,”Blue”,”Red”,”Violet”,”Blue”,”Yellow”,”Green”,”Red”}=”Red”

35、{FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE}

36、注意，如果你在这里使用的是N函数：

37、当然，也不能够单独只使用OFFSET函数：

38、{#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}

39、N(OFFSET(Sheet3!C1,Arry2,,,))=$B11

40、{0,2010,2010,2012,2012,2012,2012,2011,2014,2011}=2012

41、{FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE}

42、好了！现在可以将上面得到的中间结果放到主公式中：

43、=INDEX(Sheet3!D1:D10,MATCH(1,(T(OFFSET(Sheet3!B1,Arry2,,,))=$A11)*(N(OFFSET(Sheet3!C1,Arry2,,,))=$B11),0))

44、=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))

45、=INDEX(Sheet3!D1:D10,MATCH(1,{0,0,0,0,1,0,0,0,0,0},0))

二、Excel VLOOKUP函数怎么查询一个值返回多个结果

1、在原始数据中A列有多个“张三丰”，需要提取每个“张三丰”对应的“工号”。

2、一般情况的VLOOKUP只能返回第一个值，如下：=VLOOKUP(A8,A1:D5,2,0)

3、在【姓名】列前插入一列输入公式：=COUNTIF(B$2:B2,B2)

4、将A2公式改成：=B2&COUNTIF(B$2:B2,B2)这样就将B列的“姓名”和出现次数连在一起。

5、在B8输入公式：=A8&ROW(A1)将A8值和ROW(A1)返回的1连在一起，下拉ROW(A1)会变成ROW(A2)返回2。

6、将B8的公式修改成如下：=VLOOKUP(A$8&ROW(A1),A$1:E$5,3,0)也就是在A1:E5范围内查询“张三丰”和出现次数的文本对应第3列的【工号】。

7、将公式下拉，就将所有“张三丰”对应的信息查询出来了。

三、如何在一个工作表内用vlookup函数查找另一个工作表数据

公式：=VLOOKUP(条件1：条件2,IF({1,0},$条件1区域$：$条件2区域$,返回列),2,0)。注意：最后一定要同时按住ctrl shift回车三键。具体操作方法如下：

1、VLOOKUP公式中的条件1：条件2,意思是将两个单元格连在一起作为整体进行查找。图中即为：F2:G2。

2、VLOOKUP公式中的$条件1区域$：$条件2区域$，意思是将业务类型和订单编号作为整体。即图中：$A$2:$B$9（使用$锁定区域-绝对引用）。

3、VLOOKUP公式中的IF({1,0},$条件1区域$：$条件2区域$,返回列)，意思是将业务类型和订单编号作为整体和计划到货日期并为两列。即为：IF({1,0},$A$2:$B$9,$C$2:$C$9)

4、然后我们根据公式VLOOKUP(条件1：条件2,IF({1,0},$条件1区域$：$条件2区域$,返回列),2,0)。在H2单元格内录入此公式=VLOOKUP(F2：G2,IF({1,0},$A2$：$B9$,$C$2:$C$9),2,0)。

5、最后同时按住ctrl、shift、回车三键，OK。