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($D$26:$D$44,MATCH(1,(B3=$B$26:$B$44)*(C3=$C$26:$C$44),0)),"")You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
This one works nice! thanks OliverScheurich
there is one one thing missing.
How can I apply the same formula in cell B26, to retrieve the objective from B3 that was assigned for that date? 01/august/2023
here is the example:
this table needs to retrieve the objectives from the MAIN OBJECTIVES TABLE
01/08/2023 TODAY'S OBJECT LIST DATE
1 OBJECTIVE 1 1/8/2023
2 OBJECTIVE 2 1/8/2023
3 OBJECTIVE 3 1/8/2023
4
5
there is one one thing missing.
How can I apply the same formula in cell B26, to retrieve the objective from B3 that was assigned for that date? 01/august/2023
here is the example:
this table needs to retrieve the objectives from the MAIN OBJECTIVES TABLE
01/08/2023 TODAY'S OBJECT LIST DATE
1 OBJECTIVE 1 1/8/2023
2 OBJECTIVE 2 1/8/2023
3 OBJECTIVE 3 1/8/2023
4
5
=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.
- Hi again, OliverScheurich
I got an issue, maybe you can give me an advice:
If I put the same formula in my excel, but in different cells, it does not work. I think that's because of the ROW function which I can't understand how it works in that situation.
How can I adjust it , please?
Many thanks in advance!
Alex=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.
- thanks OliverScheurich
I appreciate your help. It worked as expected!!!