List price from data

[Zubair]

Hi experts,

I am unable to bring List price & Cost in the tab result from the tab data contained 3 different criteria please help to bring formula to apply in B4 & B5


Result

Year​	2021​
Price change​	Feb​
SKU​	SetJet 50 CD 1 ltr​
List price​	700​
Cost​	423​

Data

Year​	Month​	Customer Name​	SKU​	Volume​	Invoice​	COGS Val​	GM Value​	List Price​	Unit Cost​
2021​	Jan​	Customer 1​	SetJet 50 CD 1 ltr​	6,887​	3,202,455​	2,231,196​	971,259​	465.00​	323.97​
2021​	Jan​	Customer 1​	SetPlus 10W40 CF4 1 ltr​	679​	316,075​	206,219​	109,856​	475.00​	303.71​
2021​	Jan​	Customer 1​	SetUltra 5W30 CH4 1 ltr​	428​	280,340​	140,064​	140,276​	655.00​	327.25​
2021​	Jan​	Customer 2​	SetJet 50 CD Bulk​	4,000​	768,000​	1,038,471​	(270,471)​	400.00​	259.62​
2021​	Jan​	Customer 2​	SetJet 50 CD 1 ltr​	913​	203,782​	295,787​	(92,005)​	465.00​	323.97​
2021​	Jan​	Customer 2​	SetPlus 10W40 CF4 1 ltr​	81​	18,468​	24,600​	(6,132)​	475.00​	303.71​
2021​	Jan​	Customer 2​	SetUltra 5W30 CH4 1 ltr​	52​	15,668​	17,017​	(1,350)​	655.00​	327.25​
2021​	Feb​	Customer 2​	SetJet 50 CD 1 ltr​	1,020​	398,000​	302,000​	96,000​	700.00​	423.00​
2021​	Feb​	Customer 2​	SetPlus 10W40 CF4 1 ltr​	120​	39,555​	20,120​	19,435​	530.00​	352.00​
2021​	Feb​	Customer 2​	SetUltra 5W30 CH4 1 ltr​	588​	195,000​	152,511​	42,489​	720.00​	351.00​

 

[Toadstool]

Hi Zubair,

If this is your Data

Zubair.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Year	Month	Customer Name	SKU	Volume	Invoice	COGS Val	GM Value	List Price	Unit Cost
2	2021	Jan	Customer 1	SetJet 50 CD 1 ltr	6,887	3,202,455	2,231,196	971,259	465.00	323.97
3	2021	Jan	Customer 1	SetPlus 10W40 CF4 1 ltr	679	316,075	206,219	109,856	475.00	303.71
4	2021	Jan	Customer 1	SetUltra 5W30 CH4 1 ltr	428	280,340	140,064	140,276	655.00	327.25
5	2021	Jan	Customer 2	SetJet 50 CD Bulk	4,000	768,000	1,038,471	(270,471)	400.00	259.62
6	2021	Jan	Customer 2	SetJet 50 CD 1 ltr	913	203,782	295,787	(92,005)	465.00	323.97
7	2021	Jan	Customer 2	SetPlus 10W40 CF4 1 ltr	81	18,468	24,600	(6,132)	475.00	303.71
8	2021	Jan	Customer 2	SetUltra 5W30 CH4 1 ltr	52	15,668	17,017	(1,350)	655.00	327.25
9	2021	Feb	Customer 2	SetJet 50 CD 1 ltr	1,020	398,000	302,000	96,000	700.00	423.00
10	2021	Feb	Customer 2	SetPlus 10W40 CF4 1 ltr	120	39,555	20,120	19,435	530.00	352.00
11	2021	Feb	Customer 2	SetUltra 5W30 CH4 1 ltr	588	195,000	152,511	42,489	720.00	351.00
12
Data

Then this should retrieve the matching data or return #N/A if no match is found.

Zubair.xlsx
	A	B
1	Year	2021
2	Price change	Feb
3	SKU	SetJet 50 CD 1 ltr
4	List price	700.00
5	Cost	423.00
Result

Cell Formulas
Range	Formula
B4	B4	=INDEX(Data!$I$2:$I$9999,MATCH(1,INDEX(($B$1=Data!$A$2:$A$9999)*($B$2=Data!$B$2:$B$9999)*($B$3=Data!$D$2:$D$9999),0,1),0))
B5	B5	=INDEX(Data!$J$2:$J$9999,MATCH(1,INDEX(($B$1=Data!$A$2:$A$9999)*($B$2=Data!$B$2:$B$9999)*($B$3=Data!$D$2:$D$9999),0,1),0))

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Year	Month	Customer Name	SKU	Volume	Invoice	COGS Val	GM Value	List Price	Unit Cost
2	2021	Jan	Customer 1	SetJet 50 CD 1 ltr	6,887	3,202,455	2,231,196	971,259	465	323.97
3	2021	Jan	Customer 1	SetPlus 10W40 CF4 1 ltr	679	316,075	206,219	109,856	475	303.71
4	2021	Jan	Customer 1	SetUltra 5W30 CH4 1 ltr	428	280,340	140,064	140,276	655	327.25
5	2021	Jan	Customer 2	SetJet 50 CD Bulk	4,000	768,000	1,038,471	-270,471	400	259.62
6	2021	Jan	Customer 2	SetJet 50 CD 1 ltr	913	203,782	295,787	-92,005	465	323.97
7	2021	Jan	Customer 2	SetPlus 10W40 CF4 1 ltr	81	18,468	24,600	-6,132	475	303.71
8	2021	Jan	Customer 2	SetUltra 5W30 CH4 1 ltr	52	15,668	17,017	-1,350	655	327.25
9	2021	Feb	Customer 2	SetJet 50 CD 1 ltr	1,020	398,000	302,000	96,000	700	423
10	2021	Feb	Customer 2	SetPlus 10W40 CF4 1 ltr	120	39,555	20,120	19,435	530	352
11	2021	Feb	Customer 2	SetUltra 5W30 CH4 1 ltr	588	195,000	152,511	42,489	720	351
Data

+Fluff 1.xlsm
	A	B
1	Year	2021
2	Price change	Feb
3	SKU	SetJet 50 CD 1 ltr
4	List price	700
5	Unit Cost	423
Result

Cell Formulas
Range	Formula
B4:B5	B4	=INDEX(Data!$E$2:$J$11,MATCH($B$1&"|"&$B$2&"|"&$B$3,Data!$A$2:$A$11&"|"&Data!$B$2:$B$11&"|"&Data!$D$2:$D$11,0),MATCH(A4,Data!$E$1:$J$1,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Zubair]

Thanks

Toadstool &​

Fluff​

Both Formulas are working fine.

 

[Fluff]

Glad we could help & thanks for the feedback.