The following is returned in an array of dates when the 2 conditions are true. I want it to just return the most recent date. I know I can filter a filter, but the logic here is escaping me.
=FILTER(data!B:B,data!A:A=A30,data!F:F=B30)
finding the most recent date in a filter function using sheets
Viewed 327
3 Answers
I would think a Maxifs() would work fine.
=MAXIFS(data!B:B,data!A:A,A30,data!F:F,B30)
If your FILTER function is returning an array. You can use the MAX function on it to return the highest date:
=MAX(FILTER(data!B:B,data!A:A=A30,data!F:F=B30))
Documentation
try:
=INDEX(SORTN(SORT({data!B2:B, data!A2:A}, data!F2:F, 0), 9^9, 2, 2, 1),,1)
sort data {B & A} based on F in descending order. then sortn all rows (9^9), group them (2), based on A column (2), in ascending order (1). and index only 1st column
Related