Excel - - if then else with multiple criteria

January 2017



Issue


Essentially, I would like to determine a numeric value (1-9) for the letters of the alphabet. Returning the number value in relation to a name.

example:
if:

"a", "j", "s" =1
"b", "k", "t" =2
"c", "l", "u" =3
"d", "m", "v" =4
"e", "n", "w" =5
"f", "o", "x" =6
"g", "p", "y" =7
"h", "q", "z" =8
"i", "r"=9


entering: m a r k
should return: 4 1 9 2

I have tried each letter of the name being in it's own cell and using "if, then, else" statements; "or" statements; and vlookup but continue to have trouble with the multiple (9) arguments.

Solution


I shall tell you one way how to do it

in A1 type
a
in A2 type
=CHAR(CODE(A1)+1)

copy A2 down till A26

you will get all the lower case alphabets
in B1 type 1 to 9 for alphabets a to i(cells B1 to B9)
highlight B1 to B9 and edit-copy
select B10 edit-paste
again select B19 edit-paste


Type

in four adjacent cells e.g A28 to D28
m a r k (each of the letters)
in A29 type this formula
=VLOOKUP(A28,$A$1:$B$26,2,FALSE) 

copy A29 to B29,c29 and D29

you will get

4 1 9 2

Note


Thanks to venkat1926 for this tip on the forum.

Related


Published by aakai1056. Latest update on December 10, 2009 at 03:52 PM by aakai1056.
This document, titled "Excel - - if then else with multiple criteria," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).