Developer Forum

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

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.

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/
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---

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/
View this thread: http://www.yqcomputer.com/

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