Comparing 2 list and get the closest date record for each ID

[keongymonopoly]

Question
I got 2 lists and in list 2, I want to get the value from the result column of list 1 with the following criteria (I've attached the image of the 2 list):

• based on the Same ID
• AND Result record's date (list 1) later than the date in list 2
• AND Result record's date (list 1) nearest to the date in list 2

Expected output

• In List 2, second row should get the result from second row of List 1

Thanks in advance! Appreciate any help.

 

[Peter_SSs]

Welcome to the MrExcel board!

You will generally get a faster answer here if you provide your sample data and expected results with XL2BB to make it easier for helpers by not having to manually type out sample data to test with.

See if this is what you want.

20 08 07.xlsm
	A	B	C	D	E	F	G	H
1	ID	Datye	Result			ID	Date	Result
2	1	3-Aug-19	Good			1	1-Aug-19	Good
3	1	20-Aug-19	Bad			1	15-Aug-19	Bad
4	2	5-Aug-19	Fair			1	27-Aug-19	N/A
5	2	13-Aug-19	Good			2	1-Aug-19	Fair
6	3	27-Aug-19	Bad			2	13-Aug-19	N/A
7	3	12-Sep-19	Fair			3	25-Aug-19	Bad
8	3	25-Sep-19	Good			3	4-Sep-19	Fair
9						3	20-Sep-19	Good
10						3	27-Sep-19	N/A
Sheet2 (3)

Cell Formulas
Range	Formula
H2:H10	H2	=IFERROR(INDEX(C$2:C$8,AGGREGATE(15,6,(ROW(C$2:C$8)-ROW(C$2)+1)/((A$2:A$8=F2)*(B$2:B$8=MINIFS(B$2:B$8,A$2:A$8,F2,B$2:B$8,">"&G2))),1)),"N/A")