錦子老師

VLOOKUP函數是一個非常好用的查找函數，但由於種種原因，在實際使用時會遇到種種讓人搞不明白的錯誤。於是本文就把常遇到的VLOOKUP錯誤問題來一次大整理，希望能對同學們有用。

n  函數參數使用錯誤

第1種：第2個參數範圍設置錯誤之一。

如下圖所示，根據姓名查找年齡時產生錯誤。

錯誤原因:VLOOKUP函數第二個參數是查找範圍，該範圍的第1欄有一個必備條件，就是查找的資料(A12)，必須對應於範圍的第1欄。本例中是根據姓名查找的，那麼，第二個參數姓名必須是在範圍的第1欄位置，而上述公式中姓名列是在表格A1：E6範圍的第2欄。所以公式應改為：

=VLOOKUP(A12,B2:E8,3,0)

第2種：第2個參數範圍設置錯誤之二。

如下圖所示，根據姓名查找職務時產生查找錯誤。

錯誤原因：本例是根據姓名查找職務，可大家注意一下，第2個參數B2:D8根本就沒有包括E欄的職務，當然會產生錯誤了。所以公式應改為：

=VLOOKUP(A12,B2:E8,4,0)

第3種：第4個參數少了或設置錯誤。

如下圖所示，根據工號查找姓名。

錯誤原因：VLOOKUP第四個參數為0時表示精確查找，為1或省略時表示模糊查找。如果忘了設置第4個參數則會被Excel誤以為是故意省略，按模糊查找進行。當範圍也不符合模糊查找規則時，公式就會傳回錯誤值。所以公式應改為。

=VLOOKUP(A12,A2:D8,2,0) 或 =VLOOKUP(A12,A2:D8,2,)

注意：當參數為0時可以省略，但必須保留「,」號。

二、數值格式不同，造成查找錯誤

第4種：查找為數值，被查找範圍為文字型數值。

如下圖所示，根據工號查找姓名，查找出現錯誤。

錯誤原因：在VLOOKUP函數查找過程中，文字型數值和數值型數值會被認為不同的字元。所以造成無法成功查找。

解決方案：把查找的數值在公式中轉換成文字型，然後再進行查找。即：

=VLOOKUP(A12&"",A2:E8,2,0)

第5種：查找格式為文本型數位，被查找區域為數值型數位。

例5：如下圖所示根據工號查找姓名，查找出現錯誤

錯誤原因：同4

解決方法：把文字型數值轉換成數值型。即：

=VLOOKUP(A12*1,A2:E8,2,0)

三、參照方式使公式複製後產生錯誤

第6種：沒有正確的使用參照方式，造成在複製公式後範圍發生變動引起錯誤。

如下圖所示，當B12的公式複製到B13和B14後，B13公式傳回錯誤值。

錯誤原因：由於第二個參數A2:E8是相對參照，所以向下複製公式後會自動更改為A3:E9,而A13中的工號A01所在的列，不在A3:E9範圍中，從而造成查找失敗。

解決方案：把第二個參數的參照方式由相對參照改為絕對參照即可。

B12公式改為：=VLOOKUP(A12,$A$2:$E$8,2,0)

四、多餘的空格或不可見字元

第7種：資料表中含有多餘的空格。

如下圖所示，由於A欄工號含有多餘的空格，造成查找錯誤。

錯誤原因：多一個空格，用不帶空格的字元查找當然會出錯了。

解決方案:

1、手工替換掉空格。建議用這個方法;

2、在公式中用TRIM函數替換空格而必須要用資料公式形式輸入。

即：輸入公式：「=VLOOKUP(A12,TRIM(A1:E8),2,0)」後，按Ctrl + Shift + Enter鍵，輸入後陣列形式為 {=VLOOKUP(A12,TRIM(A1:E8),2,0)}

第8種：類空格但非空格的字元。

在表格內存在大量的「空格」，但又用空格無法替換掉時，這些就是類空格的不可見字元，這時可以「以其人之道還之其人之身」，直接在儲存格中複製不可見字元粘貼到替換視窗，替換掉即可。

第9種：不可見字元的影響

例：如下圖所示的A欄中，A欄看不見不存在空格和類空格字元，但查找結果還是出錯。

出錯原因：這是從網頁或資料庫中導入資料時帶來的不可見字元，造成了查找的錯誤。

解決方案：在A欄後插入幾欄空欄，然後對A欄進行分欄操作(資料 >資料工具 > 資料剖析)，即可把不可見字元分離出去。

第10種：反向查找VLOOKUP不支援產生的錯誤。

如下圖所示，在這個表格中根據姓名查找工號，結果傳回了錯誤。

錯誤原因：VLOOKUP不支持反向查找。

解決方法：

用IF函數重組區域，讓兩列顛倒位置。

=VLOOKUP(A12,IF({0,1},A2:A8,B2:B8),2,0)

用INDEX+MATCH組合實現

=INDEX(A2:A8,MATCH(A128,B2:B8,0))

第11種：萬用字元引起的查找錯誤

如下圖所示，根據範圍查找提成傳回錯誤值。

錯誤原因：「~」用於查找萬用字元，如果在VLOOKUP公式中出現，會被認為特定用途，非真正的「~」。如在表格中查找3*6 ，356，376也被查找到。

如果精確查找3*6，需要使用~，如下圖所示。

解決方法：用~~就可以表示查找~了。所以公式可以修改為

=VLOOKUP(SUBSTITUTE(A8,"~","~~"),A2:B4,2,0)

第12種：VLOOKUP函數第1個參數不直接支援陣列形式產生的錯誤

如下圖所示，同時查找A和C產品的和，然後用SUM求總和。

錯誤原因：VLOOKUP第一個參數不能直接用於陣列。

解決方法：利用N/T+IF結構轉化一下陣列，如果不瞭解N/T+IF結構用法，可以參考本落格其他VLOOKUP範例。

公式修改為：

=SUM(VLOOKUP(T(IF({1},A9:B9)),A2:B6,2,))

部落格相關範例