Excel: Using iterative calc to find first occurrence of date >= current date in another table

Armenius

I Drive Myself to the [H]ospital
Joined
Jan 28, 2014
Messages
20,549
Been trying to figure this one out, as either my Google Fu fails me or no one has done this kind of thing before and shared it. For various reasons I can't use a macro at work to make this easy on myself. Neither can I use SQL, so I have to use formulas in Excel.

I have two tables of data: one with a list of dates for one type of event (Event1), the other with dates of another type of event (Event2). The databases these separate events are in are siloed, so I have to match them up externally. I have to find the first occurrence of Event2 that occurs after the date of Event1. There can be multiple occurrences of Event2, so simply using a reference lookup isn't going to cut it. It is also possible for there to be no Event2 date that matches with Event1.

Reaching back to my programming days I broke the problem into its various pieces and came up with the following:
Code:
Event2 Count
=COUNTIF(Event2[Name],[@Name])

Event2 First Row
=IF([@[Event2 Count]],MATCH([@Name],Event2[Name],0),NA())

Event2 Next
=IF(IFNA(AND([@[Event2 Days Since Event1]]<1,[@[Event2 Next]]<[@[Event2 Count]],[@[Event2 Count]]<>1),[@[Event2 First Row]]),[@[Event2 Next]]+1,[@[Event2 Next]])

Event2 Next Row
=IF([@[Event2 Count]]=1,[@[Event2 First Row]],IF([@[Event2 Next]]+[@[Event2 First Row]]>[@[Event2 First Row]]+[@[Event2 Count]]-1,[@[Event2 First Row]]+[@[Event2 Count]]-1,[@[Event2 First Row]]+[@[Event2 Next]]))

Event2 Date
=INDEX(Event2[Date],[@[Event2 Next Row]])

Event2 Days Since Event1
=[@[Event2 Date]]-[@[Event1 Date]]
Note I have not tried to clean these formulas up, yet. It seems to be working in my sample of 60 Event1 vs. 100 Event2. Did I overcomplicate this? Is there a more simple way to approach this, keeping in mind my limitations? I'm about to deploy this solution to the full tables of around 3k rows a piece.
 
Top