Find Max date prior to specific date and min date after specific date for each ID

[MWhiteDesigns]

I can't for the life of me figure this out. I've tried maxifs (don't have the function available in my version), vlookup, match, index etc. I just don't know where to start.

I have a list of ID's in one tab along with a start date and end date, Col A,B,C respectively. In a second tab I have an as of date, ID and a due date, again columns a,b,c respectively. There are duplicates in the second tab.

I need to do the following.

• lookup the ID on the first tab
• compare the start date to the as of date on the second tab
• return the due date based on the most recent (max) as of date PRIOR to the start date.
• compare the end date to the as of date on the second tab
• return the due date based on the first (min) as of date AFTER the end date.

I've placed example data on one sheet for ease.

ID	start date	end date	Max_date	Min_date		as of date	ID	due date
123456789	5/30/2020​	9/26/2020​	4/30/2020​	12/1/2020​		4/15/2020​	123456789	1/1/2020​
987654321	10/2/2020​	1/26/2021​	9/27/2020​	6/2/2021​		4/18/2020​	123456789	4/30/2020​
						9/25/2020​	123456789	9/1/2020​
						9/26/2020​	123456789	10/1/2020​
						9/27/2020​	123456789	12/1/2020​
						9/28/2020​	123456789	12/12/2020​
						10/1/2020​	123456789	12/21/2020​
						9/29/2021​	987654321	9/22/2020​
						9/30/2021​	987654321	9/23/2020​
						10/1/2021​	987654321	9/27/2020​
						10/2/2021​	987654321	9/28/2020​
						1/24/2021​	987654321	10/8/2020​
						1/25/2021​	987654321	10/9/2020​
						1/26/2021​	987654321	5/2/2021​
						1/27/2021​	987654321	6/2/2021​
						1/28/2021​	987654321	7/1/2021​

I hope this makes sense. Thank you in advance.

 

[MWhiteDesigns]

Yes. Some of my example dates were incorrect. My apologies. However this gets me where I need to be! Thank you so much for the assistance. It is greatly appreciated.

 

[Fluff]

Glad we could help & thanks for the feedback.