if..then statement with three or more conditions

21MSU · Mar 26th 2003

I have three or more choices that maybe selected in a certain cell on my spreadsheet. For instance, let's say I may choose five different choices in that cell

If that choice equals L60, then cell A10 should display a certain value

If that choice equals L100, then cell A10 should display a certain value

If that choice equals L500, then cell A10 should display a certain value

If that choice equals H100, then cell A10 should display a certain value

If that choice equals H500, then cell A10 should display a certain value

I only know how to write an IF..THEN statement for two conditions. Can an IF...THEN statement be written to accomodate three or more conditions?

I do not want to use a macro to accomplish this

Please Help!!
Thanks!!

Paddyd · Mar 26th 2003

you need:

a) a 2 column lookup table that relates the options
b) a vlookup() formula that returns the appropriate values from the table.

check out clookup() in the help file. post back with more info about uyour conditions if you can't get it to work.

paddy

Neale · Mar 26th 2003

Here goes

=IF(L60=Cell,A10=x,if(L100=Cell,a10=x,if(L500=Cell,A10=x,if(H100=Cell,a10=x,if(H500=Cell,a10,0)))))

Cell = chosen cell reference
x = certain value you mentioned

You can only "nest" 7 IF Functions

HTH
:guitar:

Anonymous · Mar 26th 2003

yeppers :

=IF(OR(A1=7,A1=10,A1=55,A1=100),"Yes","no")

this will put a "yes" if A1 =7 or 10 or 55 or 100

=IF(AND(A1=7,B1=10,C1=55,D1=100),"Yes","no")

this will put a "yes" if A1=7 and B1=10 and C1=55 and D1=100

you can also combine ORs and ANDS till your heart's content (subject to normal 7 nested conditions), although I'll not confuse the issue at this stage

21MSU · Mar 26th 2003

Thanks, Neale and Chris, and Paddyd

Paddyd, although I am going to use nested if functions,I'm interested in your solution. Here is a table of conditions and results

Let's say cell B3 is where I want to result to appear, and B2 is the pull down menu w/ options.

Neale · Mar 26th 2003

Formula for B3

=VLOOKUP(B2,$C$17:$D$21,2,0)

This is heaps better than IF statements.

HTH:guitar:

21MSU · Mar 26th 2003

thanks, Neale that is a lot simplier

if..then statement with three or more conditions

Participate now!

Share