Match name and multiply base on criteria

[nparrillo]

Looking to match names between two columns and then multiply a value by 1 or -1 based on Y or N criteria. Many more names will be added so it needs to be scalable.

 

[awoohaw]

Not sure what you mean by scalable, the formula can be copied down. But, try this, and you may need to reverse signs in the formula as you are not specific on what value is to be times 1 or times -1.:

Book1
	A	B	C	D	E	F	G
21
22		Name	(Y/N)		Name	Count	Formula
23		Sally	Y		Sally	2	-2
24		Mark	N		Mark	3	3
25		James	Y		Mark	3	3
26		Sue	N		Mark	3	3
27		Tom	N		Sue	4	4
28					Sally	2	-2
29					Tom	4	4
Sheet1

Cell Formulas
Range	Formula
G23:G29	G23	=IF(VLOOKUP(E23,$B$23:$C$27,2,0)="Y",-1,1)*F23

 

[nparrillo]

Not sure what you mean by scalable, the formula can be copied down. But, try this, and you may need to reverse signs in the formula as you are not specific on what value is to be times 1 or times -1.:

Book1
	A	B	C	D	E	F	G
21
22		Name	(Y/N)		Name	Count	Formula
23		Sally	Y		Sally	2	-2
24		Mark	N		Mark	3	3
25		James	Y		Mark	3	3
26		Sue	N		Mark	3	3
27		Tom	N		Sue	4	4
28					Sally	2	-2
29					Tom	4	4
Sheet1

Cell Formulas
Range	Formula
G23:G29	G23	=IF(VLOOKUP(E23,$B$23:$C$27,2,0)="Y",-1,1)*F23

Is this immune to me adding or moving columns? Any way with sumifs?

 

[awoohaw]

It is not immune from moving columns in the lookup table. The Name must always be the first column and the (Y/N) must always be the second.
If you want to move either or both of these columns then you should use the following:

=Index($C$23:$C$27,match(e23,$B$23:$B$27,0))

(I eyeballed this and did not test it out before typing, but I hope you get the syntax if you have an error returned).

The columns must always be of the same length.

As to SUMIFS, i'm not sure what you mean. What do you want to sum, and based on what criteria? But, really that is a separate question that should probably be put forth in a new post, your original post is based on name matching and getting a new value not getting a summarization based on matching names in a column.