Return Date from Matched Adjacent Cell which is Closest to Current Adjacent Cell

[Tguillaume]

Hi all,

I currently have two tables. Table 1 includes a patient's Unique ID and when they visited the hospital (Hospital Visit). In Table 2, there is also the Unique ID, but instead of Hospital Visit I have the date that the patient's primary doctor called to follow up with the patient after their hospital visit. I have made the data simpler below, but it shows how it is organized.

I need to create a third table which pulls the Doctor's Call date which is closest to the Hospital Visit date, but not before the Hospital Visit date. For example, Table 3 should look like this:

I need help with a formula that I would use in column J (Doctor's Call).

I've tried using combinations of IF(), COUNTIF(), AND(), and other formulas to achieve this goal but I am stuck. I would appreciate any help in learning how to match on the Unique IDs and then pull in the date I desire from a range of dates in the adjacent cell. I know I can use INDEX MATCH to pull in the most recent Doctor's Call, but this may not be the one I need (the Doctor's Call that is soonest after the Hospital Visit).

Thanks for any help!

 

[AhoyNC]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J
1
2		ID	Hosp. Visit		ID	Doctor call		ID	Hosp. Visit	Call
3		John Smith	1/1/2021		John Smith	12/31/2020		John Smith	1/1/2021	1/4/2021
4		Jane Smith	2/12/2021		John Smith	1/4/2021		Jane Smith	2/12/2021	2/15/2021
5		Robert Brown	3/4/2021		John Smith	1/7/2021		Robert Brown	3/4/2021	3/5/2021
6		Saah Jane	4/15/2021		Jane Smith	2/10/2021		Saah Jane	4/15/2021	4/17/2021
7					Jane Smith	2/15/2021
8					Robert Brown	3/5/2021
9					Saah Jane	4/12/2021
10					Saah Jane	4/14/2021
11					Saah Jane	4/17/2021
Sheet1

Cell Formulas
Range	Formula
J3:J6	J3	=MIN(FILTER($F$3:$F$11,(H3=$E$3:$E$11)*($F$3:$F$11>=I3)))

 

[Tguillaume]

This works! Thank you so much, AhoyNC - this is brilliant.

 

[AhoyNC]

You're welcome. Thanks for the feedback.