Lookup Value is Between Range 1 and Range 2

[Corissa]

I have the following data:
Col A = Start Date
Col B = End Date
Col C = Week Number

Is there a way to do a lookup function to display the Week Number if a user
enters a date that is between Col A and Col B?

[cscorp]

Corissa,

I don't think the vlookup as designed can help you to do that. I designed a small function called "WeekFinder". The spreadsheet attached is an example on how to use it. The code is unprotected and documented. Please, verify and test it. It is in a zip file because this system do not allow Excel Attachments.

Let me know if that helps.

regards

Juan Carlos

[bj]

One way to do it would be to use sumproduct
=sumproduct(--(A1:A100<=datevalue(D1)),--(B1:B100>=Datevalue(D1)),C1:C100)

"Corissa" wrote:

> I have the following data:
> Col A = Start Date
> Col B = End Date
> Col C = Week Number
>
> Is there a way to do a lookup function to display the Week Number if a user
> enters a date that is between Col A and Col B?

[Corissa]

I'm looking for a "lookup" function that will display the contents of col C
if the date entered is between the start date and the end date. I'm not
following how the sumproduct will get me to the correct results. Thanks for
your help!

"bj" wrote:

> One way to do it would be to use sumproduct
> =sumproduct(--(A1:A100<=datevalue(D1)),--(B1:B100>=Datevalue(D1)),C1:C100)
>
> "Corissa" wrote:
>
> > I have the following data:
> > Col A = Start Date
> > Col B = End Date
> > Col C = Week Number
> >
> > Is there a way to do a lookup function to display the Week Number if a user
> > enters a date that is between Col A and Col B?