Need help with Lookup between a range of dates

[Joyner]

I thought I was good at excel until I found this site [/img][/list]. I have been able to solve several problems searching other questions, but I have not been able to find an answer to my problem. It seems like it should be easy and it’s been driving me crazy. Any help would be greatly appreciated.

I have created a calendar and I want scheduled events to be automatically entered from a table. I have a table on the same sheet as the calendar that lists event titles and the "from" dates and "to" dates. I want a user to be able to enter just the from and to dates and have a formula enter the title of the event on each of the calendar dates (inclusive). So if the user enters "conference" from Jan 1 to Jan 7, the calendar should show "conference" on the calendar for Jan 1,2 3,4,5,6 and 7. Can a lookup or other formula look at and between the two dates?

(I hope this is clear, this is my first post and the first time I typed all of my question in, I somehow hit a wrong button and lost everything I typed, and I don’t type so fast).

So what would the formula be given the following cell references:

A1 = Calendar date
A2 = where the formula should go to return "conference" is my example.

A30:A40 from dates
B30:B40 to dates
C30:C40 event titles


I can rearrange the title and date colums to fit any table format if necessary.

I hope this is enough, I had typed in (the first time I tried to post) my lookup formulas previously used but they are not working correctly. For example I used =lookup(A1,A30:C40,3,false) and “true” but these don’t work right.

I am using excel 2002, and Windows 2000.

Thank you for any help you can give me.

Dean

 

[barry houdini]

Welcome to the board

Are your date ranges contiguous, i.e. is there no gap between B30 and A31, B31 and A32 etc?

If that's the case and the dates in A30:A40 are in ascending order then your original formula would be close, just use

=lookup(A1,A30:C40)

I suspect you may have gaps between your date ranges, in which case, assuming only one possible event for each date (otherwise the earliest will be selected), use this array formula, confirmed with CTRL+SHIFT+ENTER

=INDEX(C30:C40,MATCH(1,(A30:A40<=A1)*(B30:B40>=A1),0))

 

[Joyner]

Thank you barry houdini,

That works great. Wish I had saved myself the hours of frustration but I guess I learned some on the way. I could not have figured that out on my own - you have saved me a lot of time. Thanks for the quick response and the magic....

Dean

 

[Joyner]

P.S. You were right in your assumption, it was the second formula I needed. Thanks