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?

 

[Pecoflyer]

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 th
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 an
you
can only have up to seven! Any ideas?

The mix of letters and numbers is irrelevant, it's a string.
Jut add FALSE in your formula like =VLOOKUP(A1,$AB$7:$AC24,2,false) t
get an exact matc

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste

 

[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!