vlookup

[S Hendry]

i am creating a workbook to track assessment details for my classes. As part
of this workbook i am trying to use a vlookup table to convert an assessment
level to a numerical value e.g. 4B converts to 4.33.

My lookup function works fine apart from any level that has the letter A in
it.

My lookup table is as follows

3 3
4C 4
4B 4.33
4A 4.67
5C 5
5B 5.33
5A 5.67
6C 6
6B 6.33
6A 6.67
7C 7
7B 7.33

4A returns an N/A error
5A returns the value of 4A
6A returns the value of 5A

Please can anyone help me resolve this issue

kind regards

Si

 

[Bernard Liengme]

It is a pity you did not show us your formula. My guess would be you did not
enter the fourth argument.

I used =VLOOKUP(A1,K1:L12,2,0) with your data in K1:L12 (ie 3 in K1 and 7.33
in L12)
If you use a formula like this in many cells, it is adviasable to use
absolut reference for the table
=VLOOKUP(A1,$K$1:$L$12,2,0)

I also used =LOOKUP(A1,{"3";"4c";"4b"},{3; 4; 4.33}) You can see I was too
lazy to complete this but the idea should be clear.

best wishes
Bernard Liengme
people.stfx.ca/bliengme

 

[S Hendry]

Thanx Joel, problem fixed

Lookup tables havve to be in alpha-numberic order

try

 

[Joel]

Lookup tables havve to be in alpha-numberic order

try

3 3
4A 4.67
4B 4.33
4C 4
5A 5.67
5B 5.33
5C 5
6A 6.67
6B 6.33
6C 6
7B 7.33
7C 7