VLOOKUP to find a value that falls between a range?

D

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
 
M

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
 
D

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.
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top