引言：在Excel中，VLOOKUP函數應該是最受關注的函數之一了。關於VLOOKUP函數，在網上有數不清的討論和文章。這裡介紹的是DAVE BRUNS編寫的一篇有代表性的文章《23things you should know about VLOOKUP》，我對其進行了一些整理，供大家進一步理解和運用VLOOKUP函數時參考。
當你想從表中提取信息時，Excel的VLOOKUP函數是一個很好的解決方案。從表中動態查找和獲取信息的能力給許多用戶帶來了全新的改變，你到處都可以發現VLOOKUP。
儘管VLOOKUP相對容易使用，但也容易出錯。其中一個原因是VLOOKUP有一個主要的設計缺陷，默認情況下，假定你認為的是近似匹配，但這可能並不是你的本意。因此，會導致看起來結果正常但實際上是錯誤的。
1.VLOOKUP是如何運行的
VLOOKUP是一個查找函數，可以獲取表中的數據。在VLOOKUP中的「V」代表垂直，意味著表中的數據必須垂直地排列，即數據在行中。
如果你有一個結構良好的表，信息垂直排列，左邊有一列可以用來匹配查找的數據，那麼可以使用VLOOKUP。
VLOOKUP要求表結構化，在左側列中顯示查找值，在右側任意列中顯示想要獲取的數據（結果值）。當使用VLOOKUP時，假設表中的每列都從左側（查找列）列始編號。要從特定列中獲取值，簡單地提供合適的數字作為「列索引」即可。在下面的例子中，查找電子郵件地址，因此使用數字4作為列索引：
圖1
在圖1所示的表中，員工ID位於左側第1列，電子郵件地址在右側的第4列。
要使用VLOOKUP，要提供4個參數：
要查找的值（lookup_value）
組成表的單元格區域（table_array）
要獲取的結果所在的列編號（column_index）
匹配模式（range_lookup，TRUE=近似匹配，FALSE=精確匹配）
2.VLOOKUP僅向右查找
可能VLOOKUP最大的局限是僅能向右查找來獲取數據。這意味著VLOOKUP僅能獲取表中第一列右側列的數據。當查找值在第一列（最左側列）時，這個限制沒有多大意義，因為所有其他列已經在右側。但是，如果查找列在表裡的某個位置，則只能從該列右側的列中查找值。還必須為VLOOKUP提供一個以查找列開始的更小的表。
圖2
使用INDEX和MATCH代替VLOOKUP可以克服這個局限。
3.VLOOKUP總是查找第一個匹配值
如果查找列包含重複值，那麼VLOOKUP將僅匹配找到的第1個值。如果表中的第1列沒有重複值，這顯然不是問題。但是，如果第1列包含重複值，那麼VLOOKUP將僅匹配第1個值。例如，使用VLOOKUP查找名字，雖然表中有兩個「Janet」，但VLOOKUP僅匹配第1個：
圖3
4.VLOOKUP不區分大小寫
查找值時，VLOOKUP不會處理大寫和小寫文本差異。對於VLOOKUP，產品代碼「PQRF」與「pqrf」相同。下面的示例中，查找大寫的「JANET」，但VLOOKUP不會區分大小寫，因此簡單地匹配「Janet」，因為這是找到的第1個匹配：
圖4
5.VLOOKUP有兩種匹配模式
VLOOKUP有兩種操作模式：完全匹配和近似匹配。大多數情況下，可能想使用VLOOKUP的完全匹配模式。當你想要基於某種唯一鍵（例如，基於產品代碼的產品信息或者基於電影名稱的電影數據）查找信息時，這是有意義的：
圖5
在單元格H6中的公式基於完全匹配電影名稱來查找年：
=VLOOKUP(H4,B5:E9,2,FALSE)
但是，如果不匹配唯一ID，而是查找「最佳匹配」或者「最佳類別」，則需要使用近似匹配。例如，可能要根據重量查找郵資，根據收入查找稅率，根據每月銷售額查找佣金率。在這些情況下，可能無法在表中找到精確的查找值，相反，想要VLOOKUP來為提供的查找值獲得最佳匹配。
圖6
在單元格D5中的公式使用近似匹配獲取正確的佣金率：
=VLOOKUP(C5,$G$5:$H$10,2,TRUE)
6.注意：VLOOKUP默認情形下使用近似匹配
第4個參數，稱之為「range_ lookup」控制著VLOOKUP的完全和近似匹配。
對於完全匹配，使用FALSE或者0。對於近似匹配，設置range_lookup為TRUE或1：
=VLOOKUP(value,table,column,TRUE) //近似匹配
=VLOOKUP(value,table,column,FALSE) //完全匹配
然而，第4個參數range_lookup是可選的，默認值是TRUE，這意味著VLOOKUP默認情形下進行近似匹配。當進行近似匹配時，VLOOKUP假定表已排序並執行二分查找。在二分查找時，如果VLOOKUP找到完全匹配的值，則從該行返回一個值。但是，如果VLOOKUP遇到大於查找值的值，將從前一行返回一個值。
這種默認設置非常危險，因為許多人無意中將VLOOKUP保留在默認模式下，當表未排序時可能導致錯誤的結果。
為了避免這個問題，確保在想要完全匹配時使用FALSE或0作為第4個參數。
7.你可以強迫VLOOKUP執行完全匹配
要強迫VLOOKUP找到完全匹配，確保設置第4個參數（range_lookup）為FALSE或0。下面兩個公式等價：
=VLOOKUP(value,table,column,FALSE)
=VLOOKUP(value,table,column,0)
在完全匹配模式下，當VLOOKUP不能找到值時，返回#N/A。清楚地表明沒有在表中找到值。
8.你可以告訴VLOOKUP執行近似匹配
要使用VLOOKUP的近似匹配模式，忽略第4個參數（range_lookup）或者設置其為TRUE或1。下面3個公式等價：
=VLOOKUP(value,table,column)
=VLOOKUP(value,table,column,1)
=VLOOKUP(value,table,column,TRUE)
推薦總是顯式設置range_lookup參數，即使VLOOKUP不需要。這樣，你總能明顯地看到你期望的匹配模式。
9.對於近似匹配，數據必須排序
如果使用近似匹配模式，那麼數據必須根據查找值按升序排序。否則，可能得到的是錯誤結果。同時注意，有時文本數據可能看起來已排序，雖然實際上並沒有排序。
10.VLOOKUP能夠合併不同表中的數據
VLOOKUP的常見使用示例是連接來自兩個或多個表中的數據。例如，可能在一個表中有訂單數據，在另一個表中是客戶數據，想要將一些客戶數據合併到訂單表中進行分析：
圖7
由於客戶Id在兩個表中都存在，可以在VLOOKUP中使用這個值來提取數據，只需配置VLOOKUP使用表1中的Id值，表2中相應的列索引和數據。在上例中，使用兩個VLOOKUP公式，一個提取客戶名稱，另一個提取客戶狀態。
圖8
11.VLOOKUP能夠識別或分類數據
如果需要將任意類別應用於數據記錄，那麼可以使用VLOOKUP輕鬆完成此操作，方法是使用擔當「鍵」的表來賦值分類。
一個經典的例子是基於分數來賦值成績：
圖9
本例中，VLOOKUP設置為近似匹配，因此表按升序進行排列是重要的。
然而，也可以使用VLOOKUP來賦值任意類別。在下面的例子中，使用VLOOKUP來為每個部門計算一個組，使用了定義分組的小表（稱為「key」）。
圖10
12.絕對引用使VLOOKUP更具可移植性
在打算從表中獲取多於1列的信息的情形下，或者需要複製和粘貼VLOOKUP時，可以通過對查找值和表數組使用絕對引用來節省時間。這可讓你複製公式，然後僅改變列索引編號以使用相同的查找來從不同列中獲取值。
下面的示例中，因為查找值和表數組是絕對引用，所以可以跨列複製公式，然後按需要回來修改列索引。
圖11
13.命名區域使VLOOKUP更容易閱讀（並且更可移植）
絕對單元格區域相當難看，因此可以通過使用命名區域代替絕對引用使VLOOKUP公式更簡潔易讀。
在圖11中，命名輸入單元格為「id」，命名表中的數據為「datas」，可以編寫公式：
圖12
不僅公式易讀，而且更具可移植性，因為命名區域自動為絕對引用。
14.插入列可能中斷現有的VLOOKUP公式
如果工作表中已經存在VLOOKUP公式，那麼在表中插入列時可能中斷公式。這是因為當插入或刪除列時，硬編碼的列索引值不會自動更改。
本示例中，當在Year和Rank之間插入新列後，查找Rank和Sales被中斷，而Year工作正常，因為其所在列在插入列的左側，沒有受到影響：
圖13
為了避免這種問題，可以使用下文描述的技巧計算列索引號。
15.可以使用ROW或COLUMN計算列索引號
如果不想在複製公式後還要對公式進行編輯，那麼可以使用ROW或COLUMN來生成動態的列索引號。如果從連續列中獲取數據，這個技巧可讓你設置一個VLOOKUP公式，然後將其複製而無需進行任何修改。
本例中，使用COLUMN函數生成動態的列索引號。在單元格C3中，COLUMN函數返回當前列的列號3，將其減去1得到表中列的索引號，然後向右複製該公式：
圖14
所有的公式都是相同的，不需要任何編輯。使用的公式如下：
=VLOOKUP(ids,datax,COLUMN()-1,0)
16.VLOOKUP+MATCH用於完全動態的列索引
將上面介紹的技巧更進一步，可以使用MATCH來查找表中列的位置，返回完全動態的列索引號。
有時這被稱作雙向查找，因為正沿著行和列的方向查找。
一個示例是查找銷售人員在指定月份的銷售量，或者查找從指定的供應商特定產品的價格。例如，假設有銷售人員每月的銷售量：
圖15
VLOOKUP可以容易地查找銷售人員，但是沒有辦法自動處理月名。技巧是使用MATCH函數代替靜態列索引號。
圖16
注意，我們給出的匹配區域包括表中所有列，以便同步VLOOKUP中使用的列號。
=VLOOKUP(H2,saledata,MATCH(H3,months,0),0)
17.VLOOKUP允許使用通配符進行部分匹配
任何時候在完全匹配模式下使用VLOOKUP時，都可以在查找值中使用通配符。這看起來違反直覺，但是通配符讓你根據部分匹配進行精確匹配。
Excel提供了2個通配符：星號（*）匹配1個或多個字符，問號（？）匹配1個字符。
例如，可以直接在單元格中輸入星號，然後引用它作為VLOOKUP中的查找值。在圖17中，被命名為「val」的單元格H3中輸入「Mon*」，將導致VLOOKUP匹配名字「Monet」。
圖17
本例中的公式為：
=VLOOKUP(val,dataname,1,0)
如果你願意，可以調整VLOOKUP公式使用內置的通配符，如下圖18所示，簡單地連接單元格H3中的值和通配符。
本例中，在VLOOKUP函數里將查找值和星號連接：
=VLOOKUP(val &”*”,dataname,1,0)
注意，小心使用通配符和VLOOKUP。雖然提供了一個容易的辦法創建「偷懶的匹配」，但是也容易找到錯誤的匹配。
18.可以捕獲#N/A錯誤並顯示友好的消息
在完全匹配模式，當沒有找到匹配時VLOOKUP將顯示#N/A錯誤。一方面，這是有用的，因為明確告訴你在查找表中沒有匹配。然而，#N/A看起來不是很友好，因此有幾種方法可以捕獲這個錯誤並顯示為其他內容。
一旦開始使用VLOOKUP，肯定會遇到#N/A錯誤。當VLOOKUP不能找到匹配項時，發生這種錯誤。
這是有用的錯誤，因為VLOOKUP清楚地告訴你不能夠找到查找值。本例中，「Latte」在表中不存在，因此VLOOKUP拋出#N/A錯誤。
圖19
本例中的公式是完全標準的完全匹配：
=VLOOKUP(E4,datab,2,0)
然而，#N/A看起來不是非常友好，因此你可能想要捕獲這個錯誤並顯示更友好的消息。
最容易的方法是將VLOOKUP放在IFERROR函數里面。IFERROR允許捕獲任意錯誤並返回你選擇的結果。要捕獲錯誤並顯示「沒有找到」消息來代替該錯誤，可以將原來的公式簡單地放置在IFERROR裡面並設置你想要的結果：
圖20
如果找到了查找值，那麼沒有錯誤發生並且VLOOKUP函數返回正常的結果。下面是公式：
=IFERROR(VLOOKUP(E4,datab,2,0),”沒有找到”)
19.數字作為文本可能導致匹配錯誤
有時，在VLOOKUP中使用的表可能包含以文本形式輸入的數字。如果只是將數字作為文本從表中檢索，則無關緊要。但是，如果表中的第1列包含以文本形式輸入的數字，而查找值不是文本，則會出現#N/A錯誤。
下面的例子中，planet表的ids是以文本形式輸入的數字，由於查找值是數字3，因而導致VLOOKUP返回錯誤：
圖21
要解決這個問題，需要確保查找值和表中的第1列都是相同的數據類型（都是數字或者都是文本）。
一種方法是將查找列中的值轉換為數字。然而，如果不容易控制源表格，也可以調整VLOOKUP公式來轉換查找值為文本，如下面所示通過在查找值後連接」」：
=VLOOKUP(idn &””,planets,2,0)
圖22
如果無法確定何時會有數字，何時有文本，那麼可以通過在IFERROR中放置VLOOKUP來處理這兩種情況：
=IFERROR(VLOOKUP(idn,planets,2,0),VLOOKUP(idn& “”,planets,2,0))
20.可以使用VLOOKUP來替換嵌套的IF語句
VLOOKUP最有趣的用法之一是替換嵌套的IF語句。如果你曾經構建過一系列嵌套的IF語句，知道它們工作正常，但它們需要很多括號，也必須注意嵌套的順序，以免引入邏輯錯誤。
例如，嵌套的IF語句的常見用法是根據分數來確定成績。下面的示例中，可以看到使用嵌套的IF語句構建的公式可以實現。
圖23
完整的IF嵌套公式如下：
=IF(C5<64,”F”,IF(C5<73,”D”,IF(C5<85,”C”,IF(C5<95,”B”,”A”))))
公式工作正常，但是要注意，邏輯關係和實際分數都直接編寫進公式。如果因任何原因修改了分數，需要仔細更新公式，然後將其複製到整個表中。
相比之下，VLOOKUP可以使用一個簡單的公式來賦值成績。所需要做的是確保為VLOOKUP設置好成績表，即按分數排序，且包含括號來處理所有分數。
圖24
此時，使用的公式為：
=VLOOKUP(C5,grade,2,TRUE)
這種方法的好處是，邏輯和分數都內置在成績指標表中。如果有任何改變，可以直接修改更新表，無需編輯公式，VLOOKUP公式將自動更新。
21.VLOOKUP可以僅處理單個條件
根據設計，VLOOKUP只能根據單個條件查找值，該條件作為查找值在表的第1列（查找列）中查找。
這意味著，諸如在「Accounting」中查找姓氏為「Smith」的員工，或者根據在單獨的列中的名字和姓氏來查找員工，都是不容易的。
然而，有辦法來克服這個局限。一種解決方法是創建輔助列，用來連接來自不同列的值來創建查找值，類似多條件。例如，想要查找員工的部門和組，但是名字和姓氏在不同的列中，怎樣實現同時查找呢？
圖25
首先，添加輔助列，將名字和姓氏連接在一起：
圖26
然後，讓VLOOKUP來使用包含了新列的表，將名字和姓氏連接作為查找值：
圖27
最後的VLOOKUP公式將輔助列作為查找列來查找名字和姓氏連在一起的值：
=VLOOKUP(C3&D3,name,4,0)
22.兩個VLOOKUPS比一個VLOOKUP更快
這可能看起來很瘋狂，但是當你有大量數據並需要執行完全匹配時，可以通過在公式中添加另一個VLOOKUP來加快VLOOKUP的速度！
背景：假設你有大量的訂單數據，例如超過了10000條記錄，並且正在使用VLOOKUP來基於訂單ID查找訂單總數。因此，使用的公式形式如下：
=VLOOKUP(order_id,order_data,5,FALSE)
公式最後的FALSE迫使VLOOKUP執行完全匹配。你需要完全匹配，因為有可能找不到訂單號。此時，完全匹配設置將導致VLOOKUP返回#N/A錯誤。
問題是完全匹配非常慢，因為Excel必須以線性方式遍歷所有值，直至找到匹配或者不匹配。
相反，近似匹配相當快，因為Excel能夠執行所謂的二分查找。
然而，二分查找的問題（VLOOKUP處於近似匹配模式）是當找不到值時，VLOOKUP可能返回錯誤的結果。更糟糕的是，結果可能看起來完全正常，因此很難發現錯誤。
解決方案是在近似匹配模式下使用VLOOKUP兩次。第1個實例簡單地檢查該值是否真的存在。如果存在，另一個VLOOKUP運行（同樣，在近似匹配模式）來獲取想要的數據。如果不是，可以返回你想要的任意值來指示沒有找到結果。
最後的公式形式如下：
=IF(VLOOKUP(order_id,order_data,1,TRUE)=order_id,VLOOKUP(order_id,order_data,5,FALSE),」Missing」)
注意，使用這個技巧時，數據必須已排序。這只是一種防止查找值丟失的方法，同時保持快速查找。
23.INDEX和MATCH組合能夠做VLOOKUP能夠做的任何事情，甚至更多
INDEX和MATCH組合與VLOOKUP孰優孰劣，在網上有許多爭論。
INDEX+MATCH可以完成VLOOKUP（和HLOOKUP）所能做的所有事情，並且更靈活，但也更複雜。因此，支持INDEX+MATCH的人會說，最好先學習INDEX和MATCH，因為最終會提供給你一個更好的工具集。
反對INDEX+MATCH的觀點是需要兩個函數，因此對用戶來說，學習和精通更複雜。
如果經常使用Excel，需要學習如何使用INDEX和MATCH，這是一個非常強大的組合。
但也應該學習VLOOKUP，因為經常會在很多工作表中發現VLOOKUP的使用。在直觀的情形下，VLOOKUP可以毫不費力地實現目的。
By: 23 things you should know about VLOOKUP