Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
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.
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.