Trying to derive the top 3 highest cost fruits and return the date they were bought excluding apples and oranges. I would like to avoid using a helper column. Having issues w/ these formulas. Not sure if you can have an array (Large w/ IFS) inside of another formula. The formulas I have so far:
=INDEX(A:A,MATCH(LARGE(IFS(B:B,"<>Apple",B:B,"<>Orange"),1),C:C,0))
=INDEX(A:A,MATCH(LARGE(IFS(B:B,"<>Apple",B:B,"<>Orange"),2),C:C,0))
=INDEX(A:A,MATCH(LARGE(IFS(B:B,"<>Apple",B:B,"<>Orange"),3),C:C,0))
>Solution :
With older versions we need to use Nested Aggregates:
=INDEX(A:A,AGGREGATE(15,7,ROW(A2:A12)/((C2:C12=AGGREGATE(14,7,C2:C12/((B2:B12<>"A")*(B2:B12<>"O")),ROW($ZZ1)))*(B2:B12<>"A")*(B2:B12<>"O")),1))
Put that in the first output cell and copy/drag it down.
with Office 365 we can use Take/SORT/FILTER:
=TAKE(SORT(FILTER(A:C,(B:B<>"A")*(B:B<>"O")),3,-1),3,1)
