Please Help

[TLARS]

Hi,

the following formula seems to keep returning a zero when the looked up cell is blank.

=IF(A2="","",IF(ISNA(VLOOKUP(A2,'C:\New Folder\[CUSTOMER ADDRESS.xls]CUSTOMER ADDRESS'!$A$1:$Y$6000,7,FALSE)),"",VLOOKUP(A2,'C:\New Folder\[CUSTOMER ADDRESS.xls]CUSTOMER ADDRESS'!$A$1:$Y$6000,7,FALSE)))

What i need is for a blank to be returned .

any ideas?

 

[Derek Brown]

Yes, this seems to be a 'feature' of Excel when the lookup is on another worksheet.
Your second VLOOKUP (the first establishes whether there is something to be able to find) needs to be checked for zero length.
Here is a simple example that explains:
=IF(LEN(VLOOKUP(A2,Sheet2!$A$4:$B$6,2,FALSE))=0,"",VLOOKUP(A2,Sheet2!$A$4:$B$6,2,FALSE))

 

[TLARS]

Thanks Derek,

I can't understand why in some circumstances the formula works and returns a blank but for some reason this time it was a zero.

Thanks again

 

[MrKowz]

A possible reason it shows a blank sometimes is because the value found might actually be a null character "" returned from a formula. When it returns a 0, that means it is returning a cell that is truly blank (empty).

Are you wanting it to show blank for aesthetic purposes, or are you wanting it blank so you can test for blank in a calculation?