Excel: if then else with multiple criteria

Solved/Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

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.

3 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
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
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Thanks for the very quick reply!

I've had the opportunity to give this a try, however I am getting the error #508 (Missing bracket, for example, closing brackets, but no opening brackets).

I am using Open Office Calc, perhaps this is the issue.. I'll try using the formula wizard with this concept and see if I can troubleshoot!
> Luchtain
Okay! Got that to work with this change: =VLOOKUP(A29;$A$1:$B$26;'b';FALSE)

Now I'm looking to sum the values returned. Laughing.. Error (#N/A).

With a bit more digging, I believe I saw the answer to that question on this forum, and should be able to track it down again.

Thanks so much for your help..
You Rock!!
> Luchtain
In the case of the sum value returning #n/a, I have included additional cells in the calculation, and these cells did not have a letter to match.

Example..

The total letters in the sum were 25, the name entered was only 15 letters long, so the remaining 10 calculations were returning the #n/a which ended up in the sum calculation.

It did work fine as long as there were no empty letter cells.

hmmmm..
> Luchtain
After much playing around, I came up with this, which seems to do the trick.

=IF(ISNA(VLOOKUP(A28;$A$1:$B$26;"2";0));"0";(VLOOKUP(A28;$A$1:$B$26;"2";0)))

Without your original string, I wouldn't know where to begin.
Thanks again for your help!

Solved!!!
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
In excel file it should work and it works in my computer

regarading vlookup formula in excel the third argument is the column number . typing "b" will not help
in my formula it 2 that means the second column in the reference range that is column B (that is second argument)

by the by are we talking about excel file or some other file
From home, I was using Excel, however at work I must use Open Office Calc.
In which case the second column only worked if the argument was "b".

If I should open and use the file at home using excel, then if excel doesn't automatically do the conversion, I know I will have to swap the "b" back to "2"...

All is working well, and solution was successful thanks to your assistance..

Blessings
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
I have done much witgh open office. perhpas there "b" means second column. thanks.greetings