Finding column title and returning row values in descending order (top 10)

[lentel26]

Hi All,

I appreciate the future input, I'm a bit stuck at the moment using index/match! I'm looking to create a drop down that contains all of the months (January, February, etc.)

Based on this drop down selection (i.e. February), I need the formula to pull out the top 10 sales values in descending order with the agent who sold it. Here's an example of my spreadsheet:

A B C D E F
1 Agent January February March April (cont'd)
2 Al 400 100 50 50
3 Ben 500 200 75 100
4 Chris 600 300 100 400

The person selects February in the dropdown and the spreadsheet returns in descending order:
Chris 300
Ben 200
Al 100

Thank you again for the help!

 

[sheetspread]

The pivot table does this easily. I recommend a normal format first: https://www.youtube.com/watch?v=xmqTN0X-AgY (though it's not absolutely needed here)

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H	I
1	Agent	January	February	March	April	May		3
2	Al	400	100	50	50			may
3	Ben	500	200	75	100	75		2
4	Chris	600	300	100	400	70		agent	sales
5	Dan	400	100	80	100			Ben	75
6								Chris	70
7
8
Sheet1

H1: 3 (Top N; set N to suit)

H2: may (a dropdown month selection)

In H3 enter:

=COUNTIFS(INDEX($B$2:$F$5,0,MATCH(H$2,$B$1:$F$1,0)),">="&LARGE(INDEX($B$2:$F$5,0,MATCH(H$2,$B$1:$F$1,0)),MIN(H$1,COUNT(INDEX($B$2:$F$5,0,MATCH(H$2,$B$1:$F$1,0))))))

This adjusts Top N to suit the data.

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

=IF($I5="","",INDEX($A$2:$A$5,SMALL(IF(INDEX($B$2:$F$5,0,MATCH(H$2,$B$1:$F$1,0))=$I5,ROW($A$2:$A$5)-ROW($A$2)+1),COUNTIFS($I$5:I5,I5))))

In I5 enter and copy down:

=IF(ROWS($I$5:I5)>H$3,"",LARGE(INDEX($B$2:$F$5,0,MATCH(H$2,$B$1:$F$1,0)),ROWS($I$5:I5)))

 

[lentel26]

Awesome, this works perfect!! Thank you so much!

	A	B	C	D	E	F	G	H	I
Agent	January	February	March	April	May
Al	may
Ben
Chris	agent	sales
Dan	Ben
Chris

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]400[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]500[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]600[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]400[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]75[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]70[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

H1: 3 (Top N; set N to suit)

H2: may (a dropdown month selection)

In H3 enter:

=COUNTIFS(INDEX($B$2:$F$5,0,MATCH(H$2,$B$1:$F$1,0)),">="&LARGE(INDEX($B$2:$F$5,0,MATCH(H$2,$B$1:$F$1,0)),MIN(H$1,COUNT(INDEX($B$2:$F$5,0,MATCH(H$2,$B$1:$F$1,0))))))

This adjusts Top N to suit the data.

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

=IF($I5="","",INDEX($A$2:$A$5,SMALL(IF(INDEX($B$2:$F$5,0,MATCH(H$2,$B$1:$F$1,0))=$I5,ROW($A$2:$A$5)-ROW($A$2)+1),COUNTIFS($I$5:I5,I5))))

In I5 enter and copy down:

=IF(ROWS($I$5:I5)>H$3,"",LARGE(INDEX($B$2:$F$5,0,MATCH(H$2,$B$1:$F$1,0)),ROWS($I$5:I5)))

 

[Aladin Akyurek]

Awesome, this works perfect!! Thank you so much!

You are welcome. Thanks for providing feedback.