Product to bring its ingredients with cost

[Zubair]

Hi experts.

I want to see in result tab ingredients and its cost samples shown below based on the selection of Grade Name in A1, from the data list shown below at last, please help with formula but not VBA code. Thanks in advance.

Result tab
Sample 1

SetJet 50 CD Bulk​
Track​	1​	1​	1​	1​	1​	1​	1​	1​	1​	1​
Component ID​	200018010​	200018011​	200018012​	100018015​	100018016​	100018028​	100018032​	400018001​	400018002​	400018009​
Component Name​	ZESSA-B010​	ZESSA-B011​	ZESSA-B012​	ZESSA-A015​	ZESSA-A016​	ZESSA-A028​	ZESSA-A032​	Blending​	Logistic​	S & H​
Cost PKR/Litre​	144.22​	25.39​	13.70​	24.91​	14.09​	14.87​	1.76​	9.25​	8.30​	3.11​

Sample 2

SetJet 50 CD 1 ltr​

Track​

2​

2​

2​

2​

2​

2​

2​

2​

2​

2​

2​

2​

2​

2​

2​

Component ID​

200018010​

200018011​

200018012​

100018015​

100018016​

100018028​

100018032​

400018001​

300018007​

300018015​

300018023​

300018031​

400018003​

400018002​

400018009​

Component Name​

ZESSA-B010​

ZESSA-B011​

ZESSA-B012​

ZESSA-A015​

ZESSA-A016​

ZESSA-A028​

ZESSA-A032​

Blending​

ZESSA-BT Plastic Bottle SetJet 50 CD 1 ltr​

ZESSA-LA Label F SetJet 50 CD 1 ltr​

ZESSA-LA Label B SetJet 50 CD 1 ltr​

ZESSA-CT Carton 12 x 1 SetJet 50 CD 1 ltr​

Filling 1 ltr​

Logistic​

S & H​

Cost PKR/Litre​

144.22​

25.39​

13.70​

24.91​

14.09​

14.87​

1.76​

9.25​

25.95​

4.15​

4.15​

25.95​

4.15​

8.30​

3.11​

Data

