移動版 - 最新教程 - 熱門教程 - 推薦教程 - 熱門資源 分享高質量的原創Office教程、Word教程、Excel教程、PPT教程。

vlookup函數的12種常見錯誤、原因及解決方法

發布時間:2015-02-13 來源:excel精英培訓網

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

一、函數參數使用錯誤

第1種:第2個參數區域設置錯誤之一。

例:如下圖所示,根據姓名查找齡時產生錯誤。

vlookup函數常見錯誤

錯誤原因: vlookup函數第二個參數是查找區域,該區域的第1列有一個必備條件,就是查找的對象(A9),必須對應于區域的第1列。本例中是根據姓名查找的,那么,第二個參數姓名必須是在區域的第1列位置,而上述公式中姓名列是在區域A1:E6的第2列。所以公式應改為:

=VLOOKUP(A9,B1:E6,3,0)

第2種:第2個參數區域設置錯誤之二。

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

vlookup函數常見錯誤

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

=VLOOKUP(A9,B1:E6,4,0)

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

例3,如下圖所示根據工號查找姓名

vlookup函數常見錯誤

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

=VLOOKUP(A9,A1:D6,2,0)

或 =VLOOKUP(A9,A1:D6,2,) 注:當參數為0時可以省略,但必須保留“,”號。

二、數字格式不同,造成查找錯誤

第4種:查找為數字,被查找區域為文本型數字。

例4:如下圖所示根據工號查找姓名,查找出現錯誤。

vlookup函數常見錯誤

錯誤原因:在vlookup函數查找過程中,文本型數字和數值型數字會被認為不同的字符。所以造成無法成功查找。

解決方案:把查找的數字在公式中轉換成文本型,然后再查找。即:

=VLOOKUP(A9&"",A1:D6,2,0)

第5種:查找格式為文本型數字,被查找區域為數值型數字。

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

vlookup函數常見錯誤

錯誤原因:同4

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

=VLOOKUP(A9*1,A1:D6,2,0)

三、引用方式使公式復制后產生錯誤

第6種:沒有正確的使用引用方式,造成在復制公式后區域發生變動引起錯誤。

例6,如下圖所示,當C9的公式復制到C10和C11后,C10公式返回錯誤值。

vlookup函數常見錯誤

錯誤原因:由于第二個參數A2:D6是相對引用,所以向下復制公式后會自動更改為A3:D7,而A10中的工號A01所在的行,不在A3:D7區域中,從而造成查找失敗。

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

B9公式改為:=VLOOKUP(A9,$A$2:$D$6,2,0)

四、多余的空格或不可見字符

第7種:數據表中含有多余的空格。

例7 如下圖所示,由于A列工號含有多余的空格,造成查找錯誤。

vlookup函數常見錯誤

錯誤原因:多一個空格,用不帶空格的字符查找當然會出錯了。

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

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

即:=VLOOKUP(A9,TRIM(A1:D6),2,0) 按ctrl+shift+enter輸入后數組形式為 {=VLOOKUP(A9,TRIM(A1:D6),2,0)}

第8種:類空格但非空格的字符。

在表格存在大量的“空格”,但又用空格無法替換掉時,這些就是類空格的不可見字符,這時可以“以其人之道還之其人之身”,直接在單元格中復制不可見字符粘貼到替換窗口,替換掉即可。

vlookup函數常見錯誤

第9種:不可見字符的影響

例:如下圖所示的A列中,A列看不去不存在空格和類空格字符,但查找結果還是出錯。

vlookup函數常見錯誤

出錯原因:這是從網頁或數據庫中導入數據時帶來的不可見字符,造成了查找的錯誤。

解決方案:在A列后插入幾列空列,然后對A列進行分列操作(數據 - 分列),即可把不可見字符分離出去。

vlookup函數常見錯誤

第10種:反向查找vlookup不支持產生的錯誤。

例10 如下圖所示的表中,根據姓名查找工號,結果返回了錯誤。

vlookup函數常見錯誤

錯誤原因:vlookup不支持反向查找。

解決方法:1、用if函數重組區域,讓兩列顛倒位置。

=VLOOKUP(D8,IF({0,1},D2:D4,E2:E4),2,0)

2、用index+match組合實現。

=INDEX(D2:D4,MATCH(D8,E2:E4,0))

第11種:通配符引起的查找錯誤

例11 如下圖所示,根據區間查找提成返回錯誤值。

vlookup函數常見錯誤

錯誤原因:~用于查找通配符,如果在vlookup公式中出現,會被認為特定用途,非真正的~。如在表格中查找3*6 ,356,376也被查找到。

vlookup函數常見錯誤

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

vlookup函數常見錯誤

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

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

第12種:vlookup函數第1個參數不直接支持數組形式產生的錯誤

例12 如下圖所示,同時查找A和C產品的和,然后用SUM求和。

vlookup函數常見錯誤

錯誤原因:VLOOKUP第一個參數不能直接用于數組。

解決方法:利用N/T+IF結構轉化一下數組,如果不了解N/T+IF結構用法。

公式修改為:

=SUM(VLOOKUP(T(IF({1},A8:B8)),A2:B5,2,))

更多相關閱讀

VLOOKUP函數的模糊查找

vlookup函數代替if函數實現任意多條件判斷的方法

運用Vlookup函數,查找某個時間段內數據的方法

Excel中一對多查找,不用vlookup函數用什么?

使用VLOOKUP函數轉換Excel表格中數據行列結構的方法

    相關教程
    專題教程推薦
    熱門資源下載
    欧美性交图