Excel replace na and 0 with empty

From LemonWiki共筆
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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)), "" )


參考資料