0
Thanks

A few words of thanks would be greatly appreciated.

Excel - - if then else with multiple criteria



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.
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
Thanks to venkat1926 for this tip on the forum.
0
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
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).

0 Comments