XLOOKUP with multiple criteria and Earliest and Latest Date

[Chinmay_Pednekar]

Hi There!

I Have 2 datasets

Dataset 1: It gives me an exhaustive set of portfolio companies of a certain set of investors. One investor can invest in multiple companies and Multiple Investors can invest in same company Eg. C3, C1

InvestorID	CompanyID	Other fields.....
I1	C1
I1	C2
I1	C3
I1	C4
I2	C3
I2	C5
I3	C6
I4	C7
I4	C1

Dataset 2:Gives me a set of investment deal details of all investments made by investments made by investors in companies. One investor can make multiple investment in a company

Investor ID	CompanyID	DealID	Deal Date(MMDDYYY)	Other Fields
I1	C1	D1	1-1-2021
I1	C2	D2	1-2-2021
I1	C3	D3	1-3-2021
I1	C4	D4	1-4-2021
I2	C3	D5	1-5-2021
I2	C5	D6	1-6-2021
I3	C6	D7	1-7-2021
I4	C7	D8	1-8-2021
I4	C1	D9	1-9-2021
I1	C1	D10	1-10-2021
I1	C2	D11	1-11-2021

Desired Dataset

Investor ID	CompanyID	Earliest Deal Date	Latest Deal Date
I1	C1	1-1-2021	1-10-2021
I1	C2	1-2-2021	1-11-2021
I1	C3	1-3-2021	1-3-2021
I1	C4	1-4-2021	1-4-2021
I2	C3	1-5-2021	1-5-2021
I2	C5	1-6-2021	1-6-2021
I3	C6	1-7-2021	1-7-2021
I4	C7	1-8-2021	1-8-2021
I4	C1	1-9-2021	1-9-2021

Would be great if you could please help me with this. 

Thanks in Advance. 

Best,

Chinmay

[L_T · Accepted solution]

Hi @Chinmay_Pednekar 

The attached workflow should help. 

Let me know if you have any questions