excel里的vlookup函數特別容易出錯,有沒有好的解決辦法?


你好,我把vlookup常遇到的錯誤和解決的方法,在這裡演示一下。希望能幫到大家!

1,VLOOKUP常見錯誤分析 - 絕少第四參數。

如下圖,根據微信號找出對應的內容。

C15=VLOOKUP(B15,C5:F11,3)

錯誤原因:缺少第四個參數。

解釋:當缺少第四個參數或為1時表示模糊查找,為0時表示精確查找

正確公式:C15=VLOOKUP(B15,C5:F11,3,0)

2,VLOOKUP常見錯誤分析 - 查找範圍中有重複值。

如下圖,根據微信號找出對應的內容。

錯誤原因:被查找區域(C6:C11)有重複值,有兩個officegif。

說明:VLOOKUP只返回從上到下第一次出現的值。

解決辦法:用唯一值進行查找,比如有編號。

3,VLOOKUP常見錯誤分析 - 相對引用查找範圍時下拉錯位。

如下圖,根據微信號找出對應的內容。

錯誤公式:C15=VLOOKUP(B15,C6:E11,3,0)

錯誤原因:相對引用範圍「C6:E11」下拉到C17變成"C8:E13"

正確公式:D15=VLOOKUP(B15,$C$6:$E$11,3,0)

4,VLOOKUP常見錯誤分析 - 從右向左查詢。

如下圖,根據微信號找出對應的編號。

錯誤公式:C15=VLOOKUP(B15,$B$6:$C$11,2,0)

錯誤原因:VLOOKUP不支持從右向左查詢

正確公式:D15=VLOOKUP(B15,IF({1,0},$C$6:$C$11,$B$6:$B$11),2,0)

5,VLOOKUP常見錯誤分析 - 查找區域與被查找區域格式不一致。

如下圖,根據編號查找對應的微信號。

錯誤原因:查找、被查找區域數字格式不一致(有些事文本格式,有些事數字格式)

解決方案:用「數據/分列」功能將兩邊的格式轉為一致。

6,VLOOKUP常見錯誤分析 - 被查找區域存在通配符。

下圖中根據編號查找出對應的微信號。其中編號中帶有~

錯誤原因:查找單元格中存在通配符 ~

解決方案:使用SUBSTITUTE函數將~替換為~~

即D15=VLOOKUP(SUBSTITUTE(B15,"~","~~"),$B$5:$C$11,2,0)

說明:Excel中~的含義是通配符,若要表示文本的~,則要書寫為~~

7,VLOOKUP常見錯誤分析 - 查找區域存在空格。

下圖中根據微信號查找對應的內容。

錯誤原因:單元格存在空格

解決方案:用替換功能(Ctrl+H)將空格替換掉

8,VLOOKUP常見錯誤分析 - 查找區域存在回車字元。

下圖中根據微信號查找對應的內容。

錯誤原因:單查找區域存在回車字元。

解決方案:用替換功能(Ctrl+H)將回車字元替換掉。

替換時在"查找內容"同時按下Ctrl+回車。

9,VLOOKUP常見錯誤分析 - 第三個參數在橫向拉動時不會改變

下圖中根據微信號查找對應的內容和類型。

公式:C15=VLOOKUP(B15,C5:F11,2,0)

把公式從C15複製到D15,查找結果不正確

錯誤原因:第三個參數在橫向拉動時不會改變

解決方法:嵌套MATCH函數, 根據列表題自動返回第三個參數

公式:D16=VLOOKUP($B16,$C$5:$F$11,MATCH(C14,$C$5:$F$5,0),0)


專業從事查找功能的函數:Vlookup,參數眾多,「脾氣」暴躁,規則複雜,一不留心就會出錯。

出錯誤並不可怕,怕的是不知道怎麼解決。

本文就教你破解VLOOKUP函數病症的良藥。

在總結Vlookup函數錯誤之前,我們先來回顧下Vlookup函數的語法:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

VLOOKUP(查找對象,查找範圍,返回列數,精確匹配或者近似匹配)

