Using Index & Match with Date Ranges

Hi,

I've been trying to use index and match to give me information from one sheet (detailed data) to another (overall view).

I have a list of tasks, with dates attached in the detailed sheet.

e.g.

Task 1 8-Jan-14
Task 2 3-Feb-14
Task 3 6-Apr-14
etc. etc.

I want to be able to give a date range (week starting dates from another table) and be able to get the task name to appear

I have been using index and match, and while this works for task 1, for the subsequent tasks it does not work. the formula i have is:

=INDEX(A1:A3,(AND(MATCH(WEEK START DATE CELL REF,B1:B3,-1),MATCH(NEXT WEEK START DATE CELL REF,B1:B3,1))))

Anyone able to help (if you can understand!)?

Cheers,

Marissa

Here you go.

Originally Posted by nflsales

attach dummy excel file

It is so very close!! What I didn't explain properly is that the dates attached to the tasks are deadline dates, and so ideally if the task deadline didn't fall in that week then the cell would be blank.
Matches-3.xlsx
(see the line below for what it would ideally look like - this is just my input though)

Thanks for your help!!

anyone got a fix for this?

Not sure but try this:

=IFERROR(INDEX(Tasks!$A$4:$A$6,MATCH(1,(Tasks!$B$4:$B$6>=B$3)*(Tasks!$B$4:$B$6<=C$3),0)),"")

Put in B5 and press CTRL-SHIFT-ENTER button together and copied cross