Formula to Match the Next Closest Date

[sushi514]

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))

 

[jasonb75]

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

=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]

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)

 

[jasonb75]

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.

=MAXIFS(Sheet2!B:B,Sheet2!A:A,A2,Sheet2!B:B,"<="&D2)

 

[sushi514]

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.

 

[Fluff]

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]

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.

=MINIFS(Sheet2!B:B,Sheet2!A:A,A2,Sheet2!B:B,">"&D2)

 

[sushi514]

Ah, that makes sense. It worked! Thank you SO much, I've been agonizing over this for days!