Excel公式與函數之美19：理解VLOOKUP函數第4個參數的真實含義

VLOOKUP函數是大家最喜歡使用的函數之一，能夠幫助我們實現很多任務。這裡，讓我們回過頭來從細節處著手，再次深入探究VLOOKUP函數，進一步熟練掌握這個常用的函數。

一鍵直達 >> Excel函數學習4：VLOOKUP函數

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

VLOOKUP函數有4個參數，其中第4個參數是可選參數，如果我們在公式中省略這個參數，有時會得到錯誤的結果。如圖1所示，在單元格區域A2:B6中查找編號為「2010」的名稱，結果是「洗衣機」，很顯然是錯誤的，應該是「冰箱」。

圖1

如果將編號排序，就會得到正確的結果「冰箱」，如圖2所示。

圖2

在VLOOKUP函數中，雖然第4個參數可省略，但省略後其默認為TRUE，即近似匹配，要求所查找的數據必須按升序排列，否則就會得出意想不到的結果，如圖1所示。

如果查找的數據列沒有按升序排列，則應顯式指定第4個參數值為FALSE，即精確匹配，才能得到正確的結果，如圖3所示。

圖3

因此，當VLOOKUP函數的第4個參數被忽略或者為TRUE時，將執行近似查找，所查找的數據列必須要按升序排列，以獲得正確的結果。反之，如果所查找的數據列沒有排序，那麼VLOOKUP函數的第4個參數應設置為FALSE，執行精確查找。

下面再進一步探究。

如圖4是一個收入—稅率表。

圖4

從圖4中可以看出，如果某員工的收入是5000元，那麼他要交稅的稅率為5%。如果另一位員工的收入是18000，他就要交稅率為15%的稅。

就上述兩種情形來說，圖4的表中沒有一個精確的數字和實際收入相匹配，只能查找到實際收入介於的區間，從而找到相應的稅率。這就是VLOOKUP函數第4個參數的真實含義，這也是為什麼該參數的英文名為「range_lookup」。

因此，當VLOOKUP函數的第4個參數是TRUE或者被忽略時，告訴Excel去執行一個區間查找。進一步說，就是查找圖4列A中的值，該值要等於或者小於但最接近所查找的值。因此，圖4中的查找表可以簡化為下圖5.

圖5

此時，使用第4個參數為TRUE或者忽略的VLOOKUP函數查找時，當查找的值大於等於0且小於3000時，返回稅率為0；當查找的值大於等於3000且小於8000時，返回稅率5%；依此類推。

這也是為什麼查找列的數據必須要按照升序排列的原因。

示例1：基本的查找

如圖6所示，要求查找收入為10000元的員工要交稅的稅率，公式為：

=VLOOKUP(A10,A2:B6,2,TRUE)

圖6

示例2：查找日期

VLOOKUP函數能夠查找最接近的日期，如圖7所示。在單元格E2中輸入公式：

=VLOOKUP(D2,$A$2:$B$4,2,TRUE)

向下拉至單元格E6。

圖7

示例3：單列查找

查找表可以僅有1列，從這1列中查找並返回區間開始點的值。如圖8所示，為2018年2月份每周一所在的日期。

圖8

現在，在圖8所示的查找表中查找指定的日期並返回該日期所在周一的日期，結果如圖9所示。

圖9

在單元格D2中的公式為：

=VLOOKUP(C2,$A$2:$A$6,1,TRUE)

向下拖至單元格D7。

示例4：比較兩個列表

有時，我們需要比較兩個列表，確定一個列表中包含另一個列表中的哪些項目。如圖10所示，有「庫存」表和「用戶需求」表，要求根據「用戶需求」表中的編號來查找「庫存」表中相應編號的價格並將找到的結果輸入到「用戶需求」表中。

圖10

可以使用VLOOKUP函數來完成，如圖11所示。

圖11

在單元格F3中輸入公式：

=VLOOKUP(D3,$A$3:$B$6,2)

下拉至單元格F8。

咋一看，似乎任務完成！但仔細看一下，發現「用戶需求」表中的編號1003和1005在「庫存」表中不存在，但仍然得到了結果。閱讀到這裡的讀者應該知道，公式中的VLOOKUP函數的第4個參數忽略，告訴Excel執行近似（區間）查找，此時Excel會找到小於但最接近查找值的值並返回相應的結果。但我們的這個示例中，並不需要這樣。我們想要的是，如果找到的數據，就返回相應的值，沒有找到就算了。

此時，應該將VLOOKUP函數的第4個參數明確設置為FALSE，當沒有找到數據時，返回#N/A。如下圖12所示。

圖12

在單元格F3中的公式為：

=VLOOKUP(D3,$A$3:$B$6,2,FALSE)

現在，我們已經可以清楚地看到哪些編號對應的價格是「庫存」表中沒有的，因為它們會顯示#N/A。

下面，我們稍作改進，讓表格更加清楚地呈現結果，如圖13所示。

圖13

在單元格F3中的公式為：

=IFERROR(VLOOKUP(D3,$A$3:$B$6,2,FALSE),"沒有庫存")

結語

在使用VLOOKUP函數時，你不能忽視其第4個參數，如果使用不恰當，會誤導得出不正確的結果。然而，如果理解了第4個參數的真實含義並恰當運用，不僅能夠更合理地使用VLOOKUP函數，而且可以有趣地使用VLOOKUP函數。

本文整理自excel-university.com，轉載請註明出處。

歡迎在下面留言，完善本文內容，讓更多的人學到更完美的知識。