Formula

[Smooth75]

What is wrong with this formula?

=IF(VLOOKUP(C10,$J$22:$K$53,2,FALSE)="W",IF(VLOOKUP(D10,$J$22:$K$53,2,FALSE="W"),IF(VLOOKUP(E10,$J$22:$K$53,2,FALSE)="W",IF(VLOOKUP(F10,$J$22:$K$53,2,FALSE)="W",IF(VLOOKUP(G10,$J$22:$K$53,2,FALSE)="W",IF(VLOOKUP(H10,$J$22:$K$53,2,FALSE)="W","Still In","OUT"))))))

Please advise

 

[Bob Umlas]

If any of the pieces, say VLOOKUP(C10,$J$22:$K$53,2,FALSE), isn't found,
this returns #N/A and that gets "propagated" through the formula. I'm
guessing you're getting #N/A. The values in C10:H10 MUST all exist in
J22:J53 or you'll see this error.

 

[Smooth75]

Bob,

I get "#VALUE" as the answer.

--
Thanks

Smooth

If any of the pieces, say VLOOKUP(C10,$J$22:$K$53,2,FALSE), isn't found,
this returns #N/A and that gets "propagated" through the formula. I'm
guessing you're getting #N/A. The values in C10:H10 MUST all exist in
J22:J53 or you'll see this error.

 

[Dave Peterson]

One thing wrong is the postion of the closing paren in the second if expression:

...,FALSE="W"),...

should probably be:
...,false)="w"),...

=IF(VLOOKUP(C10,$J$22:$K$53,2,FALSE)="W",
IF(VLOOKUP(D10,$J$22:$K$53,2,FALSE)="W",
IF(VLOOKUP(E10,$J$22:$K$53,2,FALSE)="W",
IF(VLOOKUP(F10,$J$22:$K$53,2,FALSE)="W",
IF(VLOOKUP(G10,$J$22:$K$53,2,FALSE)="W",
IF(VLOOKUP(H10,$J$22:$K$53,2,FALSE)="W","Still In","OUT"))))))

Did you really want all those things to be true to get "still in"?

And since you don't ever check for errors, you may see #n/a's.

But #value! usually means that the you have that error somewhere in the ranges
(C10:h10 or J22:k53) and you're returning that error.