更詳細的情況,可以點擊這裡查看星爺寫過的兩篇經典教程:

入門|快速掌握VLOOKUP函數之精解精析

進階|熟練使用VLOOKUP函數之精解精析【深度長文】

(點擊文章標題即可查看)

小白雷哥了解了Vlookup函數的基本語法,可還是經常會遇到一些錯誤。於是滿懷期待了走進了星爺診室,希望能夠藥到病除。

— 01 —

參數使用錯誤

NO.1:參數1使用錯誤

通常對於批量查找,參數1會使用引用單元格,這就涉及到引用方式的問題,稍微不注意,就可能造成查找錯誤。

比如,在F2輸入公式=VLOOKUP($E$2,B:C,2,0),向下複製填充,結果三個單元格中返回的值都是89,顯然高書記和李書記的成績是錯誤的。

錯誤原因第一個參數採用了絕對引用,這樣在複製填充時,單元格引用不會變化,所以一直查找的是「陸亦可」對應的成績。

解決方法對於VLOOKUP的第一個參數,如果公式需要向下複製填充,則需要使用行的相對引用,使查找對象可以依次變更。所以公式改為=VLOOKUP($E2,B:C,2,0)或=VLOOKUP(E2,B:C,2,0)均可。

NO.2:參數2首行錯誤

VLOOKUP函數中,參數2的限制很多,是最容易出錯的地方。如圖查找三位同事的成績,結果均返回錯誤值。

錯誤原因VLOOKUP函數的第二個參數是查找區域,這個區域不是隨便選取的,它必須滿足的條件之一就是:參數1(查找對象)必須位於該區域的第1列。

本例選取的參數2為A:C,其中姓名列位於此區域的第二列,所以無法正確查找。

解決方法將公式修改為=VLOOKUP(E2,B:C,2,0),即函數的第二個參數設置為B:C。

NO.3:參數2區域範圍錯誤

如圖所示,參數2設置為B:C,可是查找還是出錯了。

錯誤原因參數2必須滿足的條件之二就是:這個區域一定要包含需要返回的值所在的列。我們需要返回成績,但是第2個參數B:C根本就沒有包括需要返回值所在的D列。

解決方法將公式修改為=VLOOKUP(F2,B:D,3,0),使第二參數包含需要返回的值。

NO.4:參數2區域中列的順序錯誤

如圖,使用姓名查找組別,結果返回錯誤值。

錯誤原因本例中VLOOKUP函數的第二參數為A:B,其實這裡有兩個錯誤。一是參數1並不在參數2選定區域的第一列;第二是返回值「組別」在查找值的左側,這是不允許的。

解決方法①最簡單的方法,將A、B列數據互換,然後再使用VLOOKUP查找;②使用index+match組合查找,詳細見這篇文章中關於INDEX和MATCH函數的講解:

從0到1:混職場,必須掌握的十二個Excel函數

NO.5:參數3使用錯誤

參數3:「返回的列數」指的是在參數2這個區域中的列數,它不一定等於在Excel表格中的列數,如圖錯誤將參數3的值設置為「4」,結果返回錯誤值。

錯誤原因成績位於參數2選定的區域「B:D」中的第三列,而不是第四列,因此參數3需要設置為3.

解決方法將公式修改為=VLOOKUP(F2,B:D,3,0)即可。

NO.6:參數4設置錯誤

如圖所示,最後一個參數設置為1,結果返回的數值與姓名對不上號。

錯誤原因VLOOKUP函數的參數4為0或省略時表示精確查找,非0值時表示模糊查找。這裡設置為1,所以進行的是模糊查找。模糊查找,會找到和它最接近,但比它小的那個數。因此返回的數值不正確。

修改方法將公式改為=VLOOKUP(F2,B:D,3,)或者=VLOOKUP(F2,B:D,3,0)

— 02 —

查找匹配的錯誤

除了參數設置錯誤之外,如果要查找的數據源不符合規範,也會出現錯誤。

