chaining if formula and vlookup

[clammastak]

I been staring at this IF Chain formula for days trying to figure out. I think there may be an easier way, but here is the logic of it if anyone could please help. the formula will belong in cell X2

if W2 > v2 , then return text "push out"
if W2 < V2, then return text "pull in"
if W2 = V2, then return text "no change"
if W2 = "SEE STATUS", then vlookup(a2,table,27,false) (this will return a date value)
**this last part is where I am stumped**
if W2 = "SEE STATUS", and the vlookup(a2,table,27,false) returns N/A or error, then return text "Cancelled"

this is the formula I currently have and doesn't seem to be working. gives me a "VALUE" on the ones that are "SEE STATUS":

Please Login or Register  to view this content.

[oeldere]

Maybe like this (ontested).

=IF((W3-V3)<0,"PULLED IN",IF((W3-V3)>0,"PUSHED OUT",IF(W3=V3,"NO CHANGE",iferror(IF(ISTEXT(W3),VLOOKUP(A3,'OTD-main'!$B:$AA,27,FALSE),"Cancelled")))))

[clammastak]

it says I've entered too few arguments.

[bebo021999]

Put the text-testing first, then value-testing:

=IF(ISTEXT(W3),IFERROR(VLOOKUP(A3,'OTD-main'!$B:$AA,27,0),"cancel"),IF(W3=V3,"NO CHANGE",IF(W3<V3,"PULLED IN","PUSHED OUT")))

[JohnTopley]

Try ...


=IF((W3-V3)<0,"PULLED IN",IF((W3-V3)>0,"PUSHED OUT",IF(W3=V3,"NO CHANGE",IFERROR(IF(ISTEXT(W3),VLOOKUP(A3,'OTD-main'!$B:$AA,27,FALSE),"Cancelled"),""))))

[clammastak]

Bebo, yours worked amazing. thanks everyone for your input!

[clammastak]

hold on, I spoke too soon. Bebo, yours seemed to give me "Cancelled" even if the vlookup value existed in the OTD table.

[clammastak]

After further investigating, it seems the vlookup function is giving me a "REF!" error.

[bebo021999]

After further investigating, it seems the vlookup function is giving me a "REF!" error.

I though same. Check your lookup_value(A3) in OTD sheet to make sure they are same format.

i.e A3 is number stored as text : using VLOOKUP(A3+0,...)

[clammastak]

Bebo, your formula worked great! I resolved the issue...and it was that "27" was not the right count from $B:$AA. Its actually only 26 ...D'oh. so it was referencing a column not within the range of the table.

[bebo021999]

Check the lookup table: it has only 26 columns while you are looking 27th column!

[bebo021999]

Yah. nice tohear it works. Good luck!