Excel replace na and 0 with empty: Difference between revisions
Jump to navigation
Jump to search
mNo edit summary |
mNo edit summary |
||
| Line 17: | Line 17: | ||
[[Category:Spreadsheet]] [[Category:Excel]] | [[Category:Spreadsheet]] [[Category:Excel]] | ||
[[Category:Data transformation]] | |||
[[Category:Data_hygiene]] | [[Category:Data_hygiene]] | ||
[[Category:Data Science]] | [[Category:Data Science]] | ||
Latest revision as of 11:15, 19 April 2015
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)), "" )
參考資料