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.
--
David Biddulph

"Sean N." wrote in message
...
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

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
---
"Sean N." wrote:
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.....

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.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45339

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


"JBeaucaire" wrote:


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.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45339