So based on my comments above, here are the screenshots where the formula is working. Since I am using MS365 therefore I don't need to hit CTRL+SHIFT+ENTER when exiting edit mode for the second alternative, but for those using earlier versions needs to hit.

• Formula used in cell B4

=XLOOKUP(1,(A4>=Min_Marks)*(A4<=Max_Marks),Grade,"")

Where: Min_Marks, Max_Marks & Grade are defined names for the ranges, --> =$D$4:$D$13, =$E$4:$E$13, =$F$4:$F$13 you can also use without using defined names.

=XLOOKUP(1,(A4>=D$4:D$13)*(A4<=E$4:E$13),$F$4:$F$13,"")

Alternatively if you don't have access to XLOOKUP() then use INDEX() with MATCH()

• Formula used in cell B4

=INDEX(Grade,MATCH(1,(A4>=Min_Marks)*(A4<=Max_Marks),0))

Or, without defined names:

=INDEX(F$4:F$13,MATCH(1,(A4>=D$4:D$13)*(A4<=E$4:E$13),0))

Note: Remember to hit CTRL+SHIFT+ENTER while exiting the edit mode for the second formula.