Doing lookups with complex data or two values

[MikeCollins999]

I was desperate and determined that I would work this out for myself but I hold my hands up and say “I can’t work this out”.

I’m trying to help a friend automate the process of placing primary school children in reading bands. The school works out the child’s reading age (rightly or wrongly) by taking their age from the start of the year and counting the months so a student who is 4 years and 3 months old would have a reading age of 4:3. A student who is 4 years and 11 months old would have a reading age of 4:11.

Once the child’s expected reading age is determined, their actually reading age is assessed. Again this is entered in the format Y:M.

I now need to look up the Band (colour) that the child falls into based on the actual reading age, where the bands look like this:

[$D]  [$E]
< 4  LILIC
4:0 – 4:3  PINK
4:4 – 4:6  RED
4:7 – 4:.12   YELLOW
5:0 – 5:5  BLUE

I can split these values so they represent the upper-limit or split them so the lower limit is in one column and the upper is in the next column. Essentially, the structure of the band table is flexible – all I want to do is return the colour – respecting the fact that 4:12 is greater than 4:7.

I have attached an annotated sample - any help or pointers would be ace.

Many thanks,

Mike

 

[Hui]

K2: =IFERROR(INDEX($U$4:$U$21,MATCH(N2&":"&O2,$T$4:$T$21,1)),"")
Copy down

 

[Mike Collins]

Hi Hui,

Many thanks for the reply - unfortunately the formula(s) provided do not return the expected results. In fact, I had already tried a similar thing myself before posting the question.

I have attached an updated copy of the sample showing a) your suggested formula, b) what the expect result should be, and c) how I think it needs to be approached (Although I do not know how to implement my suggesting).

Any further help or advice would be gratefully received.

Many thanks

Mike