Forum Discussion
Excel "LOOKUP" formula help
I'm using the following formula to lookup my letter grade and and convert that into grade points.
=LOOKUP(E27,{"A","A-","B+","B","B-","C+","C","C-","D+","D","D-","F"},{4,3.67,3.33,3,2.67,2.33,2,1.67,1.33,1,0.67,0})
But, I get the following results when using the Formula
A = 4.00
A- = 3.67
B+ = 3.33
B = 3.67 (Should be 3.0)
B- = 3.67 (Should be 3.33)
C+ = 2.33
C = 2.67 (Should be 2.0)
C- = 2.67 (Should be 1.67)
D+ = 1.33
D = 1.67 (Should be 1.0)
D- 1.67 (Should be 0.67)
F = 0
Can anyone tell me why I'm seeing incorrect results?
3 Replies
Another variant
=IFNA(CHOOSE(MATCH(E27,{"A","A-","B+","B","B-","C+","C","C-","D+","D","D-","F"},0), 4,3.67,3.33,3,2.67,2.33,2,1.67,1.33,1,0.67,0),"")Louis,
LOOKUP() requires the lookup vector in ascending order.
And ascending order ist not:
A A- B+ B B- C+ C C- D+ D D- F but:
A A- B B- B+ C C- C+ D D- D+ F Louis,
The LOOKUP function isn't suitable in this case because it has some limitations, it doesn't support the exact match, and it requires the lookup_vector to be in ascending order!
For more info please check this https://support.office.com/en-us/article/LOOKUP-function-446D94AF-663B-451D-8251-369D5E3864CB.
I recommend you to use VLOOKUP function instead.
Therefore, replace your formula with this:
=VLOOKUP(E27,{"A",4;"A-",3.67;"B+",3.33;"B",3;"B-",2.67;"C+",2.33;"C",2;"C-",1.67;"D+",1.33;"D",1;"D-",0.67;"F",0},2,0)
