# Excel – Find what range a number belongs to

excel

Ive written a function to calculate what MARK a student gets, based on a scoring table.
Why does my function work only for A mark?

This what the excel sheet looks like

``````COLUMN:    A      B      C
Student  SCORE  MARK
2    asgfd    89     FALSE

3     A       90    100
4     B       81    89
5     C       71    80
6     D       61    70
7     E        56   60
8     Fx       0    55
``````

This is the function:

``````{=IF(B1>=\$B\$3:\$B\$8,IF(B1<=\$C\$3:\$C\$8,\$A\$3:\$A\$8))}
``````

I'm using {} brackets for array functions. (CTRL SHIFT ENTER)

Thank you

#### Best Solution

You're on the right track but your formula is returning an array not a single value. Wrapping the result in LOOKUP should give the desired result:

``````=LOOKUP("Z",IF(B1>=\$B\$3:\$B\$8,IF(B1<=\$C\$3:\$C\$8,\$A\$3:\$A\$8))
``````

This returns the last matching grade since "Z" is larger than any other text value in the range.

A simpler method is:

``````=LOOKUP(-B1,-C\$3:C\$8,A\$3:A\$8)
``````

The negative signs are needed so that the lookup values are in ascending order.