Find the nearest date in a range of dates formula

Hi,

Was hoping someone could help me with a formula that returns the nearest date in a range of dates, where the returned date is earlier than the search date.

This is what I have: =INDEX(B:B,MATCH(MIN(IF(C:C=L2,abs(B:B-K2))),ABS(B:B-K2),0)), however, this returns the nearest date later than the search date.

Thank you,
Caroline

For context, this is an example:

A B C D E
Date Object Search date Object Returned date
06/05/2007 a 06/14/2007 a 06/16/2007
10/10/2007 b
06/16/2007 a
03/04/2007 b


=INDEX(A:A,MATCH(MIN(IF(B:B=D3,abs(A:A-C3))),ABS(A:A-C3),0))
Returns 06/16/2007

It would be best if you could post a sanitized sample of your data and criteria. It looks like you have other criteria based on your formula which isnt explained in your post and your example makes it extremely difficult to be sure of what data is reference by your formula.

If the Date was the only criteria then a MAX/IF array formula would it. I have a feeling you have other criteria though.

Thank you 63falondude. The formula returns a value however it is not the nearest value.
Thank you Zer0Cool. I'm new to this forum. Working on figuring out how to post data.

I'm not sure where you're going wrong. I have attached a workbook showing the formula in action.

For display purposes, I have put a column in between Object and Search date.

Also, it would help much more if you share what the expected result is not the wrong result.

Thank you 63falcondude. I cannot open your file. I believe it's because I am using google sheets.
The expected result, is 06/05/2007

That would have been helpful to know from the start... This is an Excel forum, not a Google Sheets forum.

Although the two may seem similar to you, they are quite different.

That being said, we do have a section for other platforms here:
https://www.excelforum.com/for-other...mobile-os-etc/

Oversight on my part. Still getting familiar with what's what. Thank you for the link. I appreciate your time.