# Find Next in Series

#### VenturSum

##### Board Regular
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

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### VenturSum

##### Board Regular
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

##### Board Regular
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

##### MrExcel MVP, Moderator
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.

Replies
5
Views
125
Replies
6
Views
221
Replies
8
Views
179
Replies
4
Views
139
Replies
4
Views
80

1,129,795
Messages
5,638,382
Members
417,025
Latest member
MusterDuster

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back