Excel replace na and 0 with empty

From LemonWiki共筆
Jump to: navigation, search

replace #N/A and 0 with empty when vlookup


1. replace 0 with empty

  • IF( logical_test, [value_if_true], [value_if_false] )
  • IF( VLOOKUP(A1,SHEET1!A:E,3,0) = 0, "", VLOOKUP(A1, SHEET1!A:E, 3, 0) )

2. replace #N/A with empty

  • =IFNA (value, value_if_na )
  • =IFNA( IF( VLOOKUP(A1,SHEET1!A:E,3,0) = 0, "", VLOOKUP(A1, SHEET1!A:E, 3, 0)), "" )


參考資料