Lookup Formula

[SpencerMC]

I need to convert an alphanumeric value into a numerical score, according to
the following list:
P 3
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
5a 35

Something like =VLOOKUP(A1,$AB$7:$AC24,2) where AB7:AC24 is the location of
the lookup table on the worksheet, A1 being where I type the value, doesn't
work!

This is due (I believe) to the mix of letters and numbers I need to be
looked up. How can I do it? Nested IF statements are too confusing and you
can only have up to seven! Any ideas?

 

[Shane Devenshire]

Hi,

At the very least you will need to add a comma after the 3rd argument:

=VLOOKUP(A1,$AB$7:$AC$24,2,)

This is equivalent to 0 or FALSE in the match type argument and means you
are looking for an exact match.

If you sort you lookup table on the first column you can use

=LOOKUP(D1,AB$7:AC$24)

 

[Sean Timmons]

Actually, no.. the issue is, the range is in the last column. It need to be
in the first column. Merely take column AC and make that AB and make AB AC.
Done!