VLOOKUP函數怎么查找匹配值

VLOOKUP函數怎么查找匹配值

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

下面是3個示例工作表：

圖1：工作表Sheet1

圖2：工作表Sheet2

圖3：工作表Sheet3

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

圖4：主工作表Master

解決方案1：使用輔助列

可以適當修改上篇文章中給出的公式，使其可以處理這里的情形。首先在每個工作表數據區域的左側插入一個輔助列，該列中的數據為連接要查找的兩個列中數據。這樣，獲取值的數組公式（單元格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函數在多個工作表中查找相匹配的值（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中的數組公式如下：

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

可以轉換為：

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

轉換為：

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

結果為：

3

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

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

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

轉換為：

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

轉換為：

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

轉換為：

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

轉換為：

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

結果為：

Sheet3!D1:D10

傳遞到INDEX函數中作為其參數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

現在，單元格C3中的公式變為：

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

由于這里的兩個公式結構：

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

和

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

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

先看看名稱Arry2：

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

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

上述公式轉換為：

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

得到：

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

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

OFFSET(Sheet3!B1,Arry2,,,)

將會生成：

Sheet3!B1

Sheet3!B2

Sheet3!B3

…

Sheet3!B10

因此，公式：

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

轉換為：

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

轉換為：

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

轉換為：

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

轉換為：

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

得到：

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

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

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

其結果將為：

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

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

OFFSET(Sheet3!B1,Arry2,,,)

其結果將為：

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

同樣地，公式中的：

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

轉換為：

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

結果為：

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

好了！現在可以將上面得到的中間結果放到主公式中：

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

轉換為：

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

轉換為：

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

轉換為：

=INDEX(Sheet3!D1:D10,5)

結果為

32

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

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