Parsing out a cell from an array, and outputting the value.

[ScottP4680]

So I have a horizontal rolling calendar that has columns for the month, and in each column there's a date something happened (a calibration date) in a given month. I need a formula that can spit out the last 2 dates in a given row, and ignoring any date that's yet to come (the upcoming due date for the event to occur). Once cell will have the most recent date, prior to today, and another cell will have the SECOND most recent date.

My semi-excel algorithm looks like this in my head:
=#ROWLOOKUPIF(#REF<today(), #LASTCELLVALUE, #KEEPLOOKING)

 

[Marcelo Branco]

Maybe something like this

Dates as dd/mm/yyyy

Pasta1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec		Result1	Result2
2			02/02/2021			10/05/2021			20/08/2021				30/12/2021		20/08/2021	10/05/2021
3				04/03/2021	05/04/2021			07/07/2021					27/12/2021		07/07/2021	05/04/2021
4							10/06/2021						30/12/2021		10/06/2021
Plan4

Cell Formulas
Range	Formula
O2:P4	O2	=IF(COUNTIF($B2:$M2,"<"&TODAY())>=COLUMNS($O2:O2),AGGREGATE(14,6,$B2:$M2/($B2:$M2<TODAY()),COLUMNS($O2:O2)),"")

Hope this helps

M.

 

[ScottP4680]

OMG, thank you so much.

 

[Marcelo Branco]

You're welcome. Glad to help.

M.