Filter function

New Contributor

I have programmed a formula using the Filter function to bring a short list of items from a large spread sheet and I managed to make it work.   But I need to somehow limit the list to a set of rows instead of having it to bring a long list.   I would highly appreciated your help.

 

In the following sample the formula brings a list of 10 rows, and I want to limit it at 5 Max

 

Filter formula.PNG

4 Replies

@Eduardo_Mata Try it like this:

 

=INDEX(<your current formula>,SEQUENCE(5),SEQUENCE(,6))

where you replace <your current formula> with what you have now, excluding the =-sign.

 

If you happen to be an Insider (beta) user, you can use:

=TAKE(<your current formula>,5)

 The first SEQUENCE is to keep the top 5 rows, The next SEQUENCE is to keep all 6 columns from the data set.

@Eduardo_Mata 

If you do not have access to TAKE, you could always try a second filter applied to the sorted dataset

= LET(
     selected, SORT(FILTER(Table1,Table1[crit]=crit),6,-1),
     k,        SEQUENCE(ROWS(selected)),
     FILTER(selected,k<=5)
   )

 

@Riny_van_Eekelen 

 

I highly appreciate your time and help.  I was able to bring a 5 rows of information utlizing the formula that you kindly shared with me.

Thank you so much.  Have a great day.

This formula also help me to achieve the goal. thank you so much for your time and help. have a wonderful day.