Problem with IFAND function

[help85]

Hi all,

I have a workbook that contains a list of tasks (rows 3 to 82) and project milestones (columns F to M) within Sheet 1. The user selects a 'Y' in the column relevant to the milestone that the task will be completed in. I wanted to then create a consolidated list of tasks for each of the project milestones, which I have managed (shown in Sheet 3 of the example attached) using a formula I found online:

{=IFERROR(INDEX(Sheet1!$E$2:$E$82,SMALL(IF("Y"=Sheet1!F$2:F$82,ROW(Sheet1!$E$2:$E$82),""),ROW()-1)-1,1),"")}

To further consolidate the list of tasks for each project milestone, I would like to expand the formula above to include an AND function to also look for 'Y' in Column U of Sheet 1. I have tried the below:

{=IFERROR(INDEX(Sheet1!$E$2:$E$82,SMALL(IF(AND(("Y"=Sheet1!F$2:F$82),Sheet1!$U2:$U$82="Y"),ROW(Sheet1!$E$2:$E$82),""),ROW()-1)-1,1),"")}

I am only getting blank cells. Is anyone able to advise where I am going wrong??

Thanks

[Fluff13]

How about

Please Login or Register  to view this content.

this does not need array entry

[help85]

That works brilliantly, thank you!

[Fluff13]

You're welcome & thanks for the feedback.