NO.1:數據表中含有多餘的空格

如圖查找成員的成績,陸亦可的成績能夠正確查找,陳海的卻出錯。

錯誤原因源數據中,為了使姓名對齊,在陳海姓名中間添加了空格。這樣使用不帶空格的「陳海」去匹配,當然查找不到了。

解決方法使用TRIM或者手工刪除空格。如果希望兩個字的姓名與三個字的對齊,可以採用分散對齊的方式,如圖所示。

NO.2:查找對象與源數據格式不一致

小白雷哥需要通過員工工號查詢到電腦號碼。使用=VLOOKUP(G3,A2:D12,4,1) 查詢時,返回錯誤值#N/A 。

診斷分析星爺通過他的火眼金睛,立馬就發現了問題。這是因為查找值(11208)與查找範圍第一列(工號)數據格式不一致導致的。在vlookup函數查找過程中,文本型數字和數值型數字會被認為不同的字元。所以造成無法成功查找。

解決方案將源數據中工號一列更改為文本類型,然後再查找。

— 03 —

單元格引用導致的錯誤

函數中的單元格引用,「混合引用」的方式是最複雜的,也是最容易出錯的。不管是參數1,還是參數2都會有這樣的問題。

如圖使用COLUMN函數與VLOOKUP函數嵌套,一次返回多列查找值,在G2中輸入公式=VLOOKUP(F2,B2:D9,COLUMN(B1),0),然後向下拖動複製,並向右拖動複製,但是返回的值有一部分是錯誤的。

錯誤原因①由於第二個參數B2:D9是相對引用,所以向下複製公式後會自動更改為B3:D10,B4:D11……而F4中的陳海所在的行,不在B4:D11區域中,從而造成查找失敗。

②而向右複製時,參數1會變為G3,因此查找對象變為了性別,從而造成查找失敗。

解決方法把參數2由相對引用改為絕對引用;參數1改為混合引用。即公式為=VLOOKUP($F2,$B$2:$D$9,COLUMN(B1),0)

這樣既能確保向下複製時可以查找不同的姓名,又能確保向右複製時查找對象不會篡位。

這些VLOOKUP錯誤種類幾乎囊括了所有的查找情況,如果碰到了錯誤,而你剛好有解決方案,效率豈不大增!

·END·


謝謝邀請,VLOOKUP函數使用過程中如果出現報錯,一般查詢從條件列的單元格格式是不是統一。

此外,VLOOKUP函數雖然容易上手使用起來確實不方便,我們可以用INDEX函數:

為了大家很好的理解index函數,先跟大家介紹一個場景軍訓隊列點名,如下圖:

全班一共站了3排12列,教官說在隊列裡面不許講話不許動,要動打報告!但是總是有人會不由自主的動一下,如上圖穿紅衣服那位,估計手酸了一直再甩手。這時候教官就要請他出列,因為不知道名字所以教官的指令為:第2排第6列的,出列!

這個指令就相當於index函數,excel表格也是一個一個單元格組成的「隊列」,要想讓哪個數據出來就用index發號指令:

INDEX(數據範圍,第幾行,第幾列):

在具體匹配的過程中我們能清楚快速的知道要匹配的值在第幾列,但是不知道在第幾行,如下表:

這時候用INDEX函數可以確定範圍,以及GMV在第3列,但是相關類目在第幾行是沒法確定的,這個時候我們需要一個MATCH函數:

MATCH(數據,數據範圍,0),通過match函數可以判斷數據在數據範圍排在第幾位,如MATCH("男裝",A2:A8,0)返回的值是1:

在配上index函數就能匹配出相應的值,如上圖匹配男裝類目GMV:

=INDEX($A2$:$D8$,MATCH(F2,$A2$:$A8$,0),3)

關於excel不同的問題可以關注我,每天都會分享一些技巧。同時需要幫助小超也會及時幫到大家


vlookup函數是Excel的入門函數,也是Excel中最常用函數,沒有之一。

