Excel array formula to find row of largest values based on multiple criteria

Question

Column: A        | B      | C      | D
Row 1:  Variable | Margin | Sales  | Index
Row 2:  banana   | 2      | 20     | 1
Row 3:  apple    | 5      | 10     | 2
Row 4:  apple    | 10     | 20     | 3
Row 5:  apple    | 10     | 10     | 4
Row 6:  banana   | 10     | 15     | 5
Row 7:  apple    | 10     | 15     | 6

"Variable" sits in column A, row 1.
"Fruit" refers to A2:A6
"Margin" refers to B2:B6
"Sales" refers to C2:C6
"Index" refers to D2:D6

Question:

From the above table, I would like to find the row of two largest "Sales" values when Fruit = "apple" and Margin >= 10. The correct answer would be values from row 3 and 6. I have tried the following methods without success.

I have tried

=LARGE(IF(Fruit="apple",IF(Margin>=10,Sales)),{1,2}) + CSE 

and this returns 20 and 15, but not the row.

I have tried

=MATCH(LARGE(IF(Fruit="apple",IF(Margin>=10,sales)),{1,2}),Sales,0)+1

but returns row 2 and 6 as the first matches to come up are the 20 and 15 from "banana" not "apple".

I have tried

=INDEX(D2:D7,LARGE(IF(Fruit="apple",IF(Margin>=10,ROW(Sales)-ROW(INDEX(Sales,1,1))+1)),{1,2}),1)

But this returns row 7 and 5 (i.e. "Index" 6 and 4) as these are just the first occurrences of "apple" starting from the bottom of the table. They are not the largest values.

Can this be done with an Excel formula or do would I need a macro? If macro, can I please get help with the macro? Thank you!

Answer 1

use this formula:

=INDEX(D:D,AGGREGATE(15,6,ROW($A$2:$A$7)/(($B$2:$B$7>=10)*($A$2:$A$7="apple")*($C$2:$C$7 = AGGREGATE(14,6,$C$2:$C$7/(($B$2:$B$7>=10)*($A$2:$A$7="apple")),F2))),1))

I put 1 and 2 in F2 and F3 respectively to find the first and second.

---

Edit #1

to deal with duplicates we need to add (COUNTIF($G$1:G1,$D$2:$D$7) = 0). The $G$1:G1 needs to refer to the cell directly above the first placement of this formula. So the formula needs to start in at least row 2.

=INDEX(D:D,AGGREGATE(15,6,ROW($A$2:$A$7)/((COUNTIF($G$1:G1,$D$2:$D$7) = 0)*($B$2:$B$7>=10)*($A$2:$A$7="apple")*($C$2:$C$7 = AGGREGATE(14,6,$C$2:$C$7/(($B$2:$B$7>=10)*($A$2:$A$7="apple")),F2))),1))