理解VLOOKUP函數第4個參數的真實含義

理解VLOOKUP函數第4個參數的真實含義

VLOOKUP函數是大家最喜歡使用的函數之一，能夠幫助我們實現很多任務。這里，讓我們回過頭來從細節處著手，再次深入探究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函數。

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

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