Lookup query

[Guest]

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.

 

[Guest]

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.

 

[Peo Sjoblom]

=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

 

[Guest]

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

 

[Guest]

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

Pete