Calculate 2 numbers based on criteria

[Russk68]

Hi!
I need a formula in column W. 34,25 and 20 are the results needed.
Column V is a unique list from column P.
The formula would index column P and match the fixture type in column V and then subtract the 2 values in column R
TIA!

1WBV2.11.8 copy.xlsm
	P	Q	R	S	T	U	V	W
2	Fixture Type	Universe	Address	Color		Fix. ID.	Fixture Type
3	Mac Viper Profile 16 bit Extended	1	001	ffffff		101	Mac Viper Profile 16 bit Extended	34
4	Mac Viper Profile 16 bit Extended	1	035	ffffff		201	Mac Aura Extended - Colour Calibration Off	25
5	Mac Viper Profile 16 bit Extended	1	069	ffffff		301	Sharpy Vector Lamp on	20
6	Mac Viper Profile 16 bit Extended	1	103	ffffff		0
7	Mac Viper Profile 16 bit Extended	1	137	ffffff		0
8	Mac Viper Profile 16 bit Extended	1	171	ffffff		0
9	Mac Viper Profile 16 bit Extended	2	001	ffffff		0
10	Mac Viper Profile 16 bit Extended	2	035	ffffff		0
11	Mac Viper Profile 16 bit Extended	2	069	ffffff		0
12	Mac Viper Profile 16 bit Extended	2	103	ffffff		0
13	Mac Viper Profile 16 bit Extended	2	137	ffffff		0
14	Mac Viper Profile 16 bit Extended	2	171	ffffff		0
15	Mac Aura Extended - Colour Calibration Off	1	205	ffffff		0
16	Mac Aura Extended - Colour Calibration Off	1	230	ffffff		0
17	Mac Aura Extended - Colour Calibration Off	1	255	ffffff		0
18	Mac Aura Extended - Colour Calibration Off	1	280	ffffff		0
19	Mac Aura Extended - Colour Calibration Off	1	305	ffffff		0
20	Mac Aura Extended - Colour Calibration Off	1	330	ffffff		0
21	Mac Aura Extended - Colour Calibration Off	1	355	ffffff		0
22	Mac Aura Extended - Colour Calibration Off	1	380	ffffff		0
23	Mac Aura Extended - Colour Calibration Off	2	205	ffffff		0
24	Mac Aura Extended - Colour Calibration Off	2	230	ffffff		0
25	Mac Aura Extended - Colour Calibration Off	2	255	ffffff		0
26	Mac Aura Extended - Colour Calibration Off	2	280	ffffff		0
27	Mac Aura Extended - Colour Calibration Off	3	201	ffffff		0
28	Mac Aura Extended - Colour Calibration Off	3	226	ffffff		0
29	Mac Aura Extended - Colour Calibration Off	2	355	ffffff		0
30	Mac Aura Extended - Colour Calibration Off	2	380	ffffff		0
31	Mac Aura Extended - Colour Calibration Off	2	405	ffffff		0
32	Mac Aura Extended - Colour Calibration Off	2	430	ffffff		0
33	Mac Aura Extended - Colour Calibration Off	3	001	ffffff		0
34	Mac Aura Extended - Colour Calibration Off	3	026	ffffff		0
35	Mac Aura Extended - Colour Calibration Off	3	051	ffffff		0
36	Mac Aura Extended - Colour Calibration Off	3	076	ffffff		0
37	Mac Aura Extended - Colour Calibration Off	3	101	ffffff		0
38	Mac Aura Extended - Colour Calibration Off	3	126	ffffff		0
39	Mac Aura Extended - Colour Calibration Off	3	151	ffffff		0
40	Mac Aura Extended - Colour Calibration Off	3	176	ffffff
41	Mac Aura Extended - Colour Calibration Off	4	026	ffffff
42	Mac Aura Extended - Colour Calibration Off	4	051	ffffff
43	Mac Aura Extended - Colour Calibration Off	4	076	ffffff
44	Mac Aura Extended - Colour Calibration Off	4	101	ffffff
45	Mac Aura Extended - Colour Calibration Off	4	126	ffffff
46	Mac Aura Extended - Colour Calibration Off	4	151	ffffff
47	Mac Aura Extended - Colour Calibration Off	4	381	ffffff
48	Mac Aura Extended - Colour Calibration Off	4	406	ffffff
49	Mac Aura Extended - Colour Calibration Off	4	431	ffffff
50	Mac Aura Extended - Colour Calibration Off	4	456	ffffff
51	Sharpy Vector Lamp on	4	221	ffffff
52	Sharpy Vector Lamp on	4	241	ffffff
53	Sharpy Vector Lamp on	4	261	ffffff
54	Sharpy Vector Lamp on	4	281	ffffff
55	Sharpy Vector Lamp on	4	301	ffffff
56	Sharpy Vector Lamp on	4	321	ffffff
57	Sharpy Vector Lamp on	4	341	ffffff
58	Sharpy Vector Lamp on	4	361	ffffff
59	CuePix Strip Tri 7 channel	3	251	ffffff
60	CuePix Strip Tri 7 channel	3	258	ffffff
61	CuePix Strip Tri 7 channel	3	265	ffffff
62	CuePix Strip Tri 7 channel	4	176	ffffff
63	CuePix Strip Tri 7 channel	4	183	ffffff
64	Mac 700 Profile Extended	3	272	ffffff
65	Mac 700 Profile Extended	3	303	ffffff
66	Mac 700 Profile Extended	3	334	ffffff
67	Mac 700 Profile Extended	4	190	ffffff
68	Base Hazer 00	4	481	ffffff
69	Base Hazer 00	4	483	ffffff
MA2Import

 

[jasonb75]

Given that the fixture types appear many times and you only appear to be looking at the first 2 instances each time, are we able to assume that this is a constant or is there another criteria for identifying the correct 2 rows?

edit:-

Adding in one quick formula, there are many ways to get the same result.

=SUMPRODUCT(INDEX(FILTER($R$3:$R$69,$P$3:$P$69=V3),{1,2}),{-1,1})

 

[Fluff]

Do you always want to subtract the 1st value from the 2nd?

 

[Fluff]

If it is always the 1st subtracted from the 2nd, then try

=LET(f,FILTER($R$3:$R$69,$P$3:$P$69=V3),INDEX(f,2)-INDEX(f,1))

 

[Russk68]

Given that the fixture types appear many times and you only appear to be looking at the first 2 instances each time, are we able to assume that this is a constant or is there another criteria for identifying the correct 2 rows?

edit:-

Adding in one quick formula, there are many ways to get the same result.

=SUMPRODUCT(INDEX(FILTER($R$3:$R$69,$P$3:$P$69=V3),{1,2}),{-1,1})

This is a constant.
I'll give it a try. Thank you!

 

[Russk68]

Do you always want to subtract the 1st value from the 2nd?

Correct

 

[Russk68]

If it is always the 1st subtracted from the 2nd, then try

=LET(f,FILTER($R$3:$R$69,$P$3:$P$69=V3),INDEX(f,2)-INDEX(f,1))

I'll try this too.
Thank you!

 

[Russk68]

If it is always the 1st subtracted from the 2nd, then try

=LET(f,FILTER($R$3:$R$69,$P$3:$P$69=V3),INDEX(f,2)-INDEX(f,1))

Works great!
Thank you Fluff!!

 

[Fluff]

Glad we could help & thanks for the feedback.