excelexcel-formulaexcel-2010

Excel 2010 formula to show earliest date & value from table (list of dates) that is after a reference date assigned to each unique ID

I have this formula that isn't quite correct. The best example of the issue is in cell C5:

=IF(MAX(IF(F5:F15=$A5,G5:G15))<$B5,"",SUMIFS(H5:H15,F5:F15,$A5,G5:G15,MIN(IF(F5:F15=$A5,G5:G15))))

It is finding the earliest value, but not the earliest value AFTER the reference date in column B. It shows the value as 0.1, but I need it to show as 2. because this is on or after the reference date.

I also need the same for the date in column D, which will be aligned to the adjacent value, so that the left table in columns C and D, show the earliest date and value from the right table, where the ID is matched and are after each reference date for each ID.

Is this possible with a formula?

enter image description here

Column F to G:

id eventdate value
1 3/8/2024 200
3 3/5/2024
3 2/5/2021 300
25 7/16/2024 72
25 4/22/2024 50
7825 7/17/2024
7825 7/16/2024 2
7825 7/15/2024 0.9
7825 6/17/2024 0.7
7825 9/6/2005 0.1
23250 4/8/2024 0.2

Columns A To D

ID REFERENCE DATE VALUE EVENTDATE
1 3/8/2024
3 3/7/2024
25 4/22/2024
7825 7/16/2024
23250 4/8/2024
Solution

  • Please try using the following formula:

    enter image description here

    • For Values:

    =INDEX($H$2:$H$12,
 MATCH(AGGREGATE(15,6,ABS(B2-$G$2:$G$12)/(A2=$F$2:$F$12),1),
 ABS(B2-$G$2:$G$12)/(A2=$F$2:$F$12),0))

    • For Dates:

    =INDEX($G$2:$G$12,
 MATCH(AGGREGATE(15,6,ABS(B2-$G$2:$G$12)/(A2=$F$2:$F$12),1),
 ABS(B2-$G$2:$G$12)/(A2=$F$2:$F$12),0))