VLOOKUP to find a value that falls between a range?
 

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

VLOOKUP to find a value that falls between a range?
 

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" wrote:

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

VLOOKUP to find a value that falls between a range?
 

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" wrote:

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" wrote:

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

VLOOKUP to find a value that falls between a range?
 

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

"Dave" wrote:

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" wrote:

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" wrote:

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