# Excel: if then else with multiple criteria

Solved/Closed
Luchtain - Dec 10, 2009 at 02:44 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Dec 11, 2009 at 04:43 AM
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.
Related:

## 3 responses

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Dec 10, 2009 at 06:29 AM
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 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 > Luchtain
Dec 10, 2009 at 02:07 PM
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 > Luchtain
Dec 10, 2009 at 02:26 PM
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 > Luchtain
Dec 10, 2009 at 04:11 PM
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!!!
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Dec 10, 2009 at 07:48 PM
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
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Dec 11, 2009 at 04:43 AM
I have done much witgh open office. perhpas there "b" means second column. thanks.greetings