Help with Excel 2003 (multiple IF statements)

[Steve Chalom]

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

 

[Jarek Kujawa]

there is a limit of 7 nested IFs in Excel 2003

look here:

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

for a workaround

 

[Jacob Skaria]

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]

Doesnt make a difference...With the list in Sheet2 ColA and ColB try the
below formula in Sheet1

Sheet1 A1 = drop down
In Sheet1 B1 enter the below formula
=VLOOKUP(A1,Sheet2!A:B,2,0)

'Further to handle missing entries in list you can use the modified version
of the formula...
=IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,0)),"Not found",VLOOKUP(A1,Sheet2!A:B,2,0))

If this post helps click Yes

 

[Steve Chalom]

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

 

[Steve Chalom]

Thank you so much, I can't tell you how much I appreciate this
YAY - it worked.

Kind Regards

Steve Chalom