## If statement with multiple ifs

### If statement with multiple ifs

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

### If statement with multiple ifs

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.

### If statement with multiple ifs

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://www.yqcomputer.com/
xdemechanik
---

### If statement with multiple ifs

You can do the work in the formula without the VLOOKUP table somewhere
by showing the options _in_the_formula_itself_, too. CHOOSE is a good
option if the cell you're referencing is going to have a number in
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 LOOKUP
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 weird
because it needs to be alphabetical.

--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.yqcomputer.com/

### If statement with multiple ifs

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,)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire