來源:EXCEL微學堂(ID:excelwxt)
作者:山城子
Vlookup函數是一個非常好用的查找函數,但由於種種原因,在實際使用時會遇到種種讓人搞不明白的錯誤。所以我就把常遇到的Vlookup錯誤問題來一次整理,並和小夥伴們一起分享。
一、函數參數使用錯誤
----
第1種:第2個參數區域設置錯誤之1。
例1:如下圖所示,根據姓名查找年齡時產生錯誤。
錯誤原因: Vlookup函數第二個參數是查找區域,該區域的第1列有一個必備條件,就是查找的對象(A9),必須對應於區域的第1列。本例中是根據姓名查找的,那麼,姓名列必須是在區域的第1列位置,而上述公式中姓名列是在區域A1:E6的第2列。所以公式應改為:
=VLOOKUP(A9,B1:E6,3,0)
----
第2種:第2個參數區域設置錯誤之2。
例2:如下圖所示根據姓名查找職務時產生查找錯誤。
錯誤原因:本例是根據姓名查找職務,可大家注意一下,第2個參數B1:D6根本就沒有包括E列的職務,當然會產生錯誤了。所以公式應改為:
=VLOOKUP(A9,B1:E6,4,0)
----
第3種:第4個參數少了或設置錯誤。
例3:如下圖所示根據工號查找姓名返回錯誤
錯誤原因:Vlookup第四個參數為0時表示精確查找,為1或省略時表示模糊查找。如果忘了設置第4個參數則會被公式誤以為是故意省略,按模糊查找進行。當區域也不符合模糊查找規則時,公式就會返回錯誤值。所以公式應改為:
=VLOOKUP(A9,A1:D6,2,0)
或 =VLOOKUP(A9,A1:D6,2,)
註:當參數為0時可以省略,但必須保留「,」號。
二、格式不同,造成查找錯誤。
----
第4種:被查找區域為數值型數字,查找格式為文本型數字。
例4:如下圖所示根據工號查找姓名,查找出現錯誤。
錯誤原因:在Vlookup函數查找過程中,文本型數字和數值型數字會被認為不同的字符。所以造成無法成功查找。
解決方案:把查找的數字在公式中轉換成文本型,然後再查找。即:
=VLOOKUP(A9&"",A1:D6,2,0)
-
第5種 查找格式為文本型數字,被查找區域為數值型數字。
例5:如下圖所示根據工號查找姓名,查找出現錯誤
錯誤原因:同4
解決方法:把文本型數字轉換成數值型。即:
=VLOOKUP(A9*1,A1:D6,2,0)
註:如果你手工把文本轉換成數值類型,就不必在公式中轉換格式了。
三、引用方式使公式複製後產生錯誤。
-
第6種:沒有正確的使用引用方式,造成在複製公式後區域發生變動引起錯誤。
例6:如下圖所示,當C9的公式複製到C10和C11後,C10公式返回錯誤值。
錯誤原因:由於第二個參數A2:D6是相對引用,所以向下複製公式後會自動更改為A3:D7,而A10中的工號A01所在的行,不在A3:D7區域中,從而造成查找失敗。
解決方案:把第二個參數的引用方式由相對引用改為絕對引用即可。
B9公式改為:=VLOOKUP(A9,$A$2:$D$6,2,0)
四、多餘的空格或不可見字符
-
第7種:數據表中含有多餘的空格。
例7:如下圖所示,由於A列工號含有多餘的空格,造成查找錯誤。
錯誤原因:多一個空格,用不帶空格的字符查找當然會出錯了。
解決方案:
1、手工替換掉空格。建議用這個方法
2、在公式中用Trim函數替換空格而必須要用數據公式形式輸入。
即:=VLOOKUP(A9,TRIM(A1:D6),2,0)
按Ctrl+Shift+Enter輸入後數組形式為
{=VLOOKUP(A9,TRIM(A1:D6),2,0)}
-
第8種:類空格但非空格的字符。
在表格存在大量的「空格」,但又用空格無法替換掉時,這些就是類空格的不可見字符,這時可以「以其人之道還之其人之身」,直接在單元格中複製不可見字符粘貼到替換窗口,替換掉即可。
第9種:不可見字符的影響
例9: 如下圖所示的A列中,A列看上去不存在空格和類空格字符,但查找結果還是出錯。
錯誤原因:這是從網頁或資料庫中導入數據時帶來的不可見字符,造成了查找的錯誤。
解決方案:
在A列後插入幾列空列,然後對A列進行分列操作(數據 - 分列),即可把不可見字符分離出去。
在日常Vlookup函數查找錯誤示例中,第7種是最常見的一種錯誤之一。小夥伴遇到Vlookup查找錯誤時,如果參數設置沒有問題,就看一下數據表中有沒有多餘的空格。
第10種:反向查找Vlookup不支持產生的錯誤。
例10:如下圖所示的表中,根據姓名查找工號,結果返回了錯誤。
錯誤原因: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公式中出現,會被認為特定用途,非真正的~。如在表格中查找3*6 ,356,376也被查找到。
如果精確查找3*6,需要使用~,如下圖所示。
解決方法:用~~就可以表示查找~了。所以公式可以修改為
=VLOOKUP(SUBSTITUTE(A8,"~","~~"),A2:B4,2,0)
第12種:Vlookup函數第1個參數不直接支持數組形式產生的錯誤。
例12:如下圖所示,同時查找A和C產品的和,然後用SUM求和。
錯誤原因:Vlookup第一個參數不能直接用於數組。
解決方法:利用N/T+IF結構轉化一下數組,如果不了解N/T+IF結構用法,
=SUM(VLOOKUP(T(IF({1},A8:B8)),A2:B5,2,))
Vlookup函數常見的12種錯誤我們介紹完了,掌握了這些技巧可能在以後的工作中不再會被Vlookup公式困繞了。
後臺回復「動畫教程」,獲取《全套Excel原創動畫教程》
後臺回復「簡歷模板」,獲取《100份簡歷模板》
後臺回復「面試題」, 獲取《100份名企筆試、面試題》
後臺回復「報表模板」,獲取《全套財務報表模板》
想要更多精品乾貨,請持續關注我們的微信公眾號 Excel技巧精選(ID:ExcelSkill)↓↓↓
●進入公眾號輸入750發送,即可閱讀本文
●進入公眾號輸入m發送,獲取全部文章目錄