Find Next in Series

VenturSum

Board Regular
Joined
May 23, 2010
Messages
137
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 IDContract DateAssign Start----Empl IDAssign Start
12345601/02/201502/01/200911111111/15/2018
12345601/02/201602/01/200911223303/01/2017
12345606/04/201705/01/201712345602/01/2009
12345602/05/201905/01/201712345605/01/2017
12345605/05/202008/07/201912345608/07/2019
12345607/01/202008/07/201933445506/01/2014


Any help?

John,
In Annapolis
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

VenturSum

Board Regular
Joined
May 23, 2010
Messages
137
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 IDContract DateAssign Start----Empl IDAssign Start
12345601/02/201502/01/200911111111/15/2018
12345601/02/201602/01/200911223303/01/2017
12345606/04/201705/01/201712345602/01/2009
12345602/05/201905/01/201712345605/01/2017
12345605/05/202008/07/201912345608/07/2019
12345607/01/202008/07/201933445506/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
Joined
May 23, 2010
Messages
137
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
 
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,548
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,037
Messages
5,639,678
Members
417,104
Latest member
Nelsini

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top