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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DATE[TABLE="width: 75"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]4/23/2007[/TD]
[/TR]
[TR]
[TD="align: right"]5/18/2007[/TD]
[/TR]
[TR]
[TD="align: right"]6/21/2007[/TD]
[/TR]
[TR]
[TD="align: right"]7/19/2007[/TD]
[/TR]
[TR]
[TD="align: right"]8/27/2007[/TD]
[/TR]
[TR]
[TD="align: right"]9/21/2007[/TD]
[/TR]
[TR]
[TD="align: right"]10/18/2007[/TD]
[/TR]
[TR]
[TD="align: right"]12/3/2007[/TD]
[/TR]
[TR]
[TD="align: right"]12/26/2007[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2008[/TD]
[/TR]
[TR]
[TD="align: right"]3/20/2008[/TD]
[/TR]
[TR]
[TD="align: right"]4/17/2008[/TD]
[/TR]
[TR]
[TD="align: right"]4/28/2008[/TD]
[/TR]
[TR]
[TD="align: right"]5/23/2008[/TD]
[/TR]
[TR]
[TD="align: right"]6/20/2008[/TD]
[/TR]
[TR]
[TD="align: right"]7/18/2008[/TD]
[/TR]
[TR]
[TD="align: right"]8/21/2008[/TD]
[/TR]
[TR]
[TD="align: right"]9/5/2008[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]SYM[TABLE="width: 45"]
<colgroup><col></colgroup><tbody>[TR]
[TD]AAPL[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]SYM
[TABLE="width: 53"]
<colgroup><col></colgroup><tbody>[TR]
[TD]AAPL[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[/TR]
[TR]
[TD]AAPL[/TD]
[/TR]
[TR]
[TD]ABBV[/TD]
[/TR]
[TR]
[TD]ABT[/TD]
[/TR]
[TR]
[TD]ACGL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]BEGIN
[TABLE="width: 68"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]7/3/2007[/TD]
[/TR]
[TR]
[TD="align: right"]6/2/2008[/TD]
[/TR]
[TR]
[TD="align: right"]8/22/2012[/TD]
[/TR]
[TR]
[TD="align: right"]8/9/2013[/TD]
[/TR]
[TR]
[TD="align: right"]5/29/2007[/TD]
[/TR]
[TR]
[TD="align: right"]5/31/2007[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]END[TABLE="width: 68"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"]7/3/2007[/TD]
[/TR]
[TR]
[TD="align: right"]6/2/2008[/TD]
[/TR]
[TR]
[TD="align: right"]8/22/2012[/TD]
[/TR]
[TR]
[TD="align: right"]8/9/2013[/TD]
[/TR]
[TR]
[TD="align: right"]5/29/2007[/TD]
[/TR]
[TR]
[TD="align: right"]5/31/2007[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.