array search for top 2 values of each product

[gvillepa]

Hey folks, Could use the assistance by those smarter than me!

Each sales person sells 2 categories. There are 2 products in each category. Trying to find a way to return the Top 2 largest values for each product:

Red Delicious Apple Highest Sales = John Smith
Red Delicious Apple 2nd Highest Sales = Bob Smith
Orange Valencia Highest Sales = Bob Smith
Orange Valenicia 2nd Highest Sales = John Doe (A tie doesnt matter so long as highest and 2nd highest reflect the tie).

Was thinking just an index with large function. Not worried about aesthetics (i can clean up on the formatting and the way it returns, just need function assistance) Help is appreciated, thanks!

Name	Product	Type	Sales
John Doe	Apple	Red Delicious	9
John Doe	Apple	Granny Smith	8
John Doe	Orange	Valencia	7
John Doe	Orange	Clementine	3
Bob Smith	Apple	Red Delicious	1
Bob Smith	Apple	Granny Smith	16
Bob Smith	Orange	Valencia	7
Bob Smith	Orange	Clementine	3

<tbody>
</tbody>

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G
1	Name	Product	Type	Sales		apple	orange
2	gvillepa	Apple	Red Delicious	9		red delicious	valencia
3	John Doe	Apple	Granny Smith	8		2	2
4	John Doe	Orange	Valencia	7		gvillepa	John Doe
5	John Doe	Orange	Clementine	3		Bob Smith	Bob Smith
6	Bob Smith	Apple	Red Delicious	1
7	Bob Smith	Apple	Granny Smith	16
8	Bob Smith	Orange	Valencia	7
9	Bob Smith	Orange	Clementine	3
Sheet1

In F3 control+shift+enter, not just enter, and copy across:

=COUNTIFS($C$2:$C$9,F$2,$D$2:$D$9,">="&LARGE(IF($C$2:$C$9=F$2,$D$2:$D$9),MIN(2,COUNTIFS($C$2:$C$9,F$2))))

In F4 control+shift+enter, not just enter, and copy down:

=IF(ROWS(F$4:F4)<=F$3,INDEX($A$2:$A$9,SMALL(IF($D$2:$D$9=LARGE(IF($C$2:$C$9=F$2,$D$2:$D$9),ROWS(F$4:F4)),ROW($D$2:$D$9)-ROW($D$2)+1),
SUM(IF(LARGE(IF($C$2:$C$9=F$2,$D$2:$D$9),ROW(F$4:F4)-ROW(F$4)+1)=LARGE(IF($C$2:$C$9=F$2,$D$2:$D$9),ROWS(F$4:F4)),1)))),"")

 

[shaowu459]

Data range A1:D9, F1:G5 store below values:

Product	Type
Apple	Red Delicious
Apple	Granny Smith
Orange	Valencia
Orange	Clementine

<colgroup><col><col></colgroup><tbody>
</tbody>

H1:I1 store values:

1st

2nd

<tbody>
</tbody>

Enter below formula in H2(Ctrl+Shift+Enter for the array formula) and copy to range H2:I5:

=INDEX($A:$A,MOD(LARGE(IF(($B$2:$B$9=$F2)*($C$2:$C$9=$G2),$D$2:$D$9*10^4+ROW(B$2:B$9)),COLUMN(A1)),10^4))