Excel: if then else with multiple criteria

Solved/Closed
Luchtain - Dec 10, 2009 at 02:44 AM
venkat1926 Posts 1864 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.

3 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
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
1
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!
0
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!!
0
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..
0
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!!!
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
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
0
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
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Dec 11, 2009 at 04:43 AM
I have done much witgh open office. perhpas there "b" means second column. thanks.greetings
0