# Return single number value based on range

#### JGrieve

##### New Member
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.

 Grade Peak Mean 1 0 0 2 2.1 0.51 3 15.1 0.91 4 30.1 1.71 5 50 3

<tbody>
</tbody>

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### Special-K99

##### Well-known Member

=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

##### New Member

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

 Score Grade result Grade Peak Mean 0 1 1 0 0 2339.35 5 2 2.1 0.51 11 2 3 15.1 0.91 31 4 4 30.1 1.71 999 5 5 50 3 1.9 1 2.25 2

<colgroup><col><col><col span="4"></colgroup><tbody>
</tbody>

#### Special-K99

##### Well-known Member
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

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.

1,102,872
Messages
5,489,423
Members
407,687
Latest member
NeoSez

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...