Array formula with IF criteria

datamster

Board Regular
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.

Code:
``{=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:

Code:
``{=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!

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

AhoyNC

Well-known Member
What happens if you change the formula to:

Code:
``{=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

Board Regular
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

MrExcel MVP
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
Code:
``=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

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

AhoyNC

Well-known Member
You're welcome. Thanks for the feedback.

Replies
6
Views
383
Replies
10
Views
976
Replies
26
Views
922
Replies
6
Views
688
Replies
5
Views
414

1,195,670
Messages
6,011,074
Members
441,580
Latest member
BornholmerBjarne

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back