Lookup not working correctly

[Dave]

Excel 2003

=IF(A2="","",LOOKUP(A2,{"A","A-","B+","B","B-","C+","C","C-","D+","D","D-","F";4,3.75,3.25,3,2.75,2.25,2,1.75,1.25,1,0.75,0}))

B gives me a value of 3.75 but should give me a 3.00
C gives me a value of 2.75 but should give me a 2.00

What am I doing wrong?

Thanks

Dave

 

[Bob Umlas]

LOOKUP must be in sequence. If you put the grades in a colum or row & sort
them you'll find the sequence is not as you have them, but
"A","A-","B","B-","B+",...
So either change the sequence to be the correct sort order, or use MATCH:
=INDEX({4,3.75,3.25,3,2.75,2.25,2,1.75,1.25,1,0.75,0},MATCH(A2,{"A";"A-";"B+";"B";"B-";"C+";"C";"C-";"D+";"D";"D-";"F"},0))

Bob Umlas
Excel MVP

 

[Bernard Liengme]

In addition to Bob's method, this also works

=IF(A2="","",HLOOKUP(A2,{"A","A-","B+","B","B-","C+","C","C-","D+","D","D-","F";4,3.75,3.25,3,2.75,2.25,2,1.75,1.25,1,0.75,0},
2, FALSE))

best wishes

 

[Dave]

Thank you both.

working great now

Dave