Other formula than Index match match?

[gaftalik]

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
	D	E	F	G	H	I	J	K
4
5		Pizza	Margin	Qty	Contribution		total
6		a	1793	19	34067	pizza	79712
7		b	915	18	16470	Sandwiches	32040
8		c	1345	3	4035	Pies	48163
9		d	780	15	11700
10		e	1120	12	13440
11		total			79712
12		Sandwiches
13		g	1744	1	1744
14		h	1100	7	7700
15		i	1707	8	13656
16		j	1788	5	8940
17		total			32040
18		Pies
19		k	1231	10	12310
20		l	1225	6	7350
21		m	1892	6	11352
22		n	1197	9	10773
23		o	1063	6	6378
24		total			48163
25
26
27
Sheet1

 

[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]

another approach, although it would require you to change your formatting slightly, is using the DGET fx...

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Pizza	Margin	Qty	Contribution	Sandwiches	Margin	Qty	Contribution	Pies	Margin	Qty	Contribution	Pizza
2	a	1,793	19	34,067	g	1,744	1	1,744	k	1,231	10	12,310	PizzaTotal	79,712
3	b	915	18	16,470	h	1,100	7	7,700	l	1,225	6	7,350	Sandwiches
4	c	1,345	3	4,035	i	1,707	8	13,656	m	1,892	6	11,352	SandwichesTotal	32,040
5	d	780	15	11,700	j	1,788	5	8,940	n	1,197	9	10,773	Pies
6	e	1,120	12	13,440					o	1,063	6	6,378	PiesTotal	48,163
7	PizzaTotal			79,712	SandwichesTotal			32,040	PiesTotal			48,163
Sheet1

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

HTH

 

[Aladin Akyurek]

...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]

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 .