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.

1
  • 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, 2016 at 23:54

1 Answer 1

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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