Find Next in Series

[VenturSum]

Team,

I have a challenging Find problem, shown in the table below

I need to find the Assignment date that is >= to Contract date,
and based on matching employee IDs

I can do this on an Index(Match()) function to find the 1st date
But I don't know how to find the next or the third in the series.

This looks like an array formula, but I don't know how to set it up.
I prefer a formula, though I could do this with a programmed function.

Empl ID	Contract Date	Assign Start	--	--	Empl ID	Assign Start
123456	01/02/2015	02/01/2009			111111	11/15/2018
123456	01/02/2016	02/01/2009			112233	03/01/2017
123456	06/04/2017	05/01/2017			123456	02/01/2009
123456	02/05/2019	05/01/2017			123456	05/01/2017
123456	05/05/2020	08/07/2019			123456	08/07/2019
123456	07/01/2020	08/07/2019			334455	06/01/2014

Any help?

John,
In Annapolis

 

[VenturSum]

Rewording the problem.

I have a challenging Find problem

Below are 2 tables, Tables. The headers are in A - C and F - G

Normally I would use Index Match to find the Assign Start (col G) matched to Empl ID (cols A and F)
C2=INDEX(G:G, MATCH(A2,F:F,0)

Now I need the Row # or Assign Start (col G)
Where Empl ID in A = F , AND
Where Assign Start (col G) is >= Contract Date (col B)

A

B

C

D

E

F

G

Empl ID	Contract Date	Assign Start	--	--	Empl ID	Assign Start
123456	01/02/2015	02/01/2009			111111	11/15/2018
123456	01/02/2016	02/01/2009			112233	03/01/2017
123456	06/04/2017	05/01/2017			123456	02/01/2009
123456	02/05/2019	05/01/2017			123456	05/01/2017
123456	05/05/2020	08/07/2019			123456	08/07/2019
123456	07/01/2020	08/07/2019			334455	06/01/2014

I think this can be done using an array formula, but I need help setting it up.
I prefer a formula, though I could do this with a programmed function.

Any help?

John,
In Annapolis

 

[VenturSum]

I was able to figure this out. I had to keep simplifying the problem until it became simple and clear.
I used an array formula.
{=INDEX($G:$G,MATCH(1,IF($A6=$F:$F,IF($B>=$G:$G,1))),0)}

I added some error checking to get
{=IFERROR(INDEX($G:$G,MATCH(1,IF($A6=$F:$F,IF($B>=$G:$G,1))),0),"N/A")}

I hope this helps someone.
John,
In Annapolis, MD

 

[Peter_SSs]

I was able to figure this out. I had to keep simplifying the problem until it became simple and clear.
I used an array formula.
{=INDEX($G:$G,MATCH(1,IF($A6=$F:$F,IF($B>=$G:$G,1))),0)}

I added some error checking to get
{=IFERROR(INDEX($G:$G,MATCH(1,IF($A6=$F:$F,IF($B>=$G:$G,1))),0),"N/A")}

I hope this helps someone.
John,
In Annapolis, MD

Neither of your formulas in this post marked as the solution are valid formulas. Can you post the full corrected formula if you would like your solution to be helpful to others?
It would also be helpful to know which cell the posted formulas are from. Have you considered using XL2BB?

Also, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
Depending on your version, there may even be a simpler solution that does not require the array formula entry.