Lookup Formula

S

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?
 
P

Pecoflyer

SpencerMC;224950 said:
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
 
S

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)
 
S

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top