Formula to Match the Next Closest Date

Cross-post link added by moderator: https://www.mrexcel.com/board/thread...-date.1170861/

Hi,

I'm trying to write a formula in Column F on Sheet1 to retrieve the record from Sheet2 with the next closest "Start Date" that is after the "End Date" on Sheet1.

So ideally for Employee 526319, I would populate "2/15/2021" in Cell F10 in Sheet1 and "1/28/2021" in Cell F25.

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 Sheet1 will match the Start Date on Sheet2, so my Unique columns match values. So either of these work for the normal circumstance:

=INDEX(Sheet2!B:B,MATCH(Sheet1!E2,Sheet2!D:D,0))

or =XLOOKUP(E2,Sheet2!D:D,Sheet2!B:B,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(Sheet2!E:E,MATCH(MIN(ABS(Sheet1!H16-Sheet2!E:E)),0))

Any help would be much appreciated!

On rows 10 & 25 of sheet 1 the unique number does not exist on Sheet2.

Perhaps it's the unique number that is wrong.

If you use instead the Employee number then you'll get a match.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

I have added the link for you since you are a new member. Please read the rules.