Hi! I'm (brand) new to the forum and have been struggling through some Excel issues on my own that I could really use some advice with. I am trying to make a calendar that lists all of the tasks and deadlines due during a given week. I'm trying to use an INDEX MATCH array with 3 criteria: the group responsible for the task, and the due date (because it's done by week, the date must be within a given range).
I have a data table in the Data tab, cells A2 to U91, with the group names in Column D, the due date in column C, and the title of the task in column F. What I am creating is a calendar, seperated by group, that look sup all the tasks assigned to that group, due in a given week, and listing the titles of the tasks in descending rows in a column (in this case, Column G of my current worksheet, though weeks are listed horizontally so I need to be able to drag the formula right).
I've consulted a number of different posts here and elsewhere and have managed to get this formula to return the FIRST matching result : {=IFERROR(INDEX(Data!$A$2:$U$91,MATCH(1,(Data!$C:$C>=G$38)*(Data!$C:$C<=G$39)*(Data!$D:$D="Group A"),0),6),"")}
When I go to try and make this an array formula, I tried this : {=INDEX(Data!$A$2:$U$91, SMALL(IF(COUNTIFS(Data!$D:$D,"Group A",Data!$C:$C,">="&G$38,Data!$C:$C,"<="&G$39), MATCH(ROW(Data!$D:$D), ROW(Data!$D:$D)), ""), ROWS($G$21:G21)),6)} but it returns a list of EVERY task, regardless of if it matches my COUNTIF criteria.
EDIT: I have also tried the following formula, with the same result as above (lists all task names, not filtered)
{=INDEX(Data!$A$2:$U$91,SMALL(IF(ISNUMBER(MATCH(1,(Data!$C:$C>=G$38)*(Data!$C:$C<=G$39)*(Data!$D:$D="Group A"),0)),MATCH(ROW(Data!F:$F),ROW(Data!F:$F)),""),ROWS($G$21:G21)),6)}
Does anyone know how I can fix/change either of these formulas so that it will list all of the proper results? Thank you!
Bookmarks