Help with Excel 2003 (multiple IF statements)
 

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

Help with Excel 2003 (multiple IF statements)
 

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

Help with Excel 2003 (multiple IF statements)
 

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

Help with Excel 2003 (multiple IF statements)
 

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
---------------
Jacob Skaria


"Steve Chalom" wrote:

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

Help with Excel 2003 (multiple IF statements)
 

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

Help with Excel 2003 (multiple IF statements)
 

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

Kind Regards

Steve Chalom

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"Steve Chalom" wrote:

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