EXCEL matching dates

Question

Dearest Wizards,

Col A has a list of unsorted random dates

Col B has data associated with the dates in col A

C1 has a date

What I want (Formula/Function) in D1:

The value in Col B associated with the date in Col A that is closest to the date value in C1 without being greater than C1

TIA for your help with this!

Sam

Answer 1

Hi Sam, thanks for posting here in the Microsoft community, you can use this formula in D1: =INDEX(B:B, MATCH(MAX(FILTER(A:A, A:A<=C1)), A:A, 0))

This finds the latest date in Column A that’s not greater than C1, then returns the corresponding value from Column B.

Make sure Column A contains valid Excel date values and is not text-formatted, or the formula may not work correctly.

 

I hope this helps you out, let me know

Chard