If value is contained in array

[AndrewMD]

Hi Helpful folks of MrExcel, hope you can help me with this problem I've been stuck on.

I have an array of values in one column and in two other columns I have the year and an amount.

What I'm trying to so is create a third column (H) and say if year is contained in array *2, otherwise *1. etc

This won't work for me, not sure what I'm doing wrong! {=IF(F2=$A$2:$A$6,G2*2,G2*1)}

Thanks!

 

[etaf]

=IF(countif($A$2:$A$6,F2)>0,G2*2,G2)
copy down

Book1
	A	B	C	D	E	F	G	H
1
2	2					1	101	101
3	4					2	102	204
4	6					3	103	103
5	8					4	104	208
6	10					5	105	105
7						6	106	212
8						7	107	107
9						8	108	216
10						9	109	109
11						10	110	220
12						11	111	111
13						12	112	112
14						13	113	113
Sheet1

Cell Formulas
Range	Formula
H2:H14	H2	=IF(COUNTIF($A$2:$A$6,F2)>0,G2*2,G2)

 

[Kerryx]

Book1
	A	B	C	D	E	F	G	H
1	Array					Year	Amt	Tot
2	2					1	100	100
3	4					2	100	200
4	6					3	100	100
5	8					4	100	200
6	10					5	100	100
7						6	100	200
8						7	100	100
9						8	100	200
10						9	100	100
11						10	100	200
Sheet1

Cell Formulas
Range	Formula
H2:H11	H2	=IF(COUNTIF($A$2:$A$6,F2)>0,G2*2,G2*1)

 

[Rick Rothstein]

You have been given two formulas that require you to copy them down; but, since you are using XL365, you can place this single formula in cell H2 and it will automatically spill the results down for you...

=IF(COUNTIF(A2:A6,F2:F11),2*G2:G11,G2:G11)

 

[AndrewMD]

Thanks everyone, really interesting and helpful!

 

[Scott Huish]

You don't really need the IF
=((COUNTIF(A2:A6,F2:F11)>0)+1)*G2:G11

 

[AndrewMD]

Simpler the better!