Excel validation formula with date ranges

[Jonah21]

Excel 2010 question: I have a table that I'm trying to write a validation formula (using Excel formula and avoid VBA if possible)for. In the example table below - the "Assignment" given to the unique combination of "Account" & "Sub-account" for an over-lapping Start and End Date must be the same. A conflict error is thrown if that rule is violated. So, "2" is not a conflict because the date range is within "1". "3" is not a conflict because combination of "Account" AND "Sub-Account" is different. "4" and "5" are conflicts because a different "Assignment" was given for "Account" and "Sub-Account" for a over-lapping date range. "7" is a conflict because a different "Assignment" is given for the same "Account" & "Sub-Account" and over-lapping Date range.

NO. Account Sub-account Start Date End Date Assignment CONFLICT?
1 PET DOG 1/1/2012 1/31/2012 HOUSE1
2 PET DOG 1/15/2012 1/17/2012 HOUSE1 No
3 PET BIRD 1/1/2012 1/31/2012 HOUSE1 No
4 PET DOG 1/15/2012 2/15/2012 HOUSE2 YES
5 PET DOG 1/1/2012 1/31/2012 HOUSE2 YES
6 PET CAT 2/15/2012 2/29/2012 HOUSE1 No
7 PET CAT 2/15/2012 3/15/2012 HOUSE3 YES

 

[Jonah21]

I found the answer for this:

It is an array formula that first check for the conflicting assignment given to the Account and Sub-account, and then checks if there is a date overlap.

=IF(AND(F2<>INDEX($F$1:$F$8,MATCH(B2&C2,$B$1:$B$8&$C$1:$C$8,0)),D2<=INDEX($E$1:$E$8,MATCH(B2&C2,$B$1:$B$8&$C$1:$C$8,0)),E2>=INDEX($E$1:$E$8,MATCH(B2&C2,$B$1:$B$8&$C$1:$C$8,0))),"Conflict","")