Excel (1) – 用 VLOOKUP 時除去 #N/A 結果

VLOOKUP  這個 Excel 的功能,算是我在工作的第一週,處理公司報價時才接觸得到,由於以往讀書時代,還是使用 MathLab 比較多,未有機會運用到 vlookup 這個玩兒。

不過不可以睇少 Excel 裡頭這些功能,Excel functions 就好似中國武術般招式多,配合起來應用卻好考功夫。這趟子我並非在這裡班門弄斧作 Excel 教學,只是把早前看到 vlookup 的新用法,特別是如何把 vlookup 的 #N/A 結果弄走,所以在這裡好好紀錄下來,免得我某天要用的時候又記不起來。

一直以來我都係用這句 vlookup 指令去配對另一個 data matrix 找尋相關資料

  • =VLOOKUP(“dog”,$A$1:$C$20,2,FALSE)


當在 A1:C20 找不到 “dog” 相關資料,便會回饋一個「#N/A」,若你要保留 vlookup 方程式在 spreadsheet 中,又不想出現難看的” #N/A ” 字眼,便要參考以下這句,取代  #N/A  而顯示 “no match”, 或者直接顯示空白 ( 用 “” 代替 “no match”),令 Excel spreadsheet 看起來更加清簡:

  • 回傳 “no match”
    =IF(ISNA(VLOOKUP(“dog”,$A$1:$C$20,2,FALSE)),”no match”,VLOOKUP(“dog”,$A$1:$C$20,2,FALSE))
  • 回傳 空白 “”
    =IF(ISNA(VLOOKUP(“dog”,$A$1:$C$20,2,FALSE)),””, VLOOKUP(“dog”,$A$1:$C$20,2,FALSE))

除了使用 ISNA,亦可以使用 COUNTIF 先去檢查 Table_array ( Column A ) 是否有 Lookup_value ,沒有的話顯示直接顯示 ” no match “,出現的話才顯示有關項目:

  • =IF(COUNTIF($A$1:$A$20,”dog”)>0, VLOOKUP(“dog”,$A$1:$C$20,2,FALSE),”no match”)

同樣利用 COUNTIF 的功能,那麼 VLOOKUP 的 Range_lookup 可以使用 “0” 代替 FALSE:

  • =IF(COUNTIF($A$1:$A$20,”dog”), VLOOKUP(“dog”,$A$1:$C$20,2,0),”no match”)

不過要注意,如果要尋找的資料 “dog” 曾經在 data matrix 出現超過一次,因為 vlookup 只會回饋第一個數據作結果。

♥ 參考: MrExcel Tip of the Day | Excel Tutorials and Tips | Big Resources |

You may also like...

7 Responses

  1. elain says:

    我諗咗好耐都做唔到去N/A , 有幸SEARCH 到呢一頁…. 所有問題解決晒~~~ THX MUCH

  2. Leung Chai says:

    俊,估唔到你幫到我….哈哈

  3. Jack Hui says:

    Terence, 係YAHOO search, 竟然係你幫到手, 真係要請您食飯先得

  4. terewong says:

    @JackHui 好啊,估唔到幫到你,仲有乜野可以幫到你 ?

  5. BULLMARK says:

    想問如果有一大堆數值,我想找其中幾個數值的總和.

    例: 1000,1500,2000,1600

    我要找尋$4500的組合,excel 能幫我尋找嗎?希望有人能解答!謝謝!

  6. terewong says:

    @BULLMARK 我不是 excel 專家啊,不過要當中幾個值的總和,可考慮 sumif ?

Leave a Reply

Your email address will not be published. Required fields are marked *