If statement using v-lookup

[eramirez148]

Is there a way to add a formula in column D that will result in one of the outcomes in column G if it matches the date between column B and column F only if column C is "active" and column A higher than 20% otherwise the results should be an empty cell.

A	B	C	D	E	F	G
	pos_date	Status	Results
1.00%​	1/1/2021	inactive			Look up list
2.00%​	1/2/2021	inactive			pos_date	list
3.00%​	1/3/2021	inactive			1/1/2021	A
4.00%​	1/4/2021	inactive			1/2/2021	B
5.00%​	1/5/2021	inactive			1/3/2021	C
-1.00%​	1/6/2021	inactive			1/4/2021	D
-2.00%​	1/7/2021​	inactive			1/5/2021	E
-3.00%​	1/8/2021​	inactive			1/6/2021	F
20.00%​	1/9/2021​	inactive
40.00%​	1/10/2021​	inactive
50.00%​	1/11/2021​	inactive
10.00%​	1/12/2021​	active
15.00%​	1/13/2021​	active
80.00%​	1/14/2021​	active

 

[ZMyrrh]

This worked for me! Let me know if this works for you!

=IFERROR(IF(AND(C2="Active",A2>20%),VLOOKUP(B2,$F$4:$G$9,2,FALSE),""),"")

 

[eramirez148]

If I want to adjust the formula to read everything below -20% do I just add a negative sign in front of the 20% and change the sign to <?

 

[ZMyrrh]

Yeah that should work! Does it not work?

 

[eramirez148]

Yes it does thank you!

 

[ZMyrrh]

Perfect!