Returing a value depending on hi-lo range

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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)


John Phillips said:
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
Click to expand...
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".
 
Hi John,

Another way:

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

Enter the score in C1.

HTH
Regards,
Howard

John Phillips said:
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
Click to expand...
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".
 
Back
Top