This is driving me nuts. I have a formula which is returning a zero instead
of the content of the target cell, which has a formula resulting in a text
value, " ineligible until Jan 09"
I'm guessing it has something to do with the leading blanks in the target
cell, because it works in some cells where the result does not begin with
blanks. I tried copying the formula from a cell where it works into the
cells where it doesn't and instead of getting the correct value it returns
the value from the original formula, even though the new formula references
different cells. Below are the formulas which I hope will make it clearer:
Problem Formula:
=IF(OR(S4<"",T4<""),VLOOKUP(C4,'BAU CALCULATIONS'!A:N,14,0),"")
S4 contains formula:
=IF(Partial<"","",VLOOKUP(C4,'BAU CALCULATIONS'!A:I,9,0)) which displays a
value of "Y". T4 has formula =IF(Full<"","",VLOOKUP(C4,'BAU
CALCULATIONS'!A:J,10,0)), which results in a blank.

The target cell of the Vlookup contains formula =CONCATENATE(Comment1,"
",COMMENT2," ",COMMENT3," ",COMMENT4) and results in the value " Ineligible
until Jan 09". Note the leading blanks in the result.

Why am I getting a zero value instead of " Ineligible until Jan 09"?

I used formula auditing and it looks like it's going to work as it evaluates
to:
If(True," Inelligible Until Jan08",""). Unfortunately, it goes one step
further and returns "0".

I've wasted my whole morning on this so far and am up against a deadline.
HELP!!