Need Help With and INDEX MATCH Formula!!!

Hello All,

I have two tables which I would like to parse through in order to eventually come up with 3 end result fields for each ID in Table 1: Start Date, End Date, and In CC.

I want:

• Start Date to be looked up from Table2 where the max value less than the ActDate from Table 2 where Table 1 ID = Table 2 ID
• End Date to be the min End Date from Table 2 Greater than Act Date from Table 2 where Table 1 ID = Table 2 ID
• In CC should then return whether or not based on the Act Date from Table 1, whether or not the Act Date from Table 1 occurred PRE, DURING, or POST the Date Range for the Start Date and End Date from Table 2 where Table 1 ID = Table 2 ID

A few Caveats:

• A record may not have any matching record in Table 2 for which Table 1 In CC should return Never in CC, and Start Date and End Date would be blank
• A record might have a Start Date and an End Date in Table 2 for which Table 1 would populate Start Date, End Date, and In CC as mentioned above
• A record might have a Start Date and no End Date in Table 2 for which Table 1 would populate Start Date, have End Date Blank, and In CC return DURING

As of right now I have a formula which successfully pulls the Start and End Dates for some, but in others returns essentially a fictitious Date for Start Date and End Date, and I am at a loss as for why. The fictitious date I get for the Start Date is 12/28/2015, and the fictitious date I get for End Date is 5/11/2016.

My formula for Start Date is:

Please Login or Register  to view this content.

My formula for End Date is:

Please Login or Register  to view this content.