Other formula than Index match match?

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
518
Office Version
  1. 2016
Platform
  1. Windows
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? :rolleyes:

Thank you ! (y)
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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,886
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
Joined
Feb 6, 2003
Messages
518
Office Version
  1. 2016
Platform
  1. Windows
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 .
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,164
Messages
5,768,561
Members
425,481
Latest member
ihumanl

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top