Lookup not working correctly

  • Thread starter Thread starter Dave
  • Start date Start date
D

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
 
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
 
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
 
Back
Top