Question

Microsoft Excel Question.

I'm having trouble using the vlookup function, I have calculated a final numerical grade for a hypothetical course, and and trying to use a set of numerical grades with their corresponding letter grades to get a vlookup function to return the letter grade from the numerical grade.

Projects Classwork Teamwork/Integrity 100 100 A+ 98 A+ 97 A 93 A 92 A 90 A- 89 B+ 88 B+ 87 B 83 B 82 B 80 B 79 C+ 78 C+ 77 C 73 C 72 C 70 C 69 D 60 D 59 F Assignment # Homework Quiz Test 100 105 98 87 86 75 90 98 10084 8787 94 65 96 79 100 92 97 87 92 89 100 95 95 95 75 14 15 16 95 100 100 21 95 100 100 89 88.5 92.52173913 35.6 13.275 4.626086957 Average Partial Grade Final Grade 89.90108696 94.8 85.9 14.22 17.18 Final Letter Grade TRUE)

However the function for some reason only returns the lowest value out of the set grades, not the closest match.

Answer 1

For the getting the closest match (approximate match =TRUE), the lookup data needs to be sorted in the ascending order.

The following is the sheet

Get this result

this is assuming that 89-90 you want to award B+

89.901 is in between 89-90.

Of course you can always round 89.901 to the closest integer in the vlookup

=VLOOKUP(round(C29,0),J3:K23,2,TRUE)

to get A-