# Formula to Match the Next Closest Date

#### sushi514

##### New Member
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
8.8 KB · Views: 8
• Inactives.png
4.8 KB · Views: 9

### Excel Facts

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

#### jasonb75

##### Well-known Member
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
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
50.9 KB · Views: 4
• Sheet1.png
71.1 KB · Views: 4

#### jasonb75

##### Well-known Member
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

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
91.1 KB · Views: 10

#### Fluff

##### MrExcel MVP, Moderator
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.

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

#### sushi514

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

Replies
3
Views
389
Replies
0
Views
209
Replies
3
Views
59
Replies
3
Views
269
Replies
4
Views
273

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.

### Which adblocker are you using?

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

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