VLookUp, empty cells, and zeros

[Stephen White]

I am using VLoopUp to pull in data from worksheet A to worksheet B.

I find that if a cell in worksheet A, from which the data is to be
pulled in, is empty, a zero will be inserted in the appropriate cell in
worksheet B; and this is so whether the cells in the two worksheets are
formatted as General, Numbers, or Text.

I am using Excel 97 but I believe this behaviour is true of later
versions of Excel.

If the cell is empty in worksheet A, I want the corresponding cell in
worksheet B to remain empty after VLookUp has done its business.

Can VLookUp achieve this; or will I always have to edit the cells in
worksheet B to removed the zeros after VLoopUp has run?

Stephen White

 

[Vasant Nanavati]

=IF(VLOOKUP(........)=0,"",VLOOKUP(.........))

 

[Stephen White]

Why didn't I think of that?! Many thanks.

But following on from your answer, what data type is the zero that
VLookUp returns from an empty cell? Is it a variant or general?

And am I right in thinking that this is the standard behaviour of
VLookUp when it returns data from an empty cell and not something that
has gone slightly wrong with my version of Excel?

 

[Peo Sjoblom]

This is standard behavior of excel, if you link to another
sheet's/.workbook's blank cell, it will be returned as a zero
which you would need to remember if you do calculations that can be
dependent on that

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Stephen White]

Of course. Many thanks.

This is standard behavior of excel, if you link to another
sheet's/.workbook's blank cell, it will be returned as a zero
which you would need to remember if you do calculations that can be
dependent on that