VLOOKUP to find a value that falls between a range?

[Dave]

Hi, How do you format a VLOOKUP to lookup something like a student's grade.
Next to each score I want a use VLOOKUP to return "A", "B", "C" etc.

Scores
--------
92
73
87
89
etc.


Lookup Table
---------------
100 90 A
89 80 B
79 70 C
69 60 D
59 50 F

 

[Mike H]

hI,

You build a lookup table like the one below and then use

=VLOOKUP(C1,A1:B6,2,TRUE)

Where C1 is the mark. Note I included an unclassified mark but you don't
actually need this.

0 U
50 F
60 D
70 C
80 B
90 A

Mike

 

[Dave]

Thanks but I do need to compare a value that falls between a range. I just
simplified my problem to try and better explain it and to hopefully apply any
solution to it.

 

[Mike H]

Dave,

Did you try my suggestion because that's exactly what it does!!

take any mark from 50 to 59 and my formula coupled with the lookup table
will give a grade of 'F'

I used TRUE in the vlookup formula so If TRUE or omitted, an exact or
approximate match is returned. If an exact match is not found, the next
largest value that is less than lookup_value is returned.

Mike