If statement with multiple ifs

[Sean N.]

Not sure if I'm explaining this right but here it goes:

Trying to get an IF statement where if a certain set of data is entered into
a cell the resulting cell will have a certain output:
ie. If A1=Red then b1 would be 1 or if A1=Blue then b1 would be 2, etc.....

Hope someone can help on this!
Thanks,
Sean N

 

[David Biddulph]

For nested IFs, in B1 insert the formula
=IF(A1="Red",1,IF(A1="Blue",2,"alternative result if neither red nor blue"))

If there are too many alternatives for the nesting limit in Excel (7 in
Excel 2003), then VLOOKUP may be the best bet.

 

[Max]

Use VLOOKUP to simplify things

List all the associations/choices in cols A & B in Sheet2 (say), eg:
Red 1
Blue 2
etc

Then in any other sheet,
assuming the lookup values (Red, Blue) are in A2 down
you can use this in B2: =IF(A2="","",VLOOKUP(A2,Sheet2!A:B,2,0))
Copy B2 down as needed
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik

 

[JBeaucaire]

You can do the work in the formula without the VLOOKUP table somewher
by showing the options _in_the_formula_itself_, too. CHOOSE is a goo
option if the cell you're referencing is going to have a number i
ascending order...like 1, 2 or three

A1 =CHOOSE(B1,\"RED\",\"BLUE\",\"GREEN\"
This would PUT the word RED in cell A1 if B1 had the number 1 in it
==========
To go the other way, seeing "RED" and getting "1" in return, a LOOKU
with the arrary IN the formula works

*B1 =LOOKUP(A1,{"blue","green",**"red"**},{2,3,1})
I think this is what you were originally asking...the order is weir
because it needs to be alphabetical

 

[Shane Devenshire]

Hi,

Personally I would usually use a lookup table because it means that if
things change, you can edit the table, a range in the spreadsheet, rather
than a series of formulas.

That said, if you want to store the results in the formula the order would
be more logical if you use VLOOKUP:

=VLOOKUP(A1,{"Red",1;"Blue",2;"Green",3},2,)