SOLVED

How to how #n/a as an empty cell in an excel table (not chart)

Occasional Contributor

Hi - I'm trying to create a list in excel - from various sources - and where it comes up as #N/A or 0 I'd like just a blank.  How do I do this?

8 Replies
you can use IFERROR or IFNA functions to make #NA results blank.

For zero values you can use IF function.

so it will be like this.

IFERROR( IF (your formula = 0 ; ""; your formula) ; "")

I
Thank you. The current contents has for example, "=VLOOKUP(F2,FinalWorkshops,4)" ; how do I wrap that? Initial attempts failing.

@Jane_at_Stamma 

Like

=IFNA( VLOOKUP( F2, FinalWorkshops, 4), "" )
sorry, total novice here, so I typed in this
=IFERROR( IF (VLOOKUP(G7,FinalWorkshops,4)=0 ""; VLOOKUP(G7,FinalWorkshops,4) ; "")
which didn't work
best response confirmed by Jane_at_Stamma (Occasional Contributor)
Solution

@Jane_at_Stamma 

You shall use or comma or semicolon as delimiter, depends on you regional settings, Mix doesn't work. If comma, it could be like

=IFERROR(
  IF( VLOOKUP( G7, FinalWorkshops, 4) = 0,
        "",
       VLOOKUP(G7,FinalWorkshops,4) ),
 "")

If you open attached file and check G16 it'll be the formula in your locale.

Oh, thank you so much!!! That worked.
Because your list separator is not ";". Your list seperator is ","

You can see it in your vlookup formula.