=IF???

  • Thread starter Thread starter Dewayne
  • Start date Start date
D

Dewayne

I need to return values in a cell based on the "letter" in
the referenced cell, i.e. if A1="A" then return 33 in Z1.
=IF(A1 "A",33) This is fine up to the maximum 7 nested IF
functions.
I actually have 20 different values to return based on
the "letter" in a cell, example:
if"A",33
if"B",18
if"C",9
if"D",4.5
if "etc", "etc"...... A through T
Any ideas on handling this are appreciated.
Thank you
Dewayne
 
If you really meant a,b,c,d etc then this will work if you list starts at A1

=CHOOSE(CODE(INDIRECT("A"&ROW()))-96,33,18,9,4.5)
 
Dewayne,

make a table (somewhere off the visible sheet area and put
all your letters and their values in the adjacent cell, like

column Y Column Z
A 33
B 18
C 9

and so on
assume you put the letter in Y1:Y20 and the values in Z1:Z20
then use this formula

=IF(A1="","",VLOOKUP(A1,Y1:Z20,2,0))

To make sure users enter these letters you can select
A1, do data>validation and under allow select list and
in the list box source put

=$Y$1:$Y$20

uncheck in cell dropdown to keep it and let the users select from the
dropdown
 
Thanks for your help! It is appreciated.
Dewayne
-----Original Message-----
Dewayne,

make a table (somewhere off the visible sheet area and put
all your letters and their values in the adjacent cell, like

column Y Column Z
A 33
B 18
C 9

and so on
assume you put the letter in Y1:Y20 and the values in Z1:Z20
then use this formula

=IF(A1="","",VLOOKUP(A1,Y1:Z20,2,0))

To make sure users enter these letters you can select
A1, do data>validation and under allow select list and
in the list box source put

=$Y$1:$Y$20

uncheck in cell dropdown to keep it and let the users select from the
dropdown
--

Regards,

Peo Sjoblom




.
Click to expand...
 
Appreciate your help!
Dewayne
-----Original Message-----
If you really meant a,b,c,d etc then this will work if you list starts at A1

=CHOOSE(CODE(INDIRECT("A"&ROW()))-96,33,18,9,4.5)

--
Don Guillett
SalesAid Software
(e-mail address removed)



.
Click to expand...
 
Back
Top