Excel replace na and 0 with empty

From LemonWiki共筆

Revision as of 05:51, 17 April 2015 by Planetoid (talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

Jump to navigation Jump to 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)), "" )

參考資料

Retrieved from "https://wiki.planetoid.info/index.php?title=Excel_replace_na_and_0_with_empty&oldid=14913"