Help with Formula (VLOOKUP)

[Trowo]

I am trying to determine if certain we a purchase a particular stock occurred between multiple time periods- see pic, but in short I want to search Column L for B2, and check if the date in A2 falls between any of the date ranges for the occurrences of B2. Sorry for the illustration I couldn't get it to paste correctly.

I hope I explained that right- thanks for any help.

A

B

C

D

E

F

G

H

I

J

K

L

M

N

DATE 4/23/2007 5/18/2007 6/21/2007 7/19/2007 8/27/2007 9/21/2007 10/18/2007 12/3/2007 12/26/2007 2/21/2008 3/20/2008 4/17/2008 4/28/2008 5/23/2008 6/20/2008 7/18/2008 8/21/2008 9/5/2008 <colgroup><col></colgroup><tbody> </tbody>

SYM AAPL AAPL AAPL AAPL AAPL AAPL AAPL AAPL AAPL AAPL AAPL AAPL AAPL AAPL AAPL AAPL AAPL AAPL <colgroup><col></colgroup><tbody> </tbody>

SYM AAPL AAPL AAPL ABBV ABT ACGL <colgroup><col></colgroup><tbody> </tbody>

BEGIN 7/3/2007 6/2/2008 8/22/2012 8/9/2013 5/29/2007 5/31/2007 <colgroup><col></colgroup><tbody> </tbody>

END 7/3/2007 6/2/2008 8/22/2012 8/9/2013 5/29/2007 5/31/2007 <colgroup><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[clawlan]

Well, your ranges are just a day each, so your results are all false, but you can do this easily with VLOOKUP and nested IF statements.

=IF(VLOOKUP(B2,$L$2:$N$7,2,0)<=A2,IF(VLOOKUP(B2,$L$2:$N$7,3,0)>=A2,"yes","no"),"no")

 

[Aladin Akyurek]

In C2 control+shift+enter, not just enter, and copy down:

=ISNUMBER(MATCH($B2,IF($A2>=$M$2:$M$7,IF(A2<=$N$2:$N$7,$L$2:$L$7)),0))

 

[Trowo]

Thanks this worked perfectly!

 

[Trowo]

@Clawlan would you be able to share with me a formula to do a count how many times column a has a "yes" and column be has a 1 in the same row?

 

[Aladin Akyurek]

@Clawlan would you be able to share with me a formula to do a count how many times column a has a "yes" and column be has a 1 in the same row?

Here:

=COUNTIFS(A:A,"yes",B:B,1)

 

[Trowo]

Here:

=COUNTIFS(A:A,"yes",B:B,1)

Thats great-Thankyou

 

[Aladin Akyurek]

Thats great-Thankyou

You are welcome.