Find past and nearest date with parameter "X" for a given date

[Wojciech]

Hi! I would like to find past and nearest date with parameter "X" for a given date. Sorry for my English .

 

[Peter_SSs]

Welcome to the MrExcel board!

An image is not much use to us as we cannot copy the data to test with. You will get many more potential helpers if instead you post a small (copyable) screen shot or two directly in your post. My signature block below has help regarding that.

In relation to your data, are those dates in A2:A7 and A11:A14 actual dates (numbers) or text? If you are unsure, what does this formula return if you enter it in an empty cell?
=ISNUMBER(A2)

 

[Peter_SSs]

are those dates in A2:A7 and A11:A14 actual dates (numbers) or text?

Assuming they are actual dates, try this in B11, copied down.

Excel Workbook
	A	B
2	2017-11-01	X
3	2017-12-10	Y
4	2018-05-01	X
5	2018-05-01	Y
6	2018-05-10	X
7	2018-05-10	Y
8
9
10
11	2015-01-01
12	2017-12-10	2017-11-01
13	2018-05-05	2018-05-01
14	2018-05-13	2018-05-10
Nearest Past Date

 

[Wojciech]

They are actual dates, thanks! Ok, next time I will put the data to test with, just like you in the last post.

 

[Peter_SSs]

So, just checking - that suggested formula does what you want?

 

[Wojciech]

Yes, this formula was exactly what I needed. A very transparent formula. Thank you.

 

[Peter_SSs]

Yes, this formula was exactly what I needed. A very transparent formula. Thank you.

OK, thanks for the confirmation.

I would still stick with that formula but if you happen to have Excel through Office 365 then you could also use the MAXIFS function, though in this case it has to be coerced a bit.

Excel Workbook
	A	B
2	2017-11-01	X
3	2017-12-10	Y
4	2018-05-01	X
5	2018-05-01	Y
6	2018-05-10	X
7	2018-05-10	Y
8
9
10
11	2015-01-01
12	2017-12-10	2017-11-01
13	2018-05-05	2018-05-01
14	2018-05-13	2018-05-10
Nearest Past Date (2)

.. or yet another option (still for Office 365) would be to use the much simpler
=MAXIFS(A$2:A$7,A$2:A$7,"<"&A11,B$2:B$7,"X")
and use Conditional formatting to hide any 0 (displayed as 1900-01-00) values.

 

[Wojciech]

Due to internal requirements in my work, I do not have access to office 365, but I will note this solution for the future. Thank you.