Returing a value depending on hi-lo range

[Guest]

Hi

Consider the following table showing scores if a value falls into certain grade "bands"...

High low Score
100 75 A
74 50 B
49 25 C
24 0 D

I have a series of values (eg, "52") that I'd like to place in the appropriate range (ie above the low value but below the high value) and return the relevant value from the "Score" column. So if my value was "52", it would return "B".

I can produce a long formula using nested IF statements, but I'd like a neater solution. Any idea's ??

Thanks
John P

 

[Alan]

With the low values and scores arranged like this in E12:F15 and your value
"52" in H14

0 D
25 C
50 B
75 A

=VLOOKUP(H14,E12:F15,2)

Hi

Consider the following table showing scores if a value falls into certain grade "bands"...

High low Score
100 75 A
74 50 B
49 25 C
24 0 D

I have a series of values (eg, "52") that I'd like to place in the

appropriate range (ie above the low value but below the high value) and
return the relevant value from the "Score" column. So if my value was "52",
it would return "B".

 

[L. Howard Kittle]

Hi John,

Another way:

=LOOKUP(C1,{0,25,50,75},{"D","C","B","A"})

Enter the score in C1.

HTH
Regards,
Howard

Hi

Consider the following table showing scores if a value falls into certain grade "bands"...

High low Score
100 75 A
74 50 B
49 25 C
24 0 D

I have a series of values (eg, "52") that I'd like to place in the

appropriate range (ie above the low value but below the high value) and
return the relevant value from the "Score" column. So if my value was "52",
it would return "B".