Hi all,
I am new to using arrays and am having trouble with the following:
I have a long list of dates and projects. I would like to create a table that returns details for a given week. An example of what I would like to return:
Screen Shot 2018-10-31 at 3.27.41 PM.png
The week to pull is manually entered in B3 and B4. Table 1 has all the projects listed. Table 2 should pull Dates greater than or equal to B3 and less than B4. It should also pull the corresponding Project to each date. As you can see, since there are multiple projects on various dates, I know I need to use an Index function rather than a Lookup function to pull all. I have a formula that only pulls projects from a single date:
{=IFERROR(INDEX(Table_1[Project],SMALL(IF(Table_1[Date]=$B$3,ROW(Table_2[Project])-MIN(ROW(Table_2[Project]))+1),ROWS(F$7:F7))),"")}
This formula is entered in F7. A similar formula is in E7 but with all instances of "Project" exchanged with "Date". This formula works fine.
I tried this formula but it would only return the info in the first line of Table 1:
{=IFERROR(INDEX(Table_1[Project],SMALL(IF(AND(Table_1[Date]>=$B$3,Table_1[Date]<$B$4),ROW(Table_2[Project])-MIN(ROW(Table_2[Project]))+1),ROWS(F$7:F7))),"")}
After that I'm stuck. Any help would be appreciated.
Bookmarks