VLOOKUP在多個工作表中查找相匹配的值

VLOOKUP在多個工作表中查找相匹配的值

我們給出了基于在多個工作表給定列中匹配單個條件來返回值的解決方案。本文使用與之相同的示例，但是將匹配多個條件，并提供兩個解決方案：一個是使用輔助列，另一個不使用輔助列。

下面是3個示例工作表：

圖1：工作表Sheet1

圖2：工作表Sheet2

圖3：工作表Sheet3

示例要求從這3個工作表中從左至右查找，返回Colour列中為“Red”且“Year”列為“2012”對應的Amount列中的值，如下圖4所示的第7行和第11行。

圖4：主工作表Master

解決方案1：使用輔助列

可以適當修改上篇文章中給出的公式，使其可以處理這里的情形。首先在每個工作表數(shù)據(jù)區(qū)域的左側(cè)插入一個輔助列，該列中的數(shù)據(jù)為連接要查找的兩個列中數(shù)據(jù)。這樣，獲取值的數(shù)組公式（單元格C7）如下：

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

其中，Sheets是定義的名稱：

名稱：Sheets

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

這個公式的運行原理與上文相同，可參見《Excel公式技巧16：使用VLOOKUP函數(shù)在多個工作表中查找相匹配的值（1）》。

解決方案2：不使用輔助列

首先定義兩個名稱。注意，在定義名稱時，將活動單元格放置在工作表Master的第11行。

名稱：Arry1

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

名稱：Arry2

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

在單元格C11中的數(shù)組公式如下：

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

下面來看看公式是怎么運作的。首先看看名稱Arry1：

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

可以轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

=MATCH(TRUE,{0,0,1}>0,0)

結(jié)果為：

3

表明在工作表列表的第3個工作表（即Sheet3）中進行查找。

因此，在單元格C11的公式中的：

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

轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

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

結(jié)果為：

Sheet3!D1:D10

傳遞到INDEX函數(shù)中作為其參數(shù)array的值：

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

同樣，公式中的：

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

得到：

Sheet3!B1

公式中的：

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

得到：

Sheet3!C1

現(xiàn)在，單元格C3中的公式變?yōu)椋?/p>

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

由于這里的兩個公式結(jié)構(gòu)：

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

和

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

相似，因此只解釋其中一個的工作原理。

先看看名稱Arry2：

=ROW(INDIRECT(“1:10”))-1

由于將在三個工作表中執(zhí)行查找的范圍是從第1行到第10行，因此公式中使用了1:10。

上述公式轉(zhuǎn)換為：

{1;2;3;4;5;6;7;8;9;10}-1

得到：

{0;1;2;3;4;5;6;7;8;9}

該數(shù)組被傳遞給OFFSET函數(shù)作為其rows參數(shù)，這樣：

OFFSET(Sheet3!B1,Arry2,,,)

將會生成：

Sheet3!B1

Sheet3!B2

Sheet3!B3

…

Sheet3!B10

因此，公式：

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

轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

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

得到：

{FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE}

注意，如果你在這里使用的是N函數(shù)：

N(OFFSET(Sheet3!B1,Arry2,,,))

其結(jié)果將為：

{0,0,0,0,0,0,0,0,0,0}

當然，也不能夠單獨只使用OFFSET函數(shù)：

OFFSET(Sheet3!B1,Arry2,,,)

其結(jié)果將為：

{#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}

同樣地，公式中的：

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

轉(zhuǎn)換為：

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

結(jié)果為：

{FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE}

好了！現(xiàn)在可以將上面得到的中間結(jié)果放到主公式中：

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

轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

=INDEX(Sheet3!D1:D10,5)

結(jié)果為

32

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔相應法律責任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。