Excel公式與函數(shù)之美20：MATCH函數(shù)使VLOOKUP函數(shù)更靈活高效

L羅樂 2018-03-17

展開全文

這篇文章中，我們將探討靈活利用VLOOKUP函數(shù)的第3個參數(shù)的有效方法。

一鍵直達>>Excel函數(shù)學習1：MATCH函數(shù)

一鍵直達>> Excel函數(shù)學習4：VLOOKUP函數(shù)

一鍵直達 >> Excel公式與函數(shù)之美15：VLOOKUP函數(shù)的秘密

一鍵直達>>Excel 公式與函數(shù)之美16：MATCH函數(shù)的幾個秘密

一鍵直達>>Excel公式與函數(shù)之美19：理解VLOOKUP函數(shù)第4個參數(shù)的真實含義

目的

VLOOKUP函數(shù)的第3個參數(shù)指定想要返回的值的位置。例如，如果想要從所查找區(qū)域的第2個位置或第2列返回金額，則應指定該參數(shù)為2。

圖1

從圖1所示的工作表中的“表1”中返回第1列的金額，在B8中使用公式：

=VLOOKUP(A8,表1,2,0)

然而，如果想要使用列標題（如本例中的“金額”）而不是整數(shù)值2與Excel進行交互，會得到什么結果，例如

=VLOOKUP(A8,表1,”金額”,0)

返回錯誤。

顯然，Excel不允許使用列標題來引用列。難道不是嗎？

技巧

實際上，我們此時要做的就是如何將列標題（金額）轉換成相應的整數(shù)（2）。

技巧：對第3個參數(shù)使用MATCH函數(shù)來代替整數(shù)

MATCH函數(shù)返回列表項的相對位置。因此，要求MATCH函數(shù)在表的標題行查找相應標題（金額），返回其位置值。接著，VLOOKUP函數(shù)使用該位置值。

例如，因為“金額”在表中的第2列，所以下面的公式返回2：

=MATCH('金額',表1[#標題],0)

在示例中，可以使用下面的公式：

=MATCH(B7,表1[#標題],0)

這樣，要使用列標題來查找相應的值，上文中的公式就可變?yōu)椋?/span>

=VLOOKUP(A8,表1,MATCH(B7,表1[#標題],0),0)

這個技巧允許引用列標題來代替位置值。下面是這項技術的一些有趣的應用。

示例1：改變列順序

如果使用數(shù)字作為VLOOKUP函數(shù)的第3個參數(shù)，那么當所查找表的列順序改變時，找到的數(shù)據(jù)將不是想要的數(shù)據(jù)，因為Excel不會自動更新相應的數(shù)字。然而，使用MATCH函數(shù)代替數(shù)字作為VLOOKUP函數(shù)的第3個參數(shù)，可以得到正確的結果，這使得工作簿更靈活有效。

如圖2所示，開始時要求查找第2列中的相應數(shù)據(jù)。

圖2

但是，如果將圖2中的第2列和第3列交換，如圖3所示，使用MATCH函數(shù)代替2作為VLOOKUP函數(shù)的第3個參數(shù)，能夠確保在列的順序發(fā)生改變時，仍然獲得正確結果。

圖3

示例2：插入新的列

如果在查找的表中所要查找的列前插入新列，那么使用數(shù)字作為參數(shù)的VLOOKUP函數(shù)的結果將會改變，不會得到想要的數(shù)據(jù)。但是，使用MATCH函數(shù)代替數(shù)字作為參數(shù)，則不會受到插入新列的影響。

如圖4所示，要返回第5列的數(shù)據(jù)。

圖4

在第5列前插入一個新列后，原來的第5列變成了現(xiàn)在的第6列，如圖5所示，公式不需要修改，結果仍然不變。

圖5

示例3：二維查找

使用傳統(tǒng)的VLOOKUP函數(shù)，只能垂直查找匹配的值。然而，配合使用MATCH函數(shù)，可以實現(xiàn)二維查找，其中，VLOOKUP函數(shù)查找行，而MATCH函數(shù)查找列。

如下圖6所示的工作表，想要獲取某本書在某電商網(wǎng)站的價格。在單元格B7中輸入電商網(wǎng)站名，在單元格A7中是要查找的圖書名。此時，使用VLOOKUP函數(shù)查找圖書在表中的行，而使用MATCH函數(shù)查找電商網(wǎng)站所在的列，從而獲取表中的價格數(shù)據(jù)。