Excel-Vlookup函數查找易犯12個錯誤的解決方案

2021-03-02 Excel技巧精選

來源: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發送,獲取全部文章目錄

相關焦點

  • vlookup 函數有12種易犯錯誤 , 你都知道嗎?!
    於是蘭色就把常遇到的vlookup錯誤問題來一次大整理,希望能對同學們有用。一、函數參數使用錯誤。第1種:第2個參數區域設置錯誤之1。【例1】:如下圖所示,根據姓名查找齡時產生錯誤。錯誤原因:在vlookup函數查找過程中,文本型數字和數值型數字會被認為不同的字符。所以造成無法成功查找。解決方案:把查找的數字在公式中轉換成文本型,然後再查找。
  • 【excel】Vlookup 函數這些錯誤你遇到了嗎!
    點擊上方藍色字體, 免費訂閱"內部審計學習平臺"  平時在用vlookup函數是公式輸入無誤,缺不能出來正確的結果,是否在懷疑自己,難到我的Vlookup函數是假的嗎?如下圖,B8單元格輸入的是數字「111」,而A列存儲的是文本型數字,兩者不同,因此返回錯誤。1)利用【分列】功能將文本型數字轉化為數字;2)把VLOOKUP的第一參數加上&」」轉換為文本。遇到VLOOKUP函數有錯誤值的時候,別忘了用上面這幾種方法檢查一下哦。
  • excel查找函數-vlookup
    大家都會想到 VLOOKUP、LOOKUP等熟悉的查詢函數,但是對於剛入接觸或學習excel的新手不知道怎麼操作,今天分享關於查詢問題通過下面例子進行演示,如下:關鍵詞:vlookup、IFERROR、反方向查找函數使用格式=VLOOKUP
  • VLOOKUP函數教程大合集(入門+初級+進階+高級+最高級+12種常見錯誤)
    如果你有一定基礎,倒可以試試第2種方法。另外,如果工作表有幾十個或更多,就需要使用宏表函數get.workbook來獲取所有工作表的名稱,然後應用到公式中,具體的公式蘭色就不再寫了。VLOOKUP函數的使用方法(12種常見錯誤)為了方便同學們學習,蘭色把原來發的vlookup易錯系列打包整理到了一起。沒收藏的同學可以收藏了。
  • Excel查找引用:比vlookup函數還好用的是hlookup函數
    在之前發布了一個動態排班表的文章,根據日期和班組來查詢班次,小編原來使用了一個很長很長的if+vlookup函數組合查找到的!函數查找範圍是使用了定義名稱!沒想到只要一個hlookup函數就可以解決這個函數和vlookup函數的查找區別是橫向查找,查找值都必須在查找區域的首行/首列所以在F2中輸入公式=HLOOKUP(B2,四班三倒!
  • excel數據的模糊查找,vlookup函數與通配符的搭配使用
    今天我們要分享一個比較實用的excel數據的查找技巧,就是通過簡稱來查找全稱,這樣的查找方式叫做模糊查找,我們之前學習過了幾個查找函數,比較常見是vlookup函數和lookup函數以及choose函數等,我們知道這些查找函數各自有各自的優點,今天我們要講解的是vlookup函數與通配符的搭配使用
  • excel查找函數:如何用Vlookup進行多條件查找
    ,使用輔助列是個不錯的選擇。使用「&」符號將兩列或者多列值連接生成輔助列,然後再用「&」符號將兩個或者多個條件串起來當作查找值即可。這位同學的第一解決方案也正是這樣的:添加「姓名&日期」輔助列,然後用Vlookup查詢「G2&H2」。可是當同學發現輔助列原本應該得到姓名加日期的,卻變成了姓名加數字,於是就不敢繼續使用Vlookup查詢了。
  • excel查找數據就是如此簡單,vlookup函數的模糊查找
    我們在實際工作中,我們經常使用excel表格處理數據,處理數據的方法有很多種,查找數據應該是我們在日常工作中使用頻率比較高的操作,這次我們還是分享查找數據的小技巧,這次是對數據進行模糊查找,我們使用vlookup函數對數據進行模糊查找,下面我們就以實例結合視頻的形式將詳細的操作步驟展示出來
  • excel多條件查找方法:lookup、vlookup、indexmatch多條件查找
    多條件查找函數方法,分別是:lookup多條件查詢、vlookup多條件查找、indexmatch多條件查找。大家都知道lookup函數想要精準查找那麼這組數值必須要升序,但實際上這組數據運算結果0和1的順序是混亂的。所以就想到了用0來除以0和1的方式來區分。由於分母不能為0,所以0/0返回的是錯誤,0/1返回的結果為0。Lookup函數在查找的時候是忽略錯誤的,所以只有數據運算結果為1的公式滿足條件。
  • 函數vlookup與通配符功能的完美結合,助你解決習慣造成的錯誤
    excel在前兩篇文章中,第一篇文章向大家詳細介紹了函數vlookup的語法形式和基本運用方法,第二篇文章在函數vlookup的基本運用方法基礎上進一步介紹了函數vlookup的實用操作方法今天要講述的內容依然是在函數vlookup的基礎用法上進行的拓展。我們在運用excel工作表來解決日常中的問題時,往往所用的函數沒有任何問題,但是由於習慣原因,會導致我們在運用excel工作表中的函數時的過程並不順利,今天的主講內容就是我們在運用excel工作表時的習慣「錯誤」,以及解決錯誤的方法。
  • excel中vlookup函數的用法筆記
    本篇將介紹excel中vlookup函數的用法,有興趣的朋友可以了解一下!一、前言excel是我們工作中很常用的表格製作工具,它不僅僅只是用來製作表格,還能對數據進行處理(如:排序、運算等)。excel中還給我們提供了很多實用的函數,今天小編要介紹的就是其中一個,也是比較常用的一個函數,即vlookup函數。vlookup函數的功能其實就是用來找東西的,只要是找excel表格中的某個數據,大家都會想到使用vlookup函數。vlookup函數怎麼用呢?相信是很多人關心的問題,接下來就跟著小編一起來學習excel vlookup函數的用法吧!
  • 函數急診室:VLOOKUP查找錯誤詳解
    本例選取的參數2為A:C,其中姓名列位於此區域的第二列,所以無法正確查找。解決方法:將公式修改為=VLOOKUP(E2,B:C,2,0),即函數的第二個參數設置為B:C。NO.3:參數2區域範圍錯誤如圖所示,參數2設置為B:C,可是查找還是出錯了。
  • excel函數技巧:好像沒錯誤可Vlookup函數卻錯誤結果
    一些毛病是使用者功夫不到家,寫的公式存在錯誤造成的,譬如查找值不在查找區域的首列、查找區域錯誤、返回位置錯誤等等;一些毛病則是數據上有問題造成的。數據上的問題,有些很明顯,容易發現,有些很隱蔽,不容易發現。今天的教程就是分享3條影響Vlookup正常工作的數據問題。
  • 比VLOOKUP更強大的查找函數
    VLOOKUP是查找引用函數,其原理是查找什麼?在哪個範圍找?返回第幾列數據,是精確還是模糊查找。理解這個思路VLOOKUP已經爛熟於心了。但需要注意的是,VLOOKUP函數不能逆向查找,而有時候為了方便,需要逆向查找數據,所以才有了INDEX和MATCH函數INDEX函數◆定義:Index:英文意思為「索引」INDEX函數也是一個查找函數,功能與VLOOKUP有所不同◆語法:=INDEX
  • excel中vlookup函數的使用方法
    vlookup函數是excel表格中高級的用法,通過vlookup函數我們可以調用符合條件的數據,在大量調用時可以節省我們查找複製excel數據的時間,今天我就教下大家vlookup函數的使用方法吧。vlookup函數的使用方法如圖我準備了一張員工入職時間表,員工有非常多,如果我要在這裡面一一找出張三李四王五等人的入職時間的話,可以通過查找黏貼的方式,但是這樣的效率就很低了,特別是要找的人多的話,那使用vlookup函數是最簡單的方法。
  • Excel函數輕鬆解決根據會計科目匹配查找科目代碼的問題
    相信從事過財務類工作的同學,對於會計科目以及科目代碼應該不怎麼陌生,或許根據科目代碼匹配查找會計科目也是有了解的,我們直接藉助於vlookup函數就可以輕鬆匹配查找出來。可現在的問題是,我們需要逆向反向進行查找匹配,在之前的文章裡,分享過兩種方法啦!
  • excel中使用vlookup函數查找老出錯?試試index—match函數吧
    在excel中,運用查找函數是excel中進行數據統計分析最常用的操作之一,說起查找函數,很多人首先會想到vlookup函數,其實excel中的查找函數有很多種,比如lookup、index-match等,尤其是index—match函數的使用範圍比vlookup函數更加廣泛,在反向查找、橫向查找等方面能夠克服vloolup函數的不便,提供更加容易理解的查找思路
  • 函數急診室:最全的VLOOKUP查找錯誤詳解
    錯誤原因:VLOOKUP函數的第二個參數是查找區域,這個區域不是隨便選取的,它必須滿足的條件之一就是:參數1(查找對象)必須位於該區域的第1列。本例選取的參數2為A:C,其中姓名列位於此區域的第二列,所以無法正確查找。解決方法:將公式修改為=VLOOKUP(E2,B:C,2,0),即函數的第二個參數設置為B:C。NO.3:參數2區域範圍錯誤如圖所示,參數2設置為B:C,可是查找還是出錯了。
  • Excel教程:Vlookup的多條件查找失靈?!解決妙招在此!
    還有,能不能不用輔助列進行多條件查找呢?最近有學員在使用vlookup進行雙條件查找時遇到了困難: 通常使用Vlookup函數進行多條件查找的時候,使用輔助列是個不錯的選擇。使用「&」符號將兩列或者多列值連接生成輔助列,然後再用「&」符號將兩個或者多個條件串起來當作查找值即可。
  • excel操作技巧:Vlookup函數返回#N/A的解決方法
    excel函數很多,功能強大;但是如果不了解各項參數的具體含義,難免會出狀況。今天就分享一下Vlookup函數的一個小技巧。看公式,沒有什麼問題,公式都書寫正確,但是為何出現vlookup函數返回#n/a錯誤呢?細心的小夥伴,可能已經發現,是因為單元格格式不同導致的。