MWhiteDesigns
Well-known Member
- Joined
- Nov 17, 2010
- Messages
- 646
- Office Version
- 2016
- Platform
- 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.
I hope this makes sense. Thank you in advance.
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.
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.