Excel formula for automating calculation

[Tripleseas]

Hello community,

I want to automate some work in excel that need to be done.
the data below is just a preview of a large data set that I need to work on so i can't do it manually.

is there any formula that can do the following :

i want to calculate the number for each categorie ( CASH1, CASH2, CASH3 and so on : there is alot of columns that i have ) based on :
- for each category ( formula to be put on E4, i want the formula the lookup the products name and the name of the category in the table below and extract the percentage related, then multiply the total number of operations for that product in that percenatge )

I did it with the formula below but it's manual! is there any way that i can automate this.

thanks all

test.xlsx
	B	C	D	E	F	G
2				CASH1	CASH2	CASH3
3	Products		Total operations	Number	Number	Number
4		X1	2,336,784	9,732	120,397	13,150
5		X2	1,762,053	14,219
6		X3	1,171,839
7		X4	971,453
8		X5	108,421
9		X6	130,000
10
11
12
13
14
15
16			Products	%
17		CASH1	X1	0.42%
18		CASH1	X2	0.81%
19		CASH1	X3	0.61%
20		CASH1	X4	0.96%
21		CASH1	X5	0.92%
22		CASH1	X6	0.99%
23		CASH2	X1	5.15%
24		CASH2	X2	5.94%
25		CASH2	X3	4.36%
26		CASH2	X4	7.27%
27		CASH2	X5	4.70%
28		CASH2	X6	4.93%
29		CASH3	X1	0.56%
30		CASH3	X2	0.76%
31		CASH3	X3	0.33%
32		CASH3	X4	0.78%
33		CASH3	X5	0.73%
34		CASH3	X6	0.66%
GLOBAL

Cell Formulas
Range	Formula
F4	F4	=D4*E23
G4	G4	=D4*E29
E4:E5	E4	=D4*E17

 

[etaf]

does
=INDEX($D$16:$D$33,MATCH($B3&D$1,$C$16:$C$33&$B$16:$B$33,0))*$C3
work for you ?

Book1
	A	B	C	D	E	F
1				CASH1	CASH2	CASH3
2	Products		Total operations	Number	Number	Number
3		X1	2336784.17	9,732	120,397	13,150
4		X2	1762053.18	14,219	104,684	13,409
5		X3	1171838.99	7,160	51,137	3,911
6		X4	971453.164	9,278	70,586	7,568
7		X5	108421.034	999	5,095	797
8		X6	130000	1,284	6,410	863
9
10
11
12
13
14
15			Products	%
16		CASH1	X1	0.42%
17		CASH1	X2	0.81%
18		CASH1	X3	0.61%
19		CASH1	X4	0.96%
20		CASH1	X5	0.92%
21		CASH1	X6	0.99%
22		CASH2	X1	5.15%
23		CASH2	X2	5.94%
24		CASH2	X3	4.36%
25		CASH2	X4	7.27%
26		CASH2	X5	4.70%
27		CASH2	X6	4.93%
28		CASH3	X1	0.56%
29		CASH3	X2	0.76%
30		CASH3	X3	0.33%
31		CASH3	X4	0.78%
32		CASH3	X5	0.73%
33		CASH3	X6	0.66%
34
Sheet1

Cell Formulas
Range	Formula
D3:F8	D3	=INDEX($D$16:$D$33,MATCH($B3&D$1,$C$16:$C$33&$B$16:$B$33,0))*$C3

 

[Tripleseas]

does
=INDEX($D$16:$D$33,MATCH($B3&D$1,$C$16:$C$33&$B$16:$B$33,0))*$C3
work for you ?

Book1
	A	B	C	D	E	F
1				CASH1	CASH2	CASH3
2	Products		Total operations	Number	Number	Number
3		X1	2336784.17	9,732	120,397	13,150
4		X2	1762053.18	14,219	104,684	13,409
5		X3	1171838.99	7,160	51,137	3,911
6		X4	971453.164	9,278	70,586	7,568
7		X5	108421.034	999	5,095	797
8		X6	130000	1,284	6,410	863
9
10
11
12
13
14
15			Products	%
16		CASH1	X1	0.42%
17		CASH1	X2	0.81%
18		CASH1	X3	0.61%
19		CASH1	X4	0.96%
20		CASH1	X5	0.92%
21		CASH1	X6	0.99%
22		CASH2	X1	5.15%
23		CASH2	X2	5.94%
24		CASH2	X3	4.36%
25		CASH2	X4	7.27%
26		CASH2	X5	4.70%
27		CASH2	X6	4.93%
28		CASH3	X1	0.56%
29		CASH3	X2	0.76%
30		CASH3	X3	0.33%
31		CASH3	X4	0.78%
32		CASH3	X5	0.73%
33		CASH3	X6	0.66%
34
Sheet1

Cell Formulas
Range	Formula
D3:F8	D3	=INDEX($D$16:$D$33,MATCH($B3&D$1,$C$16:$C$33&$B$16:$B$33,0))*$C3

Thank you so much i managed to get solid results with it. I have applied it as an array formula then it worked
will keep applying to my real data and see if everything goes smooth.

thank so much for the help.

 

[etaf]

you are welcome, I so used now to using 365 version , i forget that older versions need the control/shift/enter to add the {} - 365 just knows its an array and works automatically -sorry about that

 

[Automatrix]

Power Query solution (in case you want to play around with that).

Load the lower table as connection only. Load the upper table, then merge them (Right Outer). Expand the CASH and percentage categories. Multiply the percent and total operations columns. This yields a data table that is easily loaded into a pivot table.

(Comma is the decimal point in my Excel.)

Book2
	F	G	H	I	J
2	Products	Total operations	Category	Percent	Multiplication
3	X1	2336784,17	CASH1	0,42%	9731,768361
4	X1	2336784,17	CASH2	5,15%	120397,0438
5	X2	1762053,181	CASH1	0,81%	14219,31272
6	X3	1171838,989	CASH1	0,61%	7159,580686
7	X4	971453,1639	CASH1	0,96%	9277,856614
8	X5	108421,0339	CASH1	0,92%	998,8441716
9	X6	130000	CASH1	0,99%	1284,408423
10	X2	1762053,181	CASH2	5,94%	104684,133
11	X3	1171838,989	CASH2	4,36%	51137,29699
12	X4	971453,1639	CASH2	7,27%	70585,79842
13	X5	108421,0339	CASH2	4,70%	5095,40515
14	X6	130000	CASH2	4,93%	6410,090878
15	X1	2336784,17	CASH3	0,56%	13150,16415
16	X2	1762053,181	CASH3	0,76%	13408,53248
17	X3	1171838,989	CASH3	0,33%	3910,7279
18	X4	971453,1639	CASH3	0,78%	7567,621542
19	X5	108421,0339	CASH3	0,73%	796,8294315
20	X6	130000	CASH3	0,66%	863,4734484
Sheet11