Multiple Criteria Search Function

[dougiek7]

Hi,

I have a situation that I haven't been able to figure out myself and searching for an answer wasn't really helping (unless I just wasn't recognizing solutions that would actually help) so I thought I might post here and see if anyone could help.

Essentially I'm trying to have a formula that returns the oldest date for an employee as long as the line item has a certain value in it.

Sample is below in the photo.

PlacementStatus <tbody> </tbody>	StartDate <tbody> </tbody>	Actual End Date <tbody> </tbody>	OrdercandidateID <tbody> </tbody>	CandidateName <tbody> </tbody>	Termination Reason <tbody> </tbody>	Rebook Exception <tbody> </tbody>	Start Date (Exception) <tbody> </tbody>		Correct Dates <tbody> </tbody>
Active <tbody> </tbody>	11/21/2016 <tbody> </tbody>		3066678 <tbody> </tbody>	Christine Pope <tbody> </tbody>		Y <tbody> </tbody>	11/9/2015 <tbody> </tbody>		6/22/2016 <tbody> </tbody>
Inactive <tbody> </tbody>	6/22/2016 <tbody> </tbody>	11/20/2016 <tbody> </tbody>	2941210 <tbody> </tbody>	Christine Pope <tbody> </tbody>	Exclude - Correction <tbody> </tbody>	N <tbody> </tbody>	6/22/2016 <tbody> </tbody>		6/22/2016 <tbody> </tbody>
Inactive <tbody> </tbody>	3/15/2016 <tbody> </tbody>	4/16/2016 <tbody> </tbody>	2864532 <tbody> </tbody>	Christine Pope <tbody> </tbody>	Client Reasons <tbody> </tbody>	Y <tbody> </tbody>	11/9/2015 <tbody> </tbody>		11/9/2015 <tbody> </tbody>
Inactive <tbody> </tbody>	11/9/2015 <tbody> </tbody>	3/15/2016 <tbody> </tbody>	2766735 <tbody> </tbody>	Christine Pope <tbody> </tbody>	Exclude - Duplicate <tbody> </tbody>	N <tbody> </tbody>	11/9/2015 <tbody> </tbody>		11/9/2015 <tbody> </tbody>

<tbody>
</tbody>

Currently, the Start Date (Exception) is the column being used to determine the correct dates with the following formula:
=IF([@[Rebook Exception]]="Y",INDEX($CY:$CY,MATCH([@CandidateName],$Q:$Q,1)),[@[Placement Start Date]])

This works, except it delivers the oldest date for all listings for the candidate. It should only return the date that is for the first Rebook Exception of "N" which basically is the start of a new date range (placement) for the candidate.

Is there a formula that can return the Start Date of the first "N" row below it? I provided a column on the right with the dates that the formula should return.

I wasn't able to figure it out - or if it's even possible.

Thanks!

 

[dougiek7]

Note: the formula mentions "Placement Start Date" but that is just another column on the raw data that has the same value as StartDate.

 

[dougiek7]

Bump