I am a bit rusy with LOOKUP and much better on IF statements. My problem is
I have 14 conditions to consider and IF will only go up to 7. Here's what I
want to say:
1c is equal to 7 points
1b " 9 "
1a 11
2c 13
2b 15
2a 17
3c 19
3b 21
3a 23
4c 25
4b 27
4a 29
5c 31
5b 33
Any one of the above number letter score could go in the required field so
they all have to be in the formula. If I type say 4b into a box I want the
number 25 to appear in the answer.

can you setup the table you just made below in a separate area of the
worksheet and then use vlookup to return your result? Just make sure the
list is sorted (so 1a is first, 1b is next, etc.) and vlookup should do the
trick.
--
Please remember to indicate when the post is answered so others can benefit
from it later.


"Jax" wrote:

I am a bit rusy with LOOKUP and much better on IF statements. My problem is
I have 14 conditions to consider and IF will only go up to 7. Here's what I
want to say:
1c is equal to 7 points
1b " 9 "
1a 11
2c 13
2b 15
2a 17
3c 19
3b 21
3a 23
4c 25
4b 27
4a 29
5c 31
5b 33
Any one of the above number letter score could go in the required field so
they all have to be in the formula. If I type say 4b into a box I want the
number 25 to appear in the answer.

=IF(A2="","",VLOOKUP(A2,MyTable,2,0))

where A2 is the cell you put the value in and MyTable a 2x2 column table
just as you posted

or hardcoded

=IF(A2="","",VLOOKUP(A2,{"1c",7;"1b",9;"1a",11;"2c ",13;"2b",15;"2a",17;"3c",19;"3b",21;"3a",23;"4c", 25;"4b",27;"4a",29;"5c",31;"5b",33},2,0))



--


Regards,


Peo Sjoblom





"Jax" wrote in message
...
I am a bit rusy with LOOKUP and much better on IF statements. My problem
is
I have 14 conditions to consider and IF will only go up to 7. Here's
what I
want to say:
1c is equal to 7 points
1b " 9 "
1a 11
2c 13
2b 15
2a 17
3c 19
3b 21
3a 23
4c 25
4b 27
4a 29
5c 31
5b 33
Any one of the above number letter score could go in the required field so
they all have to be in the formula. If I type say 4b into a box I want
the
number 25 to appear in the answer.

You can calculate it (i.e. no need for a table) with this formula:

=6*LEFT(A2,1)+2*(68-CODE(UPPER(RIGHT(A2,1))))-1

where A2 is the cell you put your values in.

Hope this helps.

Pete


"Jax" wrote:

I am a bit rusy with LOOKUP and much better on IF statements. My problem is
I have 14 conditions to consider and IF will only go up to 7. Here's what I
want to say:
1c is equal to 7 points
1b " 9 "
1a 11
2c 13
2b 15
2a 17
3c 19
3b 21
3a 23
4c 25
4b 27
4a 29
5c 31
5b 33
Any one of the above number letter score could go in the required field so
they all have to be in the formula. If I type say 4b into a box I want the
number 25 to appear in the answer.

By the way, I think you would want 27 to appear if you type 4b into your cell.

Pete

"Pete_UK" wrote:

You can calculate it (i.e. no need for a table) with this formula:

=6*LEFT(A2,1)+2*(68-CODE(UPPER(RIGHT(A2,1))))-1

where A2 is the cell you put your values in.

Hope this helps.

Pete


"Jax" wrote:

I am a bit rusy with LOOKUP and much better on IF statements. My problem is
I have 14 conditions to consider and IF will only go up to 7. Here's what I
want to say:
1c is equal to 7 points
1b " 9 "
1a 11
2c 13
2b 15
2a 17
3c 19
3b 21
3a 23
4c 25
4b 27
4a 29
5c 31
5b 33
Any one of the above number letter score could go in the required field so
they all have to be in the formula. If I type say 4b into a box I want the
number 25 to appear in the answer.