I hope I can explain this clearly.
Column A is Student Name. Column B is group (either group 1, 2, 3 or 4). Column C is test mark out of 100.
I have a lookup for each group. But each group has different parameters. For example Group 1: 0 to 40 = Below Target. 41 to 65 = On Target. 66...