Pull corresponding name for top 10

[mhenk]

My data is organized in 5 columns of 33 rows (U7:Y39), all numeric values

What I'd like to do is identify the top 10 items in this range (which I do via the LARGE function), and then return the corresponding header from row 6.

Any advice?

 

[p45cal]

Excel Workbook
	O	P	Q	R	S	T	U	V	W	X	Y
6							Header1	Header2	Header3	Header4	Header5
7							857	728	315	97	770
8							257	959	478	406	739
9							965	877	960	428	495
10							595	583	174	788	30
11							350	124	481	310	26
12							833	288	702	292	290
13	1	980	4	Header5	Header5		323	218	943	811	447
14	2	965	0	Header1	Header1		362	908	887	546	96
15	3	963	0	Header1	Header1		78	785	920	134	561
16	4	960	2	Header3	Header3		506	830	512	426	147
17	5	959	1	Header2	Header2		22	472	16	744	335
18	6	954	2	Header3	Header3		657	925	455	461	93
19	7	943	2	Header3	Header3		821	589	656	264	323
20	8	925	1	Header2	Header2		319	504	815	535	578
21	9	922	3	Header4	Header4		431	268	219	300	899
22	10	920	2	Header3	Header3		28	900	287	296	324
23							711	666	442	868	506
24							244	882	612	817	606
25							901	239	50	831	477
26							234	219	91	78	179
27							963	491	690	711	980
28							830	351	323	101	121
29							705	255	472	469	625
30							202	564	775	922	765
31							920	891	898	816	353
32							561	142	33	875	90
33							149	244	748	24	845
34							708	423	799	723	295
35							0	614	690	662	476
36							549	472	527	279	755
37							678	16	482	354	200
38							297	428	954	449	769
39							882	13	633	529	795
Sheet

The results are in column S, which is a formula which combines the two formulae in the cells in columns Q & R (just so you can see how it's made up).
The rng in the formula is the yellow range given the name rng. You can replace it with the range address instead, if you want ($U$7:$Y$39).
Note the formulae in Q13 and S13 are ARRAY-ENTERED as the note about Formula Array: says above.
You ONLY need the formula in column S, the formulae in columns Q & R can be deleted.
The formulae should be copied down the columns to get the rest of the results.
If there are two results, as there are in the 10th largest value (920), it only tells you the header of one of them.

 

[mhenk]

That works great. Any way to pull duplicates?

 

[p45cal]

Excel version?

 

[mhenk]

I'm using 2010.

 

[p45cal]

Excel Workbook
	O	P	Q	R	S	T
12				1	2	3
13	1	980	4	Header5
14	2	965	0	Header1
15	3	963	0	Header1
16	4	960	2	Header3
17	5	959	1	Header2
18	6	954	2	Header3
19	7	943	2	Header3
20	8	925	1	Header2
21	9	922	3	Header4
22	10	920	2	Header3	Header1
Sheet

R13 copied across to column T, then the three cells copied down to row 22.
Uses the same data as in my first response.
You'll need the 1,2,3 in row 12.