Hi all,
I've been struggling with this one for a while:
In sheet 1 I have a number of observations on different people identified by an ID number in column A ("ID"). Each observation has a date in column B ("Date1") and every person may appear multiple times. In sheet 2, I have more observations on the same people with ID numbers in column A ("ID") and different dates in column B ("Date2") referring to different events than those in sheet 1. Again, every person may appear multiple times.
Now, what I need to do is search for the dates in sheet2 that are the closest before and after each date in sheet1 for a particular ID number. I named these new columns "ClosestDate2Before" and "ClosestDate2After".
I managed to match the dates closest before and after from the whole range of dates in sheet2 with this formula (as currently shown in cells C2 and D2):
"ClosestDate2Before" =INDEX(Sheet2!B:B,MATCH(Sheet1!B2,Sheet2!B:B,1))
"ClosestDate2After" =INDEX(Sheet2!B:B,MATCH(Sheet1!B2,Sheet2!B:B,-1))
Incidentally, the closest date after formula does not seem to work (it returns #N/A! not just for the first cell but every cell of the file) and I'd very much appreciate help on this (the dates may have to be sorted a differently?).
But the real problem is that I need to search for matching dates only within those rows of sheet 2 that contain the same ID number as the row in sheet 1, so the look-up range has to be restricted to those rows in sheet 2 with the same ID as the row in sheet 1.
Any help is greatly appreciated! Thanks so much!
Bookmarks