index and match closest

[Mona]

example data:

 

[T. Valko]

Try this array formula**:

=INDEX(C$2:C$5,MATCH(1,(A$2:A$5=E2)*(ABS(B$2:B$5-D2)=MIN(IF(A$2:A$5=E2,ABS(B$2:B$5-D2)))),0))

Copy down as needed

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Mona]

T. Valko-
Thank you so much. This is working. I have a change. Instead of "closest"
date I need the closest date but not greater than. I modified my example a
bit to explain.

 

[T. Valko]

I need the closest date but not greater than.

If there is no date that meets the condition then the formula will return a
#N/A error.

Try this array formula** :

=INDEX(C$2:C$5,MATCH(1,--(B$2:B$5=MAX(IF((A$2:A$5=E2)*(B$2:B$5<D2),B$2:B$5))),0))

Copy down as needed

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)