problem with getting a random output from range of cells when a criteria is meet

Hi

i am wanting to write a formula for school reports. I want to input a result into column 1, which will then depending on the result, choose a random comment from a range of cells in column B
and display that in column C


So "5a", entered into Column A, chooses a comment from say Cell 1 -8 of column B
If "5p" would choose a random comment from Cell 9-16 of column B

any help would be greatly appreciated

Try this:

=INDEX($B:$B,IF(A1="5a",RANDBETWEEN(1,8),IF(A1="5p",RANDBETWEEN(9,16),"")))

Awesome thank you so much

Is there anyway to alter the formula so that once a random value is assigned to a cell it does not change every time i add another grade? So if "5a" is selected in A1 the value in C1 does not change when I enter a value for A2?

You can use VBA solution or copy and paste value

This is the formula I am using to generate a random comment from a range of cells. Is there anything I could add to stop it from changing the comments every time I enter a new students score?

=INDEX($I:$I,IF(D17="a",RANDBETWEEN(2,4),IF(D17="b",RANDBETWEEN(5,7),IF(D17="c",RANDBETWEEN(9,11),IF(D17="D",RANDBETWEEN(14,17),"""")))))