Track	Grade Name	Component Id	Component Name	Dosage %age	Cost PKR/Litre	MONTH	year 2
1​	SetJet 50 CD Bulk​	200018010​	ZESSA-B010​	75.00%​	144.22​	Jan​	A2021​
1​	SetJet 50 CD Bulk​	200018011​	ZESSA-B011​	13.00%​	25.39​	Jan​	A2021​
1​	SetJet 50 CD Bulk​	200018012​	ZESSA-B012​	6.00%​	13.70​	Jan​	A2021​
1​	SetJet 50 CD Bulk​	100018015​	ZESSA-A015​	3.00%​	24.91​	Jan​	A2021​
1​	SetJet 50 CD Bulk​	100018016​	ZESSA-A016​	1.50%​	14.09​	Jan​	A2021​
1​	SetJet 50 CD Bulk​	100018028​	ZESSA-A028​	1.00%​	14.87​	Jan​	A2021​
1​	SetJet 50 CD Bulk​	100018032​	ZESSA-A032​	0.50%​	1.76​	Jan​	A2021​
1​	SetJet 50 CD Bulk​	400018001​	Blending​	0.00%​	9.25​	Jan​	A2021​
1​	SetJet 50 CD Bulk​	400018002​	Logistic​	0.00%​	8.30​	Jan​	A2021​
1​	SetJet 50 CD Bulk​	400018009​	S & H​	0.00%​	3.11​	Jan​	A2021​
2​	SetJet 50 CD 1 ltr​	200018010​	ZESSA-B010​	75.00%​	144.22​	Jan​	A2021​
2​	SetJet 50 CD 1 ltr​	200018011​	ZESSA-B011​	13.00%​	25.39​	Jan​	A2021​
2​	SetJet 50 CD 1 ltr​	200018012​	ZESSA-B012​	6.00%​	13.70​	Jan​	A2021​
2​	SetJet 50 CD 1 ltr​	100018015​	ZESSA-A015​	3.00%​	24.91​	Jan​	A2021​
2​	SetJet 50 CD 1 ltr​	100018016​	ZESSA-A016​	1.50%​	14.09​	Jan​	A2021​
2​	SetJet 50 CD 1 ltr​	100018028​	ZESSA-A028​	1.00%​	14.87​	Jan​	A2021​
2​	SetJet 50 CD 1 ltr​	100018032​	ZESSA-A032​	0.50%​	1.76​	Jan​	A2021​
2​	SetJet 50 CD 1 ltr​	400018001​	Blending​	0.00%​	9.25​	Jan​	A2021​
2​	SetJet 50 CD 1 ltr​	300018007​	ZESSA-BT Plastic Bottle SetJet 50 CD 1 ltr​	0.00%​	25.95​	Jan​	A2021​
2​	SetJet 50 CD 1 ltr​	300018015​	ZESSA-LA Label F SetJet 50 CD 1 ltr​	0.00%​	4.15​	Jan​	A2021​
2​	SetJet 50 CD 1 ltr​	300018023​	ZESSA-LA Label B SetJet 50 CD 1 ltr​	0.00%​	4.15​	Jan​	A2021​
2​	SetJet 50 CD 1 ltr​	300018031​	ZESSA-CT Carton 12 x 1 SetJet 50 CD 1 ltr​	0.00%​	25.95​	Jan​	A2021​
2​	SetJet 50 CD 1 ltr​	400018003​	Filling 1 ltr​	0.00%​	4.15​	Jan​	A2021​
2​	SetJet 50 CD 1 ltr​	400018002​	Logistic​	0.00%​	8.30​	Jan​	A2021​
2​	SetJet 50 CD 1 ltr​	400018009​	S & H​	0.00%​	3.11​	Jan​	A2021​
3​	SetPlus 10W40 CF4 Bulk​	200018010​	ZESSA-B010​	67.00%​	129.35​	Jan​	A2021​
3​	SetPlus 10W40 CF4 Bulk​	200018011​	ZESSA-B011​	15.00%​	29.41​	Jan​	A2021​
3​	SetPlus 10W40 CF4 Bulk​	200018012​	ZESSA-B012​	7.00%​	16.05​	Jan​	A2021​
3​	SetPlus 10W40 CF4 Bulk​	100018009​	ZESSA-A009​	6.50%​	33.20​	Jan​	A2021​
3​	SetPlus 10W40 CF4 Bulk​	100018017​	ZESSA-A017​	1.50%​	7.37​	Jan​	A2021​
3​	SetPlus 10W40 CF4 Bulk​	100018035​	ZESSA-A035​	1.00%​	1.42​	Jan​	A2021​
3​	SetPlus 10W40 CF4 Bulk​	100018041​	ZESSA-A041​	1.00%​	1.88​	Jan​	A2021​
3​	SetPlus 10W40 CF4 Bulk​	100018045​	ZESSA-A045​	1.00%​	2.16​	Jan​	A2021​
3​	SetPlus 10W40 CF4 Bulk​	400018001​	Blending​	0.00%​	9.25​	Jan​	A2021​
3​	SetPlus 10W40 CF4 Bulk​	400018002​	Logistic​	0.00%​	8.30​	Jan​	A2021​
3​	SetPlus 10W40 CF4 Bulk​	400018009​	S & H​	0.00%​	3.11​	Jan​	A2021​

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	Track	Grade Name	Component Id	Component Name	Dosage %age	Cost PKR/Litre	MONTH	year 2
2	1	SetJet 50 CD Bulk	200018010	ZESSA-B010	75.00%	144.22	Jan	A2021
3	1	SetJet 50 CD Bulk	200018011	ZESSA-B011	13.00%	25.39	Jan	A2021
4	1	SetJet 50 CD Bulk	200018012	ZESSA-B012	6.00%	13.7	Jan	A2021
5	1	SetJet 50 CD Bulk	100018015	ZESSA-A015	3.00%	24.91	Jan	A2021
6	1	SetJet 50 CD Bulk	100018016	ZESSA-A016	1.50%	14.09	Jan	A2021
7	1	SetJet 50 CD Bulk	100018028	ZESSA-A028	1.00%	14.87	Jan	A2021
8	1	SetJet 50 CD Bulk	100018032	ZESSA-A032	0.50%	1.76	Jan	A2021
9	1	SetJet 50 CD Bulk	400018001	Blending	0.00%	9.25	Jan	A2021
10	1	SetJet 50 CD Bulk	400018002	Logistic	0.00%	8.3	Jan	A2021
11	1	SetJet 50 CD Bulk	400018009	S & H	0.00%	3.11	Jan	A2021
12	2	SetJet 50 CD 1 ltr	200018010	ZESSA-B010	75.00%	144.22	Jan	A2021
13	2	SetJet 50 CD 1 ltr	200018011	ZESSA-B011	13.00%	25.39	Jan	A2021
14	2	SetJet 50 CD 1 ltr	200018012	ZESSA-B012	6.00%	13.7	Jan	A2021
15	2	SetJet 50 CD 1 ltr	100018015	ZESSA-A015	3.00%	24.91	Jan	A2021
16	2	SetJet 50 CD 1 ltr	100018016	ZESSA-A016	1.50%	14.09	Jan	A2021
17	2	SetJet 50 CD 1 ltr	100018028	ZESSA-A028	1.00%	14.87	Jan	A2021
18	2	SetJet 50 CD 1 ltr	100018032	ZESSA-A032	0.50%	1.76	Jan	A2021
19	2	SetJet 50 CD 1 ltr	400018001	Blending	0.00%	9.25	Jan	A2021
20	2	SetJet 50 CD 1 ltr	300018007	ZESSA-BT Plastic Bottle SetJet 50 CD 1 ltr	0.00%	25.95	Jan	A2021
21	2	SetJet 50 CD 1 ltr	300018015	ZESSA-LA Label F SetJet 50 CD 1 ltr	0.00%	4.15	Jan	A2021
22	2	SetJet 50 CD 1 ltr	300018023	ZESSA-LA Label B SetJet 50 CD 1 ltr	0.00%	4.15	Jan	A2021
23	2	SetJet 50 CD 1 ltr	300018031	ZESSA-CT Carton 12 x 1 SetJet 50 CD 1 ltr	0.00%	25.95	Jan	A2021
24	2	SetJet 50 CD 1 ltr	400018003	Filling 1 ltr	0.00%	4.15	Jan	A2021
25	2	SetJet 50 CD 1 ltr	400018002	Logistic	0.00%	8.3	Jan	A2021
26	2	SetJet 50 CD 1 ltr	400018009	S & H	0.00%	3.11	Jan	A2021
27	3	SetPlus 10W40 CF4 Bulk	200018010	ZESSA-B010	67.00%	129.35	Jan	A2021
28	3	SetPlus 10W40 CF4 Bulk	200018011	ZESSA-B011	15.00%	29.41	Jan	A2021
29	3	SetPlus 10W40 CF4 Bulk	200018012	ZESSA-B012	7.00%	16.05	Jan	A2021
30	3	SetPlus 10W40 CF4 Bulk	100018009	ZESSA-A009	6.50%	33.2	Jan	A2021
31	3	SetPlus 10W40 CF4 Bulk	100018017	ZESSA-A017	1.50%	7.37	Jan	A2021
32	3	SetPlus 10W40 CF4 Bulk	100018035	ZESSA-A035	1.00%	1.42	Jan	A2021
33	3	SetPlus 10W40 CF4 Bulk	100018041	ZESSA-A041	1.00%	1.88	Jan	A2021
34	3	SetPlus 10W40 CF4 Bulk	100018045	ZESSA-A045	1.00%	2.16	Jan	A2021
35	3	SetPlus 10W40 CF4 Bulk	400018001	Blending	0.00%	9.25	Jan	A2021
36	3	SetPlus 10W40 CF4 Bulk	400018002	Logistic	0.00%	8.3	Jan	A2021
37	3	SetPlus 10W40 CF4 Bulk	400018009	S & H	0.00%	3.11	Jan	A2021
Data

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	SetJet 50 CD 1 ltr
2	Track	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2
3	Component ID	200018010	200018011	200018012	100018015	100018016	100018028	100018032	400018001	300018007	300018015	300018023	300018031	400018003	400018002	400018009
4	Component Name	ZESSA-B010	ZESSA-B011	ZESSA-B012	ZESSA-A015	ZESSA-A016	ZESSA-A028	ZESSA-A032	Blending	ZESSA-BT Plastic Bottle SetJet 50 CD 1 ltr	ZESSA-LA Label F SetJet 50 CD 1 ltr	ZESSA-LA Label B SetJet 50 CD 1 ltr	ZESSA-CT Carton 12 x 1 SetJet 50 CD 1 ltr	Filling 1 ltr	Logistic	S & H
5	Cost PKR/Litre	144.22	25.39	13.7	24.91	14.09	14.87	1.76	9.25	25.95	4.15	4.15	25.95	4.15	8.3	3.11
Results

