formula to match a date in one column with a date in another

[mojo707]

I'm trying to find a way (I've looked at Index, match, choose, find, search, substitute and am investigating solver) to match from a list of possible project end dates (demobilization dates) the date closest to each employees contract expiry date. The difficulty is that there will be a finite number of demobilization dates and once one is committed (i.e. matched to an employees contract expiry date, it will be unavailable for further matching). The sample worksheet below shows the desired output from the formula that I am seeking for Column E. In the sample you will see that for the Welder, the Desired Demob Date remains unchanged since a Welder cannot be swapped out for an Electrician whereas the Desired Demob Date for Electricians can change and an Electrician can be moved from one team to another.

A	B	C	D	E
Employee ID <tbody> </tbody>	Worker Type <tbody> </tbody>	Contract Expiry Date <tbody> </tbody>	Current Demob Date <tbody> </tbody>	Desired Demob Date <tbody> </tbody>
1	Electrician	08/31/19 <tbody> </tbody>	05/31/20 <tbody> </tbody>	08/31/19 <tbody> </tbody>
2	Electrician	07/31/19 <tbody> </tbody>	08/31/19 <tbody> </tbody>	07/31/19 <tbody> </tbody>
3	Electrician	06/30/19 <tbody> </tbody>	07/31/19 <tbody> </tbody>	05/31/20 <tbody> </tbody>
4	Welder	05/31/20 <tbody> </tbody>	06/30/20 <tbody> </tbody>	06/30/20 <tbody> </tbody>

<tbody>
</tbody>

 

[Marcelo Branco]

Maybe...

E2 copied down
=AGGREGATE(15,6,D$2:D$5/((B$2:B$5=B2)*(D$2:D$5>=C2)*ISNA(MATCH(D$2:D$5,E$1:E1,0))),1)

Hope this helps

M.

 

[mojo707]

Thanks M for responding, however I couldn't get your formula to work. I think perhaps because I may not have been clear enough in my description. I can't find a way to edit my post now so I will try to explain... Column E is not data but rather it is what my expectation of the solution would provide. I will try to upload the sample worksheet for you

 

[mojo707]

	A	B	C	D	E
1	Employee ID	Worker Type	Contract Expiry Date	Current Demob Date	Desired Demob Date
2	1234	Electrician	08/31/19	05/31/20	08/31/19
3	1235	Electrician	07/31/19	08/31/19	07/31/19
4	1236	Electrician	06/30/19	07/31/19	05/31/20
5	1237	Welder	05/31/20	06/30/20	06/30/20

<tbody>
</tbody>

Note that column E is not data but to explain what the expected output of the solution would provide

 

[Marcelo Branco]

Note that column E is not data but to explain what the expected output of the solution would provide

Yes, I fully understood that column E only shows the desired results.
Have you tested the formula? It worked perfectly for me

	A​	B​	C​	D​	E​
1​	Employee ID​	Worker Type​	Contract Expiry Date​	Current Demob Date​	Desired Demob Date​
2​	1​	Electrician​	08/31/2019​	05/31/2020​	08/31/2019​
3​	2​	Electrician​	07/31/2019​	08/31/2019​	07/31/2019​
4​	3​	Electrician​	06/30/2019​	07/31/2019​	05/31/2020​
5​	4​	Welder​	05/31/2020​	06/30/2020​	06/30/2020​

<tbody>
</tbody>

Formula in E2 copied down (same as my previous post)
=AGGREGATE(15,6,D$2:D$5/((B$2:B$5=B2)*(D$2:D$5>=C2)*ISNA(MATCH(D$2:D$5,E$1:E1,0))),1)

Questions
What Excel version are you using?
What you mean by "I couldn't get your formula to work."? An error, wrong results?

M.

 

[mojo707]

Yes, I fully understood that column E only shows the desired results.
Have you tested the formula? It worked perfectly for me

	A​	B​	C​	D​	E​
1​	Employee ID​	Worker Type​	Contract Expiry Date​	Current Demob Date​	Desired Demob Date​
2​	1​	Electrician​	08/31/2019​	05/31/2020​	08/31/2019​
3​	2​	Electrician​	07/31/2019​	08/31/2019​	07/31/2019​
4​	3​	Electrician​	06/30/2019​	07/31/2019​	05/31/2020​
5​	4​	Welder​	05/31/2020​	06/30/2020​	06/30/2020​

<tbody>
</tbody>

Formula in E2 copied down (same as my previous post)
=AGGREGATE(15,6,D$2:D$5/((B$2:B$5=B2)*(D$2:D$5>=C2)*ISNA(MATCH(D$2:D$5,E$1:E1,0))),1)

Questions
What Excel version are you using?
What you mean by "I couldn't get your formula to work."? An error, wrong results?

M.

I pasted the formula into E2 and copied down.
When I say it didn't work it is because I am getting a #NAME ? error
I'm running Excel 2007 but will try this in a few hours on Office 365 when I get to work.

***Update: I've just tried it on my work laptop and it works!
It must have been the older version of Excel. I will test it out further with some more data
Thanks very much Marcelo!

 

[Marcelo Branco]

The AGGREGATE function is not available in Excel 2007, so try

Array formula in E2 copied down
=SMALL(IF(B$2:B$5=B2,IF(D$2:D$5>=C2,IF(ISNA(MATCH(IF(B$2:B$5=B2,D$2:D$5),E$1:E1,0)),D$2:D$5))),1)
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Marcelo Branco]

***Update: I've just tried it on my work laptop and it works!
It must have been the older version of Excel. I will test it out further with some more data
Thanks very much Marcelo!

You are welcome. Glad to help.

M.

 

[mojo707]

Marcelo, is there any reason why this would be limited to only 6 rows? So far I've used sample data, but the real data set will be around 8000 rows. I have noticed that when I input data below row 6 the formula returns an error #NUM !

 

[Marcelo Branco]

You should adjust the ranges in the formula, changing:
B2:B5 to B2:Bn
D2:D5 to D2:Dn
where n is last row with data.

M.