Index/Match/Large Multiple Criteria-CodePudding

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))

CodePudding user response：

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)