Formula to Match the Next Closest Date

sushi514

New Member
Joined
Nov 13, 2020
Messages
29
Hi,

I'm trying to write a formula in Column E on the Inactives sheet to retrieve the record from the Returns sheet with the next closest "Start Date" that is after the "End Date" on the Inactives sheet.

So ideally for Employee 123, I would populate "2/15/2021" in Cell E2 in the Inactives sheet and "1/28/2021" in Cell E3 in the Inactives sheet.

I tried doing this by creating a unique instance of each record as I have multiple Employee records with varying start and end dates on both sheets. In a normal circumstance, the (End Date - 1) on the Inactives sheet will match the Start Date on the Returns sheet, so my Unique columns match values. So either of these work for the normal circumstance:

=INDEX(Returns!E:E,MATCH(Inactives!H2,Returns!G:G,0))

or =XLOOKUP(H2,Returns!G:G,Returns!E:E,0)

But I can't seem to find anything that makes sense to me in order to retrieve record with the next closest date that is not exact. I tried doing something like this but this isn't making sense: =INDEX(Returns!E:E,MATCH(MIN(ABS(Inactives!H16-Returns!E25)),0))
 

Attachments

  • Returns.png
    Returns.png
    8.8 KB · Views: 8
  • Inactives.png
    Inactives.png
    4.8 KB · Views: 9

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,542
Office Version
  1. 365
Platform
  1. Windows
You're using exact match, so it is looking for a date that is exactly the same, not the latest one before it.

Your formulas don't match the screen captures in your post so best guess from your description is
Excel Formula:
=MAXIFS(Returns!B:B,Returns!A:A,A2,Returns!B:B,"<="&C2)

You could probably use something similar for your 'normal circumstances' instead of using the additional 'unique' columns.
 

sushi514

New Member
Joined
Nov 13, 2020
Messages
29
Sorry about that - here are some new screenshots. Tried to install this XLS2BB add-in but it's not working at the moment.

I'd love to not use a 'unique' column as well as it's probably an additional step, but not sure how to get around it. I tried adding your formula in Column H but it doesn't seem to work for all instances (but probably because my original example was not clear).

Basically the highlighted rows in orange are my problems.

Just mentioning again: Test1 column formula in Cell F2: =INDEX(Sheet2!B:B,MATCH(Sheet1!E2,Sheet2!D:D,0)) and Test2 Column formula in G2: =XLOOKUP(E2,Sheet2!D:D,Sheet2!B:B,0)
 

Attachments

  • Sheet2.png
    Sheet2.png
    50.9 KB · Views: 4
  • Sheet1.png
    Sheet1.png
    71.1 KB · Views: 4

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,542
Office Version
  1. 365
Platform
  1. Windows
Your new screen capture has an extra column so the formula would have been looking at the wrong date in sheet1. Same formula, adjusted to the new screen captures.
Excel Formula:
=MAXIFS(Sheet2!B:B,Sheet2!A:A,A2,Sheet2!B:B,"<="&D2)
 

sushi514

New Member
Joined
Nov 13, 2020
Messages
29

ADVERTISEMENT

I'm not sure that's the reason as I'm getting hits in Column H, but very few. The formula in Column H is the same as what you've posted.
 

Attachments

  • Closest Dates.png
    Closest Dates.png
    91.1 KB · Views: 10

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,444
Office Version
  1. 365
Platform
  1. Windows
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Formula to Match the Next Closest Date
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,542
Office Version
  1. 365
Platform
  1. Windows
I had misread part of the description of what you wanted, the formula In my earlier post was looking for the closest date before then end date rather than after.
Excel Formula:
=MINIFS(Sheet2!B:B,Sheet2!A:A,A2,Sheet2!B:B,">"&D2)
 
Solution

sushi514

New Member
Joined
Nov 13, 2020
Messages
29
Ah, that makes sense. It worked! Thank you SO much, I've been agonizing over this for days!
 

Forum statistics

Threads
1,141,413
Messages
5,706,300
Members
421,440
Latest member
cmphares

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