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