VLookup Round Up Hello I\'m using a lookup table to convert points to grades. Th

Question

VLookup Round Up

Hello

I'm using a lookup table to convert points to grades. The points are a result of weighted calculation of 4 assessments which is retuning decimals.

But there are no decimals in the lookup table. So I want it to show the closest match.

Part of the Lookup table

the formula I'm using is =VLOOKUP(L2,D2:E25,2,TRUE)

I want the results for 12.9 to show 5C seeing as it's closest but it's showing 4A.

In other words anything that is over .5 to show the higher grade.

I've gotten a bit muddled up with which row needs to be sorted in the Lookup table and I'm wondering whether this is the problem.

I tried using ROUND in the formula but that didn't work....

Thanks

Explanation / Answer

Hi,,,,

This ARRAY formula will return the closest match. See below for how to enter an array formula...

=INDEX(E2:E25,MATCH(MIN(ABS(D2:D25-L2)),ABS(D2:D25-L2),0))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.

EDIT....I just looked at this again and your question isn't clear. In the question header you say VLookup Round Up but in the body of the question you say So I want it to show the closest match. The formula I provided returns the closest.

Related Questions

Navigate

• Browse (All)
• Browse V
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.