If/Then Statement

[Guest]

The following works fine:
=IF(P$5=8,O$5,IF(P$6=8,O$6,IF(P$7=8,O$7,IF(P$8=8,O$8,IF(P$9=8,O$9,IF(P$10=8,O$10,IF(P$11=8,O$11,IF(P$14=8,O$14,0))))))))

But when I try to add one more section to it, I get an error and I can't
understand why.

I am trying to get
this:=IF(P$5=8,O$5,IF(P$6=8,O$6,IF(P$7=8,O$7,IF(P$8=8,O$8,IF(P$9=8,O$9,IF(P$10=8,O$10,IF(P$11=8,O$11,IF(P$14=8,O$14,IF(P$15=8,O$15,0)))))))))
but I continue to get an error.

 

[Guest]

Excel versions prior to 2007 limit you to 7 nested IF()s.

how about this:

=IF(ISERROR(MATCH(8,P$5$11,0)),IF(P$14=8,O$14,0),INDEX(O$5:O$11,MATCH(8,P$5$11,0)))

 

[Pete_UK]

You've hi-jacked Karen's thread. There is a limit of 7 nested IFs in
Excel, which is what you have in your first formula. In your second
formula you are trying to add more and Excel will not allow you to.
You can get round this limitation with a different approach - try this
instead:

=VLOOKUP(8,P5:O15,2,0)

I see that there are no tests for P12 and P13, but as long as these do
not contain 8 then the formula will work okay. If you have more cells
to test beyond row 15, just change the O15 part to suit.

Hope this helps.

Pete

 

[Guest]

Pete -

Unfortunately, the OP is matching the lookup value to column P and
retrieving the value in column O - the column to the LEFT - so VLOOKUP()
won't work.

 

[Pete_UK]

Ah, right - well spotted, Duke.

Maybe one day I'll learn the alphabet !! <bg>

Pete