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

MWhiteDesigns

Well-known Member
Joined
Nov 17, 2010
Messages
644
Office Version
  1. 2016
Platform
  1. Windows
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.
IDstart dateend dateMax_dateMin_dateas of dateIDdue 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

Well-known Member
Joined
Nov 17, 2010
Messages
644
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,738
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,053
Messages
5,628,330
Members
416,311
Latest member
S991102

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top