SOLVED

# return rows from list that matches criteria without blanks

Brass Contributor

# 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 the ideea.

So, I have a MAIN OBJECTIVE LIST table where I add all objectives for the current month and assign a date to each objective. The STATUS column will retrieve data from the tables that are bellow.

example:

Let's say that I assign 3 objectives for the first day (01/08/2023) and two for the next day.

A maximum of 20 objectives can be assigned a month.

 MAIN OBJECTIVE LIST DATE STATUS 1 OBJECTIVE 1 01/08/2023 2 OBJECTIVE 2 01/08/2023 3 OBJECTIVE 3 01/08/2023 4 OBJECTIVE 4 02/08/2023 5 OBJECTIVE 5 02/08/2023
 6 7 8 9 10

Bellow this table, each day will have it's own table that will retrieve the objectives from the MAIN TABLE only for the dates that match the current date (without blank rows). A maximum of 5 objectives can be assigned daily.

Once an objective is completed, I will assign a status in Today's table. The STATUS column must then be retrieved by the MAIN TABLE.

 01/08/2023 TODAY'S OBJECT LIST DATE STATUS 1 OBJECTIVE 1 1/8/2023 SOLVED 2 OBJECTIVE 2 1/8/2023 SOLVED 3 OBJECTIVE 3 1/8/2023 PENDING 4 5

the other days of the month will work the same.

 02/08/2023 TODAY'S OBJECT LIST DATE STATUS 1 OBJECTIVE 4 2/8/2023 PENDING 2 OBJECTIVE 5 2/8/2023 SOLVED 3 4 5

11 Replies

# Re: return rows from list that matches criteria without blanks

@Detlef_Lewin
why is it a bad settup? it helps if you would give more details. I tried my best
My question (in one sentence) would be: how can I built a formula that returns rows from list that matches criteria (the dates) without blanks
thanks!!

# Re: return rows from list that matches criteria without blanks

Simply: You only need 1 table.

# Re: return rows from list that matches criteria without blanks

``=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.

# Re: return rows from list that matches criteria without blanks

@Detlef_Lewin
I can't have only 1 table because the example from above is only the missing part from my entire project. Each day needs to have its own table because there are more things that are recorded on a daily basis. The objectives are only one of them.

# Re: return rows from list that matches criteria without blanks

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
best response confirmed by Alecs (Brass Contributor)
Solution

# Re: return rows from list that matches criteria without blanks

``=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.

# Re: return rows from list that matches criteria without blanks

thanks @OliverScheurich
I appreciate your help. It worked as expected!!!

# Re: return rows from list that matches criteria without blanks

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.

Alex

# Re: return rows from list that matches criteria without blanks

``=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.

# Re: return rows from list that matches criteria without blanks

Now it's perfect. It works as expected. Many thanks!!!
1 best response

Accepted Solutions
best response confirmed by Alecs (Brass Contributor)
Solution

# Re: return rows from list that matches criteria without blanks

``=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.