Forum Discussion
Filter function
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
4 Replies
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) )- This formula also help me to achieve the goal. thank you so much for your time and help. have a wonderful day.
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.
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.
