Excel Index Function - Matching calendar dates and activities using an index fuction

Hi Guys,

Would greatly appreciate any input you could give on this.
So I have an excel sheet with two tabs.
The first is a list of all activities, ("TRACKER" TAB) and the column beside it is a date associated with the day the activity is due.
The second is a calendar ("CALENDAR" TAB) format, simply listing each date of the year, and an empty column underneath it.

In the empty column I am inserting the following function: [=INDEX(Tracker!$E$1:$V$41,SMALL(IF(Tracker!$E$1:$V$41=Calendar!E6,ROW(Tracker!$E$1:$V$41)),ROW(1:1)),2)]

What I am trying to achieve, is to use the date in the calendar tab - and search in the array of the tracker tab, returning any activity that corresponds with the respective date. In summary, if you looked at the 8th October on the calendar tab, it would pull all activities associated with that date into the blank cell below it.
I couldnt use a vlookup, as there may be multiple activities with the same date and thus I need to return multiple results.

The formula recognizes the date in the array, and pulls the respective information from the array/activity tab ok, the problem is its the wrong data pulled (same activity returned every time). If the date from the calendar is listed in the activity tab, it will always pull the data in the second column of the array(see last arguement in formula above), what it should do is pull the second column from the array, respective of where the target date lies. So basically cell E6 is the second column, and it returns E6 contents everytime, even if the date is somewhere completely different in the TRACKER tab.

Is there a way I can make this dynamic, so that it pulls the second column respective of the date, as opposed to the second column in general array.

Thanks!!!
Marcus

Hi Melvin, thanks for the response. Yes I thought that might be it, but could not figure out a formula for this.
Id need to get the formula to recognize the target cell and pull a "+" 2 column I guess - but it was still giving me the same result.
Sample sheet attached, you will see the dates in the tracker tab in yellow, and notice on the calendar tab that dates that are indeed listed - are all pulling the same result accross row 7 - "A - IOI Print for Manager A", where cell I7 on calendar should have "A - IOI Review for Manager A" as an example.

Thanks for your help!
Marcus

Originally Posted by Melvinrobb

So you want it to search column E and return column F, then search G - return H, then search I -return J, search K - return L,.... etc....
If that is accurate, you are going to run into major issues I believe. If you are wanting to have one cell display the values of multiple cells, the structure you have is not ideal.
Not sure how to attack this.

It would be more searching the entire array in tracker tab as opposed to the columns alone [$E$1:$V$41,], and returning the column to the right of the date recognized.

As for the multiple cells, I believe that if I were to add a row under ROW 7 in calendar tab, and pull the formula down, it will automatically retrieve the next result, and once again add another row and pull down for the third result etc etc etc. Unfortunately I cannot confirm this until I get the current issue solved first.

Thanks,
Marcus