Date Between Multiple Non Contiguous Date Ranges On Another Sheet?

I have been looking at a post number 170404 which NBVC answered with an example grid. This was while I was trying to bring searched data through to another sheet and this is just what I was looking for as a brilliant start,

I have attached my very similar example in my scenario. I have also got NBVC's example on the first two sheets of this as reference. In addition I am also bringing through from-to date ranges which I then need to be applied to a Booking Sheet so I can see that these dates are booked for this person, and ideally where they are going and what they need to do there by colour.

I tried, just as a test in this example the following. Please note that this only had the three ranges as I was testing an example coordinator which returned three date ranges. It could be that the example returns 20 or 30 ranges that need to be applied to the rather primative Booking Sheet.

=IF(OR(AND(C10>='Coordinators list'!F5,'Coordinators list'!L5<=C10),(AND(C10>='Coordinators list'!F6,'Coordinators list'!L6<=C10)),(AND(C10>='Coordinators list'!F7,'Coordinators list'!L7<=C10))),"yes","no")

It seemed to give the right answer initially but trying different dates it gave the wrong result. It seemed to be checking one of the ranges and not all of them. As mentioned above this was just an example to see what I could get working but I don't think that this would be the ideal way of doing this as it would need to check 30-40 maybe more ranges depending on what comes back from the coordinator search.

The aim would be to choose a co-ordinator on the Site Planner sheet and ideally return the city rather than the yes/no in the Booking Sheet for that date and use conditional formatting so that if the type from the Site Planner Sheet was paint it would fill in one colour, clean in another, etc.

Was hopefully trying to get this done without using any VB as I am trying to resolve this for my Wife she isn't very tech savvy and and I need to be able to explain the formulas!

I'm sure you will see from my sheet I have given this a good go but I am a bit unsure on what some of the formulas on NBVC sheet are doing, and that this is my first attempt at doing anything like this. I have also tried using lookup and match but I'm still having issues that they either aren't always in date order and it seems to return no if it doesn't match the first range. The other main issue that I have seen is that I need to check whatever ranges the selection comes back with rather than adding each one to search through.

I hope this becomes a bit clearer on the sheet I have attached,