Cell Formulas
Range	Formula
B2:S2	B2	=IFERROR(INDEX(Data!$A$2:$A$37,AGGREGATE(15,6,(ROW(Data!$B$2:$B$37)-ROW(Data!$B$2)+1)/(Data!$B$2:$B$37=$A$1),COLUMNS($B2:B2))),"")
B3:S3	B3	=IFERROR(INDEX(Data!$C$2:$C$37,AGGREGATE(15,6,(ROW(Data!$B$2:$B$37)-ROW(Data!$B$2)+1)/(Data!$B$2:$B$37=$A$1),COLUMNS($B3:B3))),"")
B4:S4	B4	=IFERROR(INDEX(Data!$D$2:$D$37,AGGREGATE(15,6,(ROW(Data!$B$2:$B$37)-ROW(Data!$B$2)+1)/(Data!$B$2:$B$37=$A$1),COLUMNS($B4:B4))),"")
B5:S5	B5	=IFERROR(INDEX(Data!$F$2:$F$37,AGGREGATE(15,6,(ROW(Data!$B$2:$B$37)-ROW(Data!$B$2)+1)/(Data!$B$2:$B$37=$A$1),COLUMNS($B5:B5))),"")

 

[Zubair]

Peter - Fantastic its working as I needed many thanks, just a question column J showing month which is currently Jan, if I want to pick Apr what change required ?

 

