Determine result based on age and score

[Strudel_Girl]

I would be so grateful for your help. I have been searching for days and am at a loss as to how to even find the answer.

I need to determine whether a group of Primary School children are Below, At or Above Level based upon their score and where that lies in relation to their age. I can either have it return a result such as Below, At or Above or I could use conditional formatting to present where the children sit. I have calculated their age as at the test date (and used MROUND to round to nearest 0.5) but what I need is a way for Excel to find the age in a table of results and then assess their score based on that age.

The following table is an example of the criteria (my ages go from 5-11yrs old):

[TABLE="class: grid, width: 250, align: left"]
<tbody>[TR]
[TD]Age[/TD]
[TD]Normal Range[/TD]
[TD]Critical[/TD]
[/TR]
[TR]
[TD]6.5[/TD]
[TD]3 to 7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]5 to 11[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]7.5[/TD]
[TD]7 to 15[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]







In my lay thinking, I am looking for a way to say (referencing below table), if their age at the time of the test is 6.5yrs (A2) and their score is between 3 (B2) and 7 (C2), they are either Normal (or conditionally formatted Yellow) or if they are 7 years old (A3) at the time of the test and receive 3 (D3) they are Critical (or conditionally formatted Red).

[TABLE="class: grid, width: 250, align: left"]
<tbody>[TR]
[TD]Age[/TD]
[TD]Norm. Low[/TD]
[TD]Norm. High[/TD]
[TD]Critical[/TD]
[/TR]
[TR]
[TD]6.5[/TD]
[TD]3[/TD]
[TD]7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]5[/TD]
[TD]11[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]7.5[/TD]
[TD]7[/TD]
[TD]15[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]








I'm not sure if it would be a 2 level VLOOKUP or what?? I have researched that though and it doesn't seem to fit, nor does Index & Match. Would be super grateful for any advice, a suggestion of a video to watch or even just what terminology I should search for.

Thanks in advance for your help!