但,Vlookup函數在幾中場景下,使用起來略顯麻煩。如,自右向左查詢,橫向查詢等。

當然,Vlookup出錯率也會高點。

這裡,我就分享給大家另外一個替代Vlookup的方法,那就是Index+Match組合。

Vlookup

先來看看vlookup的用法

VLOOKUP(找什麼,在哪找,位於區域的第幾列,精確還是模糊找)

最牛查詢Index+Match組合

為何我要給大家推薦這組組合?

因為Index+Match組合靈活,簡單。你試試,相信你也會喜歡的。

INDEX(array, row_num, [column_num])

MATCH(lookup_value, lookup_array, [match_type])

別看這個公式這麼長,其實很好理解。

用match找到滿足條件的位置,然後用index定位,就這麼簡單。

這個組合,不管是自左向右,還是自右向左,動態區域查詢等場景下,用起來就是順手。

動手試試,相信你也會喜歡上Index+Match組合的。碼字不易,點個讚唄!!!


VLOOKUP函數參數搞不懂,總出錯,希望以下傻瓜級的講解,讓你不再糾結!

首先,考你一個傻瓜問題:叮叮幾歲?!!,你會說我怎麼知道,又有沒有參考數據。參考數據表看下圖

現在再問你叮叮幾歲?11歲! 你需要三步分析,首先知道「叮叮」;然後在數據表中找「叮叮」;找到「叮叮」後,看第二列的年齡,你知道叮叮是11歲!

VLOOKUP函數也是這個思路回答問題的,問VLOOKUP函數 叮叮幾歲?

VLOOUP(叮叮,數據表,2) ,第一個參數就是要找的值「叮叮」,第二個參數就是有」叮叮「年齡的數據表,第三個參數」2「,就是找到」叮叮「後發現年齡在第2列。最後返回值為」11。 將VLOOUP(叮叮,數據表,2)翻譯成Excel函數格式「=VLOOKUP(A2,A2:B4,2)」寫入B7,下圖黃色單元格,返回值「11」。

再正式一點的講解:

叮叮,叫做查找目標

有叮叮及年齡的表格,叫做查找範圍

年齡所在的列,叫做返回值的列數

以上這些就是VLOOKUP參數,根據參數格式,VLOOKUP(查找目標,查找範圍,返回值的列數),我們可以寫出函數「=VLOOKUP(A2,A2:B4,2)」

再官方一點講解:

該函數的語法規則如下:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

有細心的夥伴會發現,官方講解中出現了第4個參數,而之前只講了3個參數。第4個代表精確查找或模糊查找,聽到這裡是不是又有點暈,沒關係!這個參數不實用,暫時不需要理解太深,這裡告訴大家第4個函數的使用準則,只要遇到VLOOKUP函數必須寫第4個參數,填0就可以。就這樣記吧,保證你不犯錯!

好了,講到這裡如果你還不懂,那我真的糾結應不應該做老師了!

再考你一個傻瓜問題:喵喵幾歲?希望你能運用VLOOKUP函數給我一個官方的寫法,4個參數哦!

答案:=VLOOKUP(A4,A2:B4,2,0),不要忘記第4個參數!

是不是太簡單了,最後一個傻瓜問題,叮叮幾歲、咚咚幾歲、喵喵幾歲,我們只要求出叮叮幾歲,利用填充,咚咚幾歲、喵喵幾歲就全部顯示出來了。

看明白了吧,但是為什麼這次第二個參數多了幾個「$」符號呢,記住:在使用填充單元格功能之前一定要把第二個參數加上「$」符號,保證準確無誤!

VLOOKUP進階高級問題:

VLOOKUP的反向查找技巧VLOOKUP的模糊查找應用VLOOKUP的區間查找應用VLOOKUP的多項查找技能

以上課程會陸續為大家講解,敬請關注!

沒有Excel2016最新版的夥伴,可以私聊回復「2016」獲取最新版


【本文示例文件獲取方法見文末】

