# Filter last result before a specific date

I've got the following formula working. However, I need only the last result, but at the moment it is returning more than one result.

``````=IFERROR(FILTER(MAX(FORM!\$Q\$3:\$Q,FORM!\$N\$3:\$N<\$A\$1,FORM!\$O\$3:\$O=\$A3,FORM!\$C\$3:\$C="End of Day")),"NO ENTRY")
``````

`\$A\$1` is the given date in the formula, so `<` indicates where N is less than the given date.

I have tried `MAX(FILTER , FILTER(MAX`.

• I have also tried to adapt it to a Query without success. =QUERY(FORM!C3:Q,"Select MAX(Q) where C = "End of Day" and O = '"&A2&"' and N date < """&text(\$A\$1,"yyyy-mm-dd")&""" ") Jun 22, 2016 at 23:54

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.