Excel 系列（五）- 為什麼VLOOKUP找不到??

Views
有沒有遇到過明明要找尋的錨點的就在表格中, 但對應的數值傳回來郤是顯示#N/A??







    前言

    VLOOKUP 和 HLOOKUP 是 Excel 很好用的一個搜尋對照資料的功能。

     不過在用VLOOKUP 或是 HLOOKUP 做搜尋時，有幾個要注意的小地方，才能確保返回值是正確的。

     不然就會有如下狀況：明明有該筆資料郤vlookup不到

    返回值是#N/A


    舉例說明:上圖是我想要從右邊的表格裡用vlookup傳回相對應於"編號"的"分類", 結果全出現#N/A.


    原因一: format 不同
    有經驗的人一看就知道那是因為二邊"編號"的cell format 不相同。左邊的format是"數值", 右邊的format是"文字"，自然無法對上。

    這個解決方法很簡單，只要把二邊的format變相同就可以對上了。

    解決方法：
    方法1) 
    用Text to Column改變其中一個表格裡"編號"的fomat. 

     先選取要改format的那一列, 然後"Data"→"Text to Coumns"→選"delimited"按Next→再按Next→然後選Text→按"Finish"就好了.

    方法2) 
    直接在=vlookup裡把text轉成value 或是value 轉成text即可.

    把value 轉成text"

     把text轉成value:
    原因二: leading zero
    但是，如果其中一個有所謂的"leading zero" 怎麼辦？

    這時候, 光是用Text to Column改cell format也沒用, 因為改完還是少個0. 
    解決方法：
    這時請用下面的方法, 可以補足leading 0.
    另外, 既沒有leading zero的問題, 也沒有數值value或是文字text的問題, 還是找不到呢?

    如上圖, Column A 和Column H都已經設定成"文字text"了, 但怎麼用了vlookup, 有些找到相對應的"分類", 有些還是傳回了#N/A呢?


    原因三: 有前後綴空格

    另一個有可能的問題就是"空格". 不確定Column A 和Column H裡的做搜尋的cell有沒有多餘不必要的空格.

     解決方法: 
    用=trim()去除多餘的空格.


    這裡可以看到大部份的都找到相對應的數值了, 但有二個還是回傳#N/A. Line7是因為在Column I7也有空格, 所以只trim一邊是沒用的, 二邊都trim了, 就可以找到I7的對應值了.

    至於line12, 那就是前面提到的leading 0了. 用=VLOOKUP(TEXT(A12, "00000"), I:J, 2, 0)
    就可以找的到了.

    原因四: 空白字元
    如果都排除了上面所列的問題, 還是找不到呢? 這個就是因為有一個多出來看似是空格, 但不是空格而是一個空白字元卡在那裡, 這樣形式的空格, 用trim是無法去除的

    舉例如上圖, 明明二旁邊都有HV2901, 但一定vlookup不到, 因為一旦到G4 cell一看, 就會發現在游標前有一個空格.

    用了=trim(G4)仍然對應不到. 那是因為這個空格不是space, 而是一個空白的字元. 只有一個一個點進去刪除這個空白的字元, 就可以vlookup到了.


    問題是, 如果有上千上萬筆資料, 怎麼可能一個一個去刪除呢??

    解決方法:
    用=TRIM(SUBSTITUTE())來解決吧.

    由上圖公式中可以看到相對應金額都找到了. 用的公式是把CHAR(160)這個空白字元用CHAR(32)來取代, 然後藉由=trim()再把CHAR(32)給去掉.

    那什麼情況下會產這樣的空白字元的空格呢? 通常是因為這個excel是從PDF或是word轉檔的, 這種偶爾就會出現這種字元型的空格. ASCII 160是一個不能被trim掉的空白, 但ASCII 32則是一般空白, 可以被trim掉.  所以上面的=trim() 公式裡就是先把CHAR(160) 換成CHAR(32)再把CHAR(32)給trim掉.
    原因五: 超過255個字元

    Excel的VLOOKUP很好用, 然而它有一個255字元長度的限度. 當你的cell值字元>255時, VLOOKUP的回傳值就會成為#VALUE!

     解決方法使用Index Match即可, 請按這裡看詳細介紹. 


    總結:

    如果用了以上方法還是VLOOKUP找不到相對應值, 歡迎留言共同討論, 找出解決方法哦.




    延伸閱讀

    Excel 系列(一) - 檢查某些字是否在一長串字中?




    民國109年庚子(鼠)年1月1日, 西元2020年1月25日開始 Views

    張貼留言

    5 留言

    1. Tommy2020年4月27日 凌晨4:45

      您好偶然看到這篇文章，我使用了TRIM+SUBSTITUTE的方法，也確認過格式應該沒問題，但有種情況還是會找不到對應值。

      我現在遇到的情況是使用vlookup搭配模糊找查，找查的的資料是用逗號串接的資料格式，ex:WET3001,WET3004,WET3005,WET3006,WET3007,WET3008.......(資料量不定)
      我發現當儲存格的資料量超過一定數量(超過三行就會找不到資料值)，當資料量小於兩行就能找到資料，爬了一些文還沒找到解答，或許文字說明的方式，無法傳達我的意思，不知道是不是可以用gmail來互相討論之類的，感謝

      回覆刪除
      回覆
      1. Keeper's Review2020年5月2日 晚上11:27

        Hi Tommy您好, 本站上方新增了"連絡我們", 歡迎您填寫表格上傳您的excel檔案, 並清楚標明您欲搜尋的行列, 請確保檔案小於1MB. 收到後會儘快和您連絡, 謝謝.

        刪除
      2. Keeper's Review2020年5月4日 清晨5:04

        Hi Tommy您好, 請看"Excel 系列(七) - 如何突破VLOOKUP 255字元限制", 應該可以解決您的問題. 謝謝.
        https://keepersreview.blogspot.com/2020/05/vlookup-255.html

        刪除
      3. Keeper's Review2020年5月5日 凌晨12:16

        昨天題目沒看清, 今天更新了, 這樣應該可以用部份字元來尋找, 並回傳對應值, 謝謝
        =INDEX(Range of Return Value, MATCH(TRUE, INDEX(ISNUMBER(SEARCH(Partial String cell, Range of Anchor Point)), 0),0))

        =INDEX(回傳值範圍, MATCH(TRUE, INDEX(ISNUMBER(SEARCH(部份字串格, 搜尋目標範圍)), 0),0))

        刪除
      4. Keeper's Review2020年5月5日 凌晨12:49

        以你的例子, K3=INDEX(A:A,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(J3,F:F)),0),0))

        :img:https://1.bp.blogspot.com/-NQ12laUKAGE/XrEYl3goM6I/AAAAAAAACz8/QPReBA_z664c-iTn_TCgeBvD_r8Yt3V3wCLcBGAsYHQ/s1600/592943d67b31a.png:eimg:

        刪除