One can get the last date of a list by sorting it in descending order and applying array_constrain
. For example (not using your data structure)
=array_constrain(sort(filter(B:E, C:C < today()), 2, false), 1, 4)
means: among the rows where the date in column C is prior to today, select one with the latest date in C. The sort happens by the 2nd column of the range B:E (which is C), in descending order (false). The array_constrain
keeps 1 row and 4 columns of the results.
If instead of last date you want "last row" among those filtered, append a column with row number and sort by it:
=array_constrain(sort(filter({B:E, row(B:E)}, C:C < today()), 5, false), 1, 4)
Here row numbers are the 5th column of the filtered range, and sort is applied to it. Then array_constrain keeps the first row, and only 4 entries in it.