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
    1    adsf     90     A
    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.

Related Question