Index Match from a SUM

[barney_t]

Hi,

I am trying with no success to find the product with the largest week on week gain without using a variance column.
Therefore the formula is in one cell having looked at all the individual sums on each row.
In the attached I have left in the variance column so that you can easily see that the result i am after is product8. I would also need to repeat for the 2nd largest unit gain (so Large function somewhere in the formula would be good).

I assume the Index would be the product column but I cant work out what would go with the Match to make it work.
I thought this would be a fairly common task within excel but i cant seem to find a tutorial on it. E.G from a calculation of data give the best seller.
I have been working at this for days so any help would be much appreciated.
I am new to the forum so apologies if i have not been clear in my request.
Many Thanks
Barney

Book1
	A	B	C	D
1	product	units wk1	units wk2	var
2	product1	2	5	3
3	product2	2	5	3
4	product3	4	5	1
5	product4	5	5	0
6	product5	8	5	-3
7	product6	9	5	-4
8	product7	10	5	-5
9	product8	0	5	5
10	product9	1	5	4
11	product10	6	5	-1
Sheet1

Cell Formulas
Range	Formula
D2:D11	D2	=C2-B2

 

[Fluff]

Hi & welcome to MrExcel.
Why don't you want to keep the variance column? It will make the formula far more efficient than getting rid of it.

 

[barney_t]

Hi Fluff
In essence, on the actual data set i am working with, there are a lot more rows and i only need the top few product names so my thought was to only have a few formulas rather than one for every row and be pulling from them all.

kind regards
barney

 

[Fluff]

You can use

+Fluff 1.xlsm
	A	B	C	D	E	F
1	product	units wk1	units wk2	var
2	product1	2	5	3		product8
3	product2	2	5	3		product9
4	product3	4	5	1		product1
5	product4	5	5	0		product2
6	product5	8	5	-3		product3
7	product6	9	5	-4		product4
8	product7	10	5	-5		product10
9	product8	0	5	5		product5
10	product9	1	5	4		product6
11	product10	6	5	-1		product7
Main

Cell Formulas
Range	Formula
D2:D11	D2	=C2-B2
F2:F11	F2	=INDEX($A$2:$A$11,AGGREGATE(15,6,(ROW($A$2:$A$11)-ROW($A$2)+1)/(($C$2:$C$11-$B$2:$B$11)=LARGE($C$2:$C$11-$B$2:$B$11,ROWS(F$2:F2)))/(ISNA(MATCH(A$2:A11,F$1:F1,0))),1))

But it may not be very fast if you have a lot of data.

 

[barney_t]

Hi Fluff,
You're a genius. Thank you so much for your help it works perfectly.
Many Thanks

 

[Fluff]

You're welcome & thanks for the feedback.