Array formula with IF criteria

[datamster]

Hello, would anyone be able to assist with an Array formula I'm working on where I'm looking to have it return only the cell in a row matching my criteria. The data starts in row 9 and row 8 is the header.

{=IFERROR(INDEX(Data!$Y$9:$Y$561,SMALL(IF(Data!$Y$9:$Y$561>=Data!$Z$3,ROW(Data!$C$9:$C$561)-ROW(Data!D$9)),ROWS(Data!Y$9:Data!Y10))),"")}

I have a table with duration values in column Y with the criteria in cell Z3. This formula currently returns the correct column but the criteria part isn't working correctly. If I remove that part and keep the rest as:

{=IFERROR(INDEX(Data!$C$9:$C$561,SMALL(IF(Data!$Y$9:$Y$561,ROW(Data!$C$9:$C$561)-ROW(Data!C$9)),ROWS(Data!C$9:Data!C10))),"")}

Then it starts populating with the first row.

thank you for any help!

 

[AhoyNC]

What happens if you change the formula to:

{=IFERROR(INDEX(Data!$Y$9:$Y$561,SMALL(IF(Data!$Y$9:$Y$561>=Data!$Z$3,ROW(Data!$C$9:$C$561)-ROW(Data!D$9)[B][COLOR=#ff0000]+1[/COLOR][/B]),ROWS(Data!Y$9:Data!Y10))),"")}

 

[datamster]

Thanks for the quick reply. Almost there I think! Your change returns the correct row for matching criteria but it skipped the first matched row with 3 of 4 showing correctly.

 

[Marcelo Branco]

Thanks for the quick reply. Almost there I think! Your change returns the correct row for matching criteria but it skipped the first matched row with 3 of 4 showing correctly.

Maybe this fix it

=IFERROR(INDEX(Data!$Y$9:$Y$561,SMALL(IF(Data!$Y$9:$Y$561>=Data!$Z$3,ROW(Data!$C$9:$C$561)-ROW(Data!D$9)[B][COLOR=#ff0000]+1[/COLOR][/B]),ROWS([B][COLOR=#ff0000]Data!Y$9:Y9[/COLOR][/B]))),"")

M.

 

[datamster]

That did it! Thank you AhoyNC and Marcelo for the quick responses.

 

[AhoyNC]

You're welcome. Thanks for the feedback.