Return single number value based on range

[JGrieve]

Heya, im barely great at Excel, and im struggling to develop a formula.

I have one sheet that has a Numeric result in ONE cell - S2
The number is ANY value, with ANY decimal point value

I need to put a formula in another cell that defines a grade 1-5 based on its value S2's value.
*Each Peak / Mean score between the values below, get assigned the corresponding grade on the far left*

Im sure its an =IF(AND( kind of formula, but cannot get it to work.
Im looking for a single formula that will check all 5 grades parameters, and return the single grade number.


[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"]Grade[/TD]
[TD="width: 64"]Peak[/TD]
[TD="width: 64"]Mean[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2.1[/TD]
[TD="align: right"]0.51[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]15.1[/TD]
[TD="align: right"]0.91[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]30.1[/TD]
[TD="align: right"]1.71[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

With your example table in Sheet2 and Grade in A1

=INDEX(Sheet2!A$2:A$5,MATCH(S2,Sheet2!B$2:B$5,0),1)

or
=LOOKUP(S2,{0,2.1,15.1,30.1,50},{1,2,3,4,5})

 

[JGrieve]

With your example table in Sheet2 and Grade in A1

=INDEX(Sheet2!A$2:A$5,MATCH(S2,Sheet2!B$2:B$5,0),1)

or
=LOOKUP(S2,{0,2.1,15.1,30.1,50},{1,2,3,4,5})

I think the bottom one appears to work,
Using the lookup on the i did some sample scores in, and it appears to be returning the correct result.

Could you explain how the lookup works,
Like, How is that formula knowing that when the score fits between two of those numbers to return the correct grade.

Dont get me wrong, i am VERY grateful for your help, and thank you very much,
I would just like to learn how its doing it - cause at this stage, its quite close to Magic in my mind hahaha.

[TABLE="width: 403"]
<colgroup><col><col><col span="4"></colgroup><tbody>[TR]
[TD]Score[/TD]
[TD]Grade result[/TD]
[TD][/TD]
[TD]Grade[/TD]
[TD]Peak[/TD]
[TD]Mean[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2339.35[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2.1[/TD]
[TD="align: right"]0.51[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]15.1[/TD]
[TD="align: right"]0.91[/TD]
[/TR]
[TR]
[TD="align: right"]31[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]30.1[/TD]
[TD="align: right"]1.71[/TD]
[/TR]
[TR]
[TD="align: right"]999[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1.9[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2.25[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

They call this use of { } brackets an inline array and you're right, the LOOKUP matches S2 against the first set of brackets
{0,2.1,15,1,30.1,50} and this are regarded as ranges 0-2, 2.1-14.9999 etc
The number returned is the appropriate number from the second set of brackets
So if you had

LOOKUP(S2,{10,20,30,40,50},{6,7,8,9,10})
and S2 was 43 the result would be 9 because 43 lies between 40 and 50 the lowest of the two positions is returned, ie the 4th number. So the 4th number in the 2nd set is returned, ie 9.
There's not a lot of references to inline arrays on the net, I've picked up most of this from the forums.

Drawback with this approach is if the number ranges change then you need to amend the formula.
Also you need to cater for the minimum or you'll get an error.
For example if S2 was 5 in the above example, you would get an error cos the minimum number in the first set opf brackets is 10 or higher to match.