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?
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 |
1
Please try using the following formula:
• 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))
16