VLOOKUP函數非常容易出錯,錯誤原因可能讓人百思不得其解,技巧君總結了VLOOKUP函數的常見7大錯誤以及排除方法,詳情請看視頻詳細了解吧!

{!-- PGC_VIDEO:{"thumb_fingerprint": 10681264466964847502, "vid": "0ec9f6ad5b8b4c7e8c728e960cdbf6b2", "vu": "0ec9f6ad5b8b4c7e8c728e960cdbf6b2", "duration": 636, "thumb_height": 360, "thumb_neardup_id": -2778885804409135827, "neardup_id": 9852020427601124856, "video_size": {"high": {"duration": 636.16, "h": 480, "subjective_score": 0, "w": 854, "file_size": 9756668}, "ultra": {"duration": 636.16, "h": 720, "subjective_score": 0, "w": 1280, "file_size": 13950730}, "normal": {"duration": 636.16, "h": 360, "subjective_score": 0, "w": 640, "file_size": 8337567}}, "vname": "VLOOKUPu51fdu6570u5e38u89c1u9519u8bef.mp4", "hash_id": 9852020427601124856, "status": 0, "media_id": 1589658441132035, "thumb_width": 640, "external_covers": [{"mimetype": "webp", "source": "dynpost", "thumb_height": 360, "thumb_url": "76bb000854d0d9c1507d", "thumb_width": 640}], "item_id": 6539515626807886344, "user_id": 86183836665, "thumb_url": "pgc-image/1522599565721744de96b9a", "md5": "c5cf3a0adabe55b7bd8d273102a34cdc", "vposter": "http://p0.pstatp.com/origin/pgc-image/1522599565721744de96b9a", "sp": "toutiao", "group_id": 6539515626807886344} --}

示例文件獲取方法:點擊右上角紅色按鈕關注EXCEL精選技巧,然後點擊技巧君頭像,發送私信【VLOOKUP錯誤排除】即可獲取關注EXCEL精選技巧,每天學習3分鐘,堅持一個月,你將大不同!


VLOOKUP函數是日常運用中最常用函數之一,之所以容易出錯,一般是大量數據,多列查找,沒有鎖定數據引用範圍;或者是查找數據的與輸入值格式不一致造成。

VLOOKUP基本格式:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

基本格式是從左到右查找,即在數據源中,查找結果必須在輸入值的右邊。

上圖是悟空問答標籤,根據標籤查找熵。見圖,熵值在標籤右側,所以只要簡單根據vlookup基本套路查找即可。

但是,如果我們按vlookup基本格式用標籤來查找排序,就會出錯了,這時可以引入一個排序函數{1,0},就可以達到效果。

vlookup函數如何查找右邊內容?

上圖,排序位於標籤左側,這時用排序函數先將二者順序調換,再查找,即可查找出準確的結果:

=VLOOKUP(D3,IF({1,0},B:B,A:A),2,0)

學好vlookup等幾個基本函數,了解其簡單套路,那麼就能解決日常大部分需求了。


大家平時在工作中經常用VLOOKUP查詢數據,但是這個函數也有不聽話的時候,小脾氣上來了,就會返回錯誤值。咱們就來了解一下VLOOKUP函數返回錯誤值的原因和解決方法。

一. VLOOKUP函數基本語法:

=VLOOKUP (lookup_value, table_array,col_index_num, [range_lookup])

這個太複雜了哦,看鵝理解的VLOOKUP函數中文語法吧:

=VLOOKUP(查找值,數據源,第幾列,模糊查找1/精確查找0)

變成人話,是不是更容易理解啊,哈哈。

二. 實例說話:

接下來,先看一個VLOOKUP函數的應用實例吧。

通過菜名查詢庫存數量。

C11單元格公式如下:

=VLOOKUP(B11,$B$3:$D$6,2,0)

這樣的用法大家都很熟悉了,可是VLOOKUP函數也有不聽話的時候,有時會返回錯誤值#N/A。這是怎麼回事呢?咱們來看看出現的原因和解決方法:

第一種:數據源沒有絕對引用。