[Fluff]

Who is Peter?

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	SetJet 50 CD 1 ltr	Jan
2	Track	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2
3	Component ID	200018010	200018011	200018012	100018015	100018016	100018028	100018032	400018001	300018007	300018015	300018023	300018031	400018003	400018002	400018009
4	Component Name	ZESSA-B010	ZESSA-B011	ZESSA-B012	ZESSA-A015	ZESSA-A016	ZESSA-A028	ZESSA-A032	Blending	ZESSA-BT Plastic Bottle SetJet 50 CD 1 ltr	ZESSA-LA Label F SetJet 50 CD 1 ltr	ZESSA-LA Label B SetJet 50 CD 1 ltr	ZESSA-CT Carton 12 x 1 SetJet 50 CD 1 ltr	Filling 1 ltr	Logistic	S & H
5	Cost PKR/Litre	144.22	25.39	13.7	24.91	14.09	14.87	1.76	9.25	25.95	4.15	4.15	25.95	4.15	8.3	3.11
6
Results

Cell Formulas
Range	Formula
B2:R2	B2	=IFERROR(INDEX(Data!$A$2:$A$37,AGGREGATE(15,6,(ROW(Data!$B$2:$B$37)-ROW(Data!$B$2)+1)/(Data!$B$2:$B$37=$A$1)/(Data!$G$2:$G$37=$B$1),COLUMNS($B2:B2))),"")
B3:R3	B3	=IFERROR(INDEX(Data!$C$2:$C$37,AGGREGATE(15,6,(ROW(Data!$B$2:$B$37)-ROW(Data!$B$2)+1)/(Data!$B$2:$B$37=$A$1)/(Data!$G$2:$G$37=$B$1),COLUMNS($B3:B3))),"")
B4:R4	B4	=IFERROR(INDEX(Data!$D$2:$D$37,AGGREGATE(15,6,(ROW(Data!$B$2:$B$37)-ROW(Data!$B$2)+1)/(Data!$B$2:$B$37=$A$1)/(Data!$G$2:$G$37=$B$1),COLUMNS($B4:B4))),"")
B5:R5	B5	=IFERROR(INDEX(Data!$F$2:$F$37,AGGREGATE(15,6,(ROW(Data!$B$2:$B$37)-ROW(Data!$B$2)+1)/(Data!$B$2:$B$37=$A$1)/(Data!$G$2:$G$37=$B$1),COLUMNS($B5:B5))),"")

 

[Zubair]

Hi Fluff - Extremely sorry for incorrect address

 

[Zubair]

Fluff - thanks it ok.

 

[Fluff]

You're welcome & thanks for the feedback.