Return blank for empty or NA values

**shelluk** · 08-18-2010, 01:25 PM

Hi Guys

I have a spreadsheet where I get a mixture of either 0 results (because the field found is empty) or #N/A results (because a match isn't found) from a vlookup.

I need both of these to just return an empty cell.

Any ideas how? I've managed one or the other but getting in a muddle over doing both!

Thanks
Shell

**shg** · 08-18-2010, 01:28 PM

=if(isna(your expression), "", if(your expression = 0, "", your expression))

It has to evaluate the expression three times ...

**ChemistB** · 08-18-2010, 01:28 PM

Hard to say without looking at your formulas. The easiest way is to nest your formula inside an IF Statement
=IF(ISERROR(MATCH(.....)),"", Yourformula)

=IF(yourformula = 0, "",yourformula)
Does that help?

**DonkeyOte** · 08-18-2010, 01:31 PM

If we assume the value being returned is text (implied by 0 comment)

=LOOKUP(REPT("Z",255), CHOOSE({1,2},"", VLOOKUP(A1,B:C,2,0)))

The VLOOKUP is only performed once in this instance (modify the VLOOKUP of course)

**DonkeyOte** · 08-18-2010, 01:56 PM

I would add the caveat that should the VLOOKUP return a string > 255 chars this approach would not work.

**shg** · 08-18-2010, 02:12 PM

I've not seen that construct before, and find it very clever.

**shelluk** · 08-19-2010, 10:20 AM

Cheers shg, your first post did the trick.

Thanks
Shell

**shg** · 08-19-2010, 11:15 AM

You're welcome, but I would urge you to use DO's. It's a little more complicated, but much more efficient, especially if the last argument to the lookup is FALSE.