Hi there,
I need help with a multiple IF formula.
I have used a drop down list in 1 cell that has about 20 items in it.
I have used an "IF <cell = "test", type formula but it seems to fail after
8 IF's??

Why is this - as I need to return about 20 different responses from the
worksheet.
i.e. IF B2="strawberries","fruit"
IF B2="pumpkin","vegetable"
etc.

How do I include all of this in one line? is it possible?

Much Appreciated

Steve

there is a limit of 7 nested IFs in Excel 2003

look he

http://www.cpearson.com/Excel/nested.htm

for a workaround

On 3 Lis, 14:08, Steve Chalom <Steve
wrote:
Hi there,
I need help with a multiple IF formula.
I have used a drop down list in 1 cell that has about 20 items in it.
I have used an "IF <cell = "test", type formula but it seems to fail after
8 IF's??

Why is this - as I need to return about 20 different responses from the
worksheet.
i.e. IF B2="strawberries","fruit"
IF B2="pumpkin","vegetable"
etc.

How do I include all of this in one line? is it possible? * *

Much Appreciated

Steve

In 2003 there is a limit of 7 for nested levels of functions.

Arrange the data as below in ColA/ColB and use the below formula to pick the
value from ColB

=VLOOKUP("pumpkin",A:B,2,0)
OR with 'pumpkin" in cell C1
=VLOOKUP(C1,A:B,2,0)

Col A Col B
strawberr fruit
pumpkin vegetable
- -
- -
- -
- -
- -
- -


If this post helps click Yes
---------------
Jacob Skaria


"Steve Chalom" wrote:

Hi there,
I need help with a multiple IF formula.
I have used a drop down list in 1 cell that has about 20 items in it.
I have used an "IF <cell = "test", type formula but it seems to fail after
8 IF's??

Why is this - as I need to return about 20 different responses from the
worksheet.
i.e. IF B2="strawberries","fruit"
IF B2="pumpkin","vegetable"
etc.

How do I include all of this in one line? is it possible?

Much Appreciated

Steve

Hi Jacob, thanks so much for taking time to help.
The 'VLOOKUP' Function might well be the solution I'm looking for (or at
least part of it).
What I left out of my original question, is that I am using a drop down list
from a validation table - (with all the lists and stuff hidden in sheet 2).
So the user would pick their choices from the drop down list on sheet 1 - and
it would need to return the answers 1 at a time. Not sure if this changes
your response?

Thanks again

Steve

"Jacob Skaria" wrote:

In 2003 there is a limit of 7 for nested levels of functions.

Arrange the data as below in ColA/ColB and use the below formula to pick the
value from ColB

=VLOOKUP("pumpkin",A:B,2,0)
OR with 'pumpkin" in cell C1
=VLOOKUP(C1,A:B,2,0)

Col A Col B
strawberr fruit
pumpkin vegetable
- -
- -
- -
- -
- -
- -


If this post helps click Yes
---------------
Jacob Skaria


"Steve Chalom" wrote:

Hi there,
I need help with a multiple IF formula.
I have used a drop down list in 1 cell that has about 20 items in it.
I have used an "IF <cell = "test", type formula but it seems to fail after
8 IF's??

Why is this - as I need to return about 20 different responses from the
worksheet.
i.e. IF B2="strawberries","fruit"
IF B2="pumpkin","vegetable"
etc.

How do I include all of this in one line? is it possible?

Much Appreciated

Steve