Forum Discussion
Solved
return rows from list that matches criteria without blanks
Hello, can you help me with this one, please? I try to create an monthly objective list and track the progress but without success. I attach the excel file here and I'll explain bellow what is t...
=IFERROR(INDEX($B$3:$B$22,SMALL(IF(($C$3:$C$22=C26)*($C$3:$C$22<>""),ROW($B$3:$B$22)-2),ROW(A1))),"")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
=IFERROR(INDEX($H$6:$H$25,SMALL(IF(($I$6:$I$25=C26)*($C$3:$C$22<>""),ROW($H$6:$H$25)-5),ROW(A1))),"")I think as well that it's because of the ROW function. In the attached file i've made a copy of the MAIN OBJECTIVE LIST in another range. Now the ROW part of the formula is:
ROW($H$6:$H$25)-5
because this evaluates to:
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20} .
The reason is that the data starts in cell H6 and ends in cell I25 which are 20 rows altogether.
Alternatively we can apply this:
ROW($H$1:$H$20)
which evaluates to
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}
as well. That's what is required since the MAIN OBJECTIVE LIST has 20 rows of data.
Now it's perfect. It works as expected. Many thanks!!!