Range lookups with overlapping entries and extra check

Hello,

Using conditional formatting I would like to highlight days in a calender. I have done so already using the rule below.
To simplify the adding of people to the calendar I would like to extend this conditional formatting but I have no idea how.

So the conditional format should give true if the date in the cell is in the range given by VacationStart and VacationEnd AND the name in that row should be matched.

With INDEX and MATCH one can search through ranges but it is dependend on the sort order of the row and in the example below one has a problem with August 21.

Has anyone a solution for this?

Regards,

Marcel

A B C D

1 Vacation
2 DATE DATE NAME DESCRIPTION
3 31-Jul-2010 21-Aug-2010 Jeremy Summer vacation
4 21-Aug-2010 21-Aug-2010 Sunny Free day
5 21-Aug-2010 21-Aug-2010 Bart Free day
6 27-Dec-2010 31-Dec-2010 Jeremy Christmas vacation
7 29-Dec-2010 31-Dec-2010 Sunny Christmas vacation


Named Ranges:

VacationStart: A3-A99
VacationEnd : B3-B99
Names : C3-C99

=IF(ISREF(INDEX(VacationEnd,MATCH(E8,VacationStart, 1),1)),IF(E8<=INDEX(VacationEnd,MATCH(E8,VacationStart, 1),1), TRUE, FALSE), FALSE)

Hello,

As requested an example worksheet.

On the first Tab a Calender that gets coloured with conditional formulas based on values on the tables on the other sheets.

Per person there is one sheet with their vacation / part-time / home working days.

What I want is to combine all the person vacation tables into one table so I only have one conditional formula. Instead of tables / Named ranges and conditional formulas for each person.
In that way adding/removing a person is easy instead of difficult.

I only added a Name column in the person 'Marcel' tab to test with.

Regards,

Marcel