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!

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Product[/TD]
[TD]Type[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Apple[/TD]
[TD]Red Delicious[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Apple[/TD]
[TD]Granny Smith[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Orange[/TD]
[TD]Valencia[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]Orange[/TD]
[TD]Clementine[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Bob Smith[/TD]
[TD]Apple[/TD]
[TD]Red Delicious[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Bob Smith[/TD]
[TD]Apple[/TD]
[TD]Granny Smith[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]Bob Smith[/TD]
[TD]Orange[/TD]
[TD]Valencia[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Bob Smith[/TD]
[TD]Orange[/TD]
[TD]Clementine[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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:

[TABLE="width: 164"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Product[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Red Delicious[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Granny Smith[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Valencia[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Clementine[/TD]
[/TR]
</tbody>[/TABLE]

H1:I1 store values:[TABLE="width: 160"]
<tbody>[TR]
[TD="class: xl65, width: 80"]1st[/TD]
[TD="class: xl65, width: 80"]2nd[/TD]
[/TR]
</tbody>[/TABLE]

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