highest date with condition

[Tschiku]

Hello all,

(i'm learning alot with this forum!!! Thank you all!!!)

I need your help, because i'm banging with my head on the wall...

I Have a table, with accounts, and ID that were created on a data. The same account may have different activations, wich one with a different id, with a new creation date:

TABLE 1

A	B	C	D
ACCOUNT	ID	Creation Date	Amount Id's
7000004	70000040026	30/05/2014	1
7000005	58912	08/04/2020	2
7000005	28119	02/08/2017	2
7000007	70000070096	30/05/2014	1
7000008	70000080087	01/02/2019	1
7000060	70000600007	28/08/2019	1
7000064	70000640068	02/01/2020	1
7000066	14622	11/02/2016	2
7000066	70000660050	30/05/2020	2
7000067	70000670041	30/05/2014	1
7000070	2048	13/08/2014	3
7000070	70000700014	30/05/2014	3
7000070	70000730084	01/04/2020	3
7000080	70000800021	30/05/2014	1

Afer a new contact, i need to get the nearest high date after contact (new activation), after the date contact, with the id associated to it (the last two columns, are the expected date and expecte result id).
TABLE 2

Contact - ACCOUNT	Date_Contact	Expected Result Date	Expected Result ID
7000004	12/02/2020	blank	blank
7000005	01/12/2019	08/04/2020	58912
7000005	13/03/2020	08/04/2020	58912
7000007	01/08/2019	blank	blank
7000008	15/01/2019	01/02/2019	70000080087
7000060	01/07/2019	28/08/2019	70000600007
7000066	14/02/2019	blank	blank
7000066	01/08/2020	blank	blank
7000067	01/12/2013	30/05/2014	70000670041
7000070	28/04/2014	30/05/2014	70000700014
7000070	01/01/2020	01/04/2020	70000730084
7000070	01/01/2012	30/05/2014	70000700014
7000070	01/07/2014	13/08/2014	2048

I appreciate your help! Thank you

TS

 

[Eric W]

Welcome to the MrExcel forum!

Given that you have Excel 365, you should have the new array formulas. So given this layout:

Dynamic functions.xlsm
	A	B	C	D	E	F	G	H	I
1	ACCOUNT	ID	Creation Date	Amount Id's		Contact - ACCOUNT	Date_Contact	Expected Result Date	Expected Result ID
2	7000004	70000040026	5/30/2014	1		7000004	2/12/2020
3	7000005	58912	4/8/2020	2		7000005	12/1/2019	4/8/2020	58912
4	7000005	28119	8/2/2017	2		7000005	3/13/2020	4/8/2020	58912
5	7000007	70000070096	5/30/2014	1		7000007	8/1/2019
6	7000008	70000080087	2/1/2019	1		7000008	1/15/2019	2/1/2019	70000080087
7	7000060	70000600007	8/28/2019	1		7000060	7/1/2019	8/28/2019	70000600007
8	7000064	70000640068	1/2/2020	1		7000066	2/14/2019	5/30/2020	70000660050
9	7000066	14622	2/11/2016	2		7000066	8/1/2020
10	7000066	70000660050	5/30/2020	2		7000067	12/1/2013	5/30/2014	70000670041
11	7000067	70000670041	5/30/2014	1		7000070	4/28/2014	5/30/2014	70000700014
12	7000070	2048	8/13/2014	3		7000070	1/1/2020	4/1/2020	70000730084
13	7000070	70000700014	5/30/2014	3		7000070	1/1/2012	5/30/2014	70000700014
14	7000070	70000730084	4/1/2020	3		7000070	7/1/2014	8/13/2014	2048
15	7000080	70000800021	5/30/2014	1
Sheet7

Put this formula in H2 and copy down:

=IFERROR(INDEX(SORT(FILTER($A$2:$C$15,($A$2:$A$15=F2)*($C$2:$C$15>=G2)),3),1,3),"")

and this in I2 and copy down:

=IFERROR(INDEX(SORT(FILTER($A$2:$C$15,($A$2:$A$15=F2)*($C$2:$C$15>=G2)),3),1,2),"")

If you don't have SORT or FILTER, the H column would be easy enough to do with MAXIFS or an array formula, but the I column would be trickier. Full disclosure: I don't have the new functions yet, but I tested the formula using UDFs.

 

[Tschiku]

Thank you! i'll give it a try!

 

[Tschiku]

The underrated situation when some people don´t help others, is because they don't understand how supportive is to help someone to learn with the way of thinking on the formulas!

This helped me alot!!! Many kudos to you ERIC W!!!

 

[Eric W]

Glad it works for you! Thanks for the update!