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?

SpencerMC;224950 Wrote:
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?

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) to
get an exact match


--
Pecoflyer

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

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=62360

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)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Pecoflyer" wrote:


SpencerMC;224950 Wrote:
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?

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) to
get an exact match


--
Pecoflyer

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

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=62360

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!

"Shane Devenshire" wrote:

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)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Pecoflyer" wrote:


SpencerMC;224950 Wrote:
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?

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) to
get an exact match


--
Pecoflyer

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

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=62360