why are blank cells returning a 'zero' - instead of blank ?

Posted by Sandy on May 05, 2001 1:10 AM

Hi
First post -I'm a novice - but I've learned all I know from these pages!

This is the formula :(say it's in cell R48)

=IF(ISERROR(VLOOKUP(R46,$A$3:$C$550,2,FALSE)),"error muster nmbr missing ",VLOOKUP(R46,$A$3:$C$550,2,FALSE))
(the formula looks up names in col 2)

When I set up the s/sheet originally, if the 'R46'
ref (in col 2) was blank, the cell R48 reflected the blank.
If there was a name in the cell, it reflected the name.
However, if I now delete the name (using the delete key)cell
R48 shows a 0 (zero)instead of a blank cell.

Could someone help me out here? (be gentle with me!)

Thanks in advance

Posted by Aladin Akyurek on May 05, 2001 1:52 AM

Hi Sandy,

I don't know for sure, but there is some peculiar issue involved with the pair 'blank' and 'empty'. The cell was first BLANK; after the delete operation, it became EMPTY. This subtle difference (hard to say why this should be case) shows up thru some formulas like yours.

I'd like take the opportunity and make some suggestions to modify your formula.

=IF(ISBLANK(R46),"",IF(ISNUMBER(MATCH(R46,$A$3:$A$550,FALSE)),"Error: Master number missing",VLOOKUP(R46,$A$3:$C$550,2,FALSE)))

The first IF should take care of the BLANK/EMPTY issue. The second IF is equivalent to your VLOOKUP formula, but can be a bit less costly with large sets of data.

Additionally, you may insert 0 for FALSE to make the formula a bit shorter because FALSE=0 and TRUE=1.

To make the formula a bit nicer (IMHO), you could select the range $A$3:$A$550 and give it a name, say LVALUES (for lookup values), via the Name Box or the option Insert|Name|Define. Accordingly, you could name, say DATA, the range $A$3:$C$550.

The formula, rewritten, would look as:

=IF(ISBLANK(R46),"",IF(ISNUMBER(MATCH(R46,LVALUES,0)),"Error: Master number missing",VLOOKUP(R46,DATA,2,0)))

Hope this helps.

Aladin

=============

Posted by sandy on May 05, 2001 5:36 PM

THANKS Aladin, for taking the time to give me such
invaluable help ! It worked a treat - and I've learned a lot from your reply !