Excel 系列(七) - 如何突破VLOOKUP 的255字元長度限制

Views
Excel VLOOKUP 很好用, 然而它有一個255個字元長度的限制, 當您搜尋的cell裡字元長度一旦超過了255個字元時, VLOOKUP傳回值會是#VALUE! 



    如何突破VLOOKUP 的255字元長度限制呢? 解決方法很簡單, 使用 INDEX MATCH即可.
    用完整個長字串搜尋

    VLOOKUP是用來搜尋回傳對應值最簡單也最好用的方法. 它的語法如下:
    =VLOOKUP(D2,I:J,2,0)
    換成文字就是:
    =VLOOKUP(查詢格, 被查詢範圍, 回傳值位置, 0或1)

    其中0=完全符合/false, 1=大約符合/true

    然而如果搜尋和被搜尋的儲存格>255個字元時, 那麼VLOOKUP就無法應付了. 

    舉例說明:
    如果Parts的儲存格字元>255時, 用VLOOKUP來搜尋對應值(Part-S或是Category), 回傳值都會是#VALUE!

    如果Parts的儲存格字元<255時, VLOOKUP會成功的回傳正確的對應值(如上圖).
    F2=VLOOKUP(D2, I:J, 2, 0)

    PS: 可以使用=LEN(D2)來查看cell的字元長度


    要解決這個255個字元的方法其實很簡單, 用 INDEX MATCH來替代VLOOKUP即可.
    公式如下:
    G3=INDEX(J:J, MATCH(TRUE, INDEX(I:I=D3, 0),0))


    把上述公式口語化就是:

    =INDEX(回傳值範圍, MATCH(TRUE, INDEX(搜尋目標範圍=搜尋對應格, 0),0))

    =INDEX(Range of Return Value, MATCH(TRUE, INDEX(Range of Search Target=Search Cell, 0),0))

    用部份的字串搜尋完整長字串的對應值

    如果要搜尋的不是完整的D cell來對應完整的I, 而是要部份的D來對應I, 回應J. 

     例如, 輸入部份的字串(人名=鄭成功), 傳回相對應的值(部門=行銷)

    詳情請看這篇: Excel 系列(九) - 如何只用部份字串來搜尋相對應儲存格的對應值



    延伸閱讀



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

    張貼留言

    0 留言