公式拖動時,查找區域發送變化,導致找不到查詢值。所以鎖定查詢區域尤其重要,否則就會查詢不到而返回#N/A。

第二種:指定第三參數錯誤,也會返回錯誤值。

例如以下公式

=VLOOKUP(E11,$C$3:$D$6,3,0)

這裡的查詢區域只有C、D兩列,而指定返回的列是3,明顯超出查詢區域範圍,Excel就暈了,因此就會顯示#REF!

第三種:查找值與數據源中的數據不一致。

1.有空格。

可以雙擊單元格,查看最後一個字元後面是否有空格或者是在編輯欄公式欄里看。

解決方案:

1) 複製一個數據源,粘貼在公式的查找條件里。

2) 直接通過函數TRIM去掉空格

C11=VLOOKUP(TRIM(B11),$B$3:$D$6,2,0)。

3) 如果ERP系統導出來的數據包含不可見字元,通過CLEAN函數處理一下,一般即可正常查詢。

如:

=VLOOKUP(CLEAN(B11),$B$3:$D$6,2,0)

2.查詢值和查詢區域中的數據類型不統一,既有文本又有數值。

可以通過TYPE函數判斷。

TYPY返回信息如下:

數值=1;文字=2;邏輯值=4;錯誤值=16;數組=64

這種情況下,只要將文本格式的數字轉換成真正數字就可以正常查詢了。

轉換成數字的方法有很多種:

E11*1

E11/1

E11+0

E11-0

--E11

VALUE(E11)

使用時任選其中一種即可。

第四種,查詢區域中沒有查詢值,所以顯示#N/A。

通過=IFERROR(公式,」」)可以將錯誤值屏蔽掉。

以上是VLOOKUP函數返回錯誤值的幾種常見原因和解決方法,怎麼樣,你還能補充一下嗎?


大家好,今天和大家分享"Vlookup函數V不出來",來自一朋友的提問,為什麼我根據工號查找部門報錯,D列是代碼,E列是代表的部門,工號的前3位決定是那個部門,如下圖

一、原因分析

這位朋友沒有理解是什麼是數值型數字,什麼是文本型數字?在Excel里分2種,一種是文本型數字,一種是數值型數字,文本型數字沒有大小之分,數值型數字有大小之分,另外一點,文本函數如Left,Right,Mid這一類提取出來的是文本型的,而你數據源D列的代碼又是數值型的數字導致Vlookup函數報錯

二、解決方法

我們可以通過--,這種減負運算把文本型數字轉數值型數字,當然我們也可以通過分列把D列數值型轉為文本型,目的就是A列和D列的格式一致。

1、公式截圖

2、公式

=VLOOKUP(--LEFT(A2,3),D:E,2,0)

3、公式解釋

從左邊提取3位作vookup函數的第1參數查找值,由於第2參數數據源的D列是數值型數,所以要在left前加負負--,把文本型轉數值型

vlookup4個參數,第1參數查找值;第2參數數據源;第3參數返回數據源那一列;第4參數用0精確查找


一、函數參數使用錯誤。

第1種:第2個參數區域設置錯誤之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時可以省略,但必須保留「,」號。

蘭色說:今天所介紹的1~3錯誤是最簡單的查找錯誤,可能有些同學已能輕鬆處理,明天咱們繼續介紹VLOOKUP函數的其他查找錯誤,可能你處理起來就沒這麼輕鬆了。

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

第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)

蘭色說:今天又介紹了三種常遇到的vlookup查找錯誤。明天我們將繼續介紹vlookup函數的其他查找錯誤。如果你也遇到了vlookup的錯誤,可以把公式通過微信平台發給我。

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

第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種:不可見字元的影響

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

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

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


推薦閱讀:

吳寒笛:找工作千萬別用這5招,因為威力太大了!
職場月光族消費太高,用敬業簽提醒便簽做理財規劃
90後,你都在經歷著什麼
馬云:偉大的領導者是如何激勵行動的?
如何一鍵生成excel圖表

TAG:Excel | 軟體 | 職場 |