# Other formula than Index match match?

#### gaftalik

##### Well-known Member
Hello !

Is there any formula which can easier replace this index match formula having more than 3 products in a column in order to return the total contribution of each item?

Thank you !
Book1
DEFGHIJK
4
5PizzaMarginQtyContributiontotal
6a17931934067pizza79712
7b9151816470Sandwiches32040
8c134534035Pies48163
9d7801511700
10e11201213440
11total79712
12Sandwiches
13g174411744
14h110077700
15i1707813656
16j178858940
17total32040
18Pies
19k12311012310
20l122567350
21m1892611352
22n1197910773
23o106366378
24total48163
25
26
27
Sheet1

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### Joe4

You have a formula that works, why do you want to simplify it?

I don't know that you can make it simpler, but if you just want that cell's formula to look nicer, you could try writing the function as a UDF in VBA. They you would just have the function name and parameters displayed in the cell's formula instead of something as complex as Index, Match, Match.

#### Zack Barresse

##### MrExcel MVP
another approach, although it would require you to change your formatting slightly, is using the DGET fx...
Book5
ABCDEFGHIJKLMN
1PizzaMarginQtyContributionSandwichesMarginQtyContributionPiesMarginQtyContributionPizza
2a1,7931934,067g1,74411,744k1,2311012,310PizzaTotal79,712
3b9151816,470h1,10077,700l1,22567,350Sandwiches
4c1,34534,035i1,707813,656m1,892611,352SandwichesTotal32,040
5d7801511,700j1,78858,940n1,197910,773Pies
6e1,1201213,440o1,06366,378PiesTotal48,163
7PizzaTotal79,712SandwichesTotal32,040PiesTotal48,163
Sheet1

kind of a give/take method, but the formula is shortened up considerably.

HTH

##### MrExcel MVP
gaftalik said:
...Is there any formula which can easier replace this index match formula having more than 3 products in a column in order to return the total contribution of each item?...

Easier in what sense?

#### gaftalik

##### Well-known Member
Hi ,

I meant easier in a way to simplify the lookup array in index and match , in order to drag the formula down in case i have over 100 products.
As you see in my case i am obliged to indicate the array and the lookup array for each formula.
Could that happen ?

Data should be under one column.

Thank you very much .

Replies
3
Views
254
Replies
0
Views
114
Replies
6
Views
4K
Replies
3
Views
297
Replies
1
Views
213

1,186,164
Messages
5,956,309
Members
438,246
Latest member
Deelea

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