nested IF(OR Vlookup returning zero instead of value in target cel

ncjefffl

unread,

Oct 31, 2008, 11:42:01 AM10/31/08

to

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!!

ShaneDevenshire

unread,

Oct 31, 2008, 12:29:03 PM10/31/08

to

Hi,

This might work
=CLEAN(CONCATENATE(Comment1," ",COMMENT2," ",COMMENT3," ",COMMENT4))

--
Thanks,
Shane Devenshire

ncjefffl

unread,

Oct 31, 2008, 12:41:01 PM10/31/08

to

Thanks for the suggestion, but unfortunately, I'm getting the same result.
It gave me an idea and I tried =Trim(Concatenate(Comment1," ",Comment2,"
",Comment3," ",Comment4) as well, just for giggles, but no go.

Dave Peterson

unread,

Oct 31, 2008, 1:11:21 PM10/31/08

to

That means that the first match for what's in C4 has a corresponding empty cell
(or is really 0).

My bet is that you're missing that row that's higher up the worksheet (maybe the
row is hidden, maybe by a filter???).

Try:

=match(c4,'bau calculations'!n:n,0)

and see if that's the row number you expected.

it that doesn't help, what's in C4?

No wildcards (asterisk or question mark), right????

--

Dave Peterson

ncjefffl

unread,

Oct 31, 2008, 1:29:00 PM10/31/08

to

Thanks for the suggestion, Dave, but no help there. I entered =match(c4,'bau
calculations'!a:a,0) (the match to c4 is in A6 of the bau calculations
worksheet) and it returned the correct row: 6. I've got no hidden rows or
columns, no filter on, no wildcards.

Dave Peterson

unread,

Oct 31, 2008, 3:23:24 PM10/31/08

to

Is calculation set to manual or automatic?

if you type:
='bau calculations'!n6
what is returned?

Is there any custom numberformat that could be hiding the real value?

--

Dave Peterson

ncjefffl

unread,

Oct 31, 2008, 4:04:06 PM10/31/08

to

Dave, thanks so much for your time. I finally found the problem. One of the
formulas in one of the contributing cells was focused on the wrong column.
At least, I assume that was the problem, because when I changed that
everything else started working.

Dave Peterson

unread,

Oct 31, 2008, 5:20:18 PM10/31/08

to

Those are the kind of bugs that are a pain to find.

--

Dave Peterson