Excel – How to find first date after given date in a sorted column

microsoft excelworksheet-function

I have two columns of sorted dates in Excel. Given a date in column 1, how do I find the first date in column 2 after the given date?

Best Answer

You can do this with INDEX and MATCH. I'm assuming your date columns are sorted from oldest to newest. To find the first date after A2 in B2:B30, use the following formula.

=INDEX(B2:B30,MATCH(A2,B2:B30,1)+1)

The key is that MATCH(A2,B2:B30,1) will return the index of the most recent date in B2:B30 that matches or is before the date in A2. Since your dates are sorted, the next date in the list will occur after the date in A2, so adding 1 to this index will give you what you want.

Related Question