Excel – Find what range a number belongs to


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:


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:


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

A simpler method is:


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

Related Question