# Index Match from a SUM

#### barney_t

##### New Member
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
ABCD
1productunits wk1units wk2var
2product1253
3product2253
4product3451
5product4550
6product585-3
7product695-4
8product7105-5
9product8055
10product9154
11product1065-1
Sheet1
Cell Formulas
RangeFormula
D2:D11D2=C2-B2

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Fluff

##### MrExcel MVP, Moderator
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

##### New Member
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

##### MrExcel MVP, Moderator
You can use
+Fluff 1.xlsm
ABCDEF
1productunits wk1units wk2var
2product1253product8
3product2253product9
4product3451product1
5product4550product2
6product585-3product3
7product695-4product4
8product7105-5product10
9product8055product5
10product9154product6
11product1065-1product7
Main
Cell Formulas
RangeFormula
D2:D11D2=C2-B2
F2:F11F2=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

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

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
9
Views
202
Replies
4
Views
110
Replies
11
Views
301
Replies
1
Views
194
Replies
3
Views
84

1,127,781
Messages
5,626,837
Members
416,204
Latest member
Perzo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back