1

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")&""" ") – pdrops Jun 22 '16 at 23:54
1

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.