OR Function Problem

[Jamie]

Hi all,

I'm hoping someone can help me. I have written the
following formula and it works exactly how I want it to.

=IF(OR(N7="")," ",IF(OR(N7=$IO$10),$IP$10,IF(OR
(N7=$IO$11),$IP$11,IF(OR(N7=$IO$12),$IP$12,IF(OR
(N7=$IO$13),$IP$13,IF(OR(N7=$IO$14),$IP$14,IF(OR
(N7=$IO$15),$IP$15)))))))

However, I need to add an extra two rows to the formula
but when I do exactly the same thing i.e

=IF(OR(N7="")," ",IF(OR(N7=$IO$10),$IP$10,IF(OR
(N7=$IO$11),$IP$11,IF(OR(N7=$IO$12),$IP$12,IF(OR
(N7=$IO$13),$IP$13,IF(OR(N7=$IO$14),$IP$14,IF(OR
(N7=$IO$15),$IP$15,IF(OR(N7=$IO$16),$IP$16)))))))

It tells me the formula I've typed contains an error. Why
is this? Is there a limit to the amount of OR functions
that you can use in one statement?

Any help would be very greatfully appreciated

Thanks in advance

Jamie

 

[Stephen Bye]

Try:
=IF(N7="","",INDEX(IP10:IP17,MATCH(N7,IO10:IO17,0)))

 

[Ron Rosenfeld]

Hi all,

I'm hoping someone can help me. I have written the
following formula and it works exactly how I want it to.

=IF(OR(N7="")," ",IF(OR(N7=$IO$10),$IP$10,IF(OR
(N7=$IO$11),$IP$11,IF(OR(N7=$IO$12),$IP$12,IF(OR
(N7=$IO$13),$IP$13,IF(OR(N7=$IO$14),$IP$14,IF(OR
(N7=$IO$15),$IP$15)))))))

However, I need to add an extra two rows to the formula
but when I do exactly the same thing i.e

=IF(OR(N7="")," ",IF(OR(N7=$IO$10),$IP$10,IF(OR
(N7=$IO$11),$IP$11,IF(OR(N7=$IO$12),$IP$12,IF(OR
(N7=$IO$13),$IP$13,IF(OR(N7=$IO$14),$IP$14,IF(OR
(N7=$IO$15),$IP$15,IF(OR(N7=$IO$16),$IP$16)))))))

It tells me the formula I've typed contains an error. Why
is this? Is there a limit to the amount of OR functions
that you can use in one statement?

Any help would be very greatfully appreciated

Thanks in advance

Jamie

1. There is a nesting limit of seven functions -- it doesn't matter what the
functions are. In your second formula you nest more than seven functions.

2. Unless I am missing something, all of your OR functions have only a single
argument. That being the case, it would seem as if the OR functions are not
needed.

3. If I understand your formula correctly, an equivalent (and more easily
extensible) formula might be:

=IF(N7=""," ",VLOOKUP(N7,$IO$10:$IP$16,2,FALSE))


--ron

 

[Jamie]

Brilliant

Thanks alot

-----Original Message-----
Try:
=IF(N7="","",INDEX(IP10:IP17,MATCH(N7,IO10:IO17,0)))




.