# Excel: find the nearest (to a reference one) date for each row

I'm not an Excel expert (not by far) and I've been having a hard time finding a solution for the following problem. I have a table that represents the scheduled day of shift (A) for each employee (B) and I'm trying to come up with a formula whose outcome should be (for each row) the next shift of that employee after a given reference date (F1).

I've tried (found on the web) with LARGE + COUNTIF (formula below) but the result is the same date for each row whereas I'd like to find the "next date" > reference date (F1) for each employee:

In column C is the following formula:

=IFERROR(LARGE(\$A\$2:\$A\$12;COUNTIF(\$A\$2:\$A\$12;">"&\$F\$1));"").

In column D the outcome I'd like to get.

If you FILTER the list of dates for rows where the Employee column matches the Employee on the current row, and the date in the Shift column is greater than the reference date, you'll get a list of all shifts for that employee where the date is after the reference day. The "next" shift is just the smallest date from that list.

So, you can use this:

=MIN(FILTER([Shift],([Emp]=[@Emp])*([Shift]>\$F\$2)))

• I think I prefer your solution @FlexYourData. I find it easier to calculate also the last shift for each employee by simply changing the ">" to "=<" (I had some trouble with ZygD's one but it's probably just me :)) Mar 2, 2021 at 13:15

Not sure if this is the best solution, but you can do it using array formula (entered using Ctrl+Shift+Enter, not a regular Enter):

=INDEX(\$A\$2:\$A\$8,MATCH(1,(\$B\$2:\$B\$8=B2)*(\$A\$2:\$A\$8>TODAY()),0))

If you have a reference date in F1:

=INDEX(\$A\$2:\$A\$8,MATCH(1,(\$B\$2:\$B\$8=B2)*(\$A\$2:\$A\$8>\$F\$1),0))