Return value if scores contain value form min and Max column

Question

Formula to 

If my score in column A is between the minimum and maximum marks in columns d and e, I want to put a formula in box 4 to determine my grade. For clarity, please see the attached photo.

Answer 1

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.