Limit the filter function

[yomarcos]

Hello everyone,

I asked before in another thread how to show only the first value from a filter function and the solution I got here worked perfectly.
Now I need to show the first 5 values from a filter function. Does anyone have an idea how to do that?

Thank you!!

 

[jorismoerings]

Hi,

Just extend the formula given earlier with a SEQUENCE so this

=index(filterformula,1)

becomes

=index(filterformula,SEQUENCE(5))

This formula will spil vertical to transpose the results horizontally

=index(filterformula,SEQUENCE(1,5))

 

[yomarcos]

Hi,

Just extend the formula given earlier with a SEQUENCE so this

=index(filterformula,1)

becomes

=index(filterformula,SEQUENCE(5))

This formula will spil vertical to transpose the results horizontally

=index(filterformula,SEQUENCE(1,5))

This is getting me only the 1st value. Not the first 5.
I have tried with =sortn(filterformula, 5) and even though it changes the order of the values, it gives me only 5 of them

 

[jorismoerings]

This is getting me only the 1st value. Not the first 5.
I have tried with =sortn(filterformula, 5) and even though it changes the order of the values, it gives me only 5 of them

Strange, please check 2 things:

1. type errors because in my test (see below) it works;
2. does your filter has also blank results because then this will not work and it becomes difficult and will involve LET function (which you can't use on Google sheets)

Book1
	J	K	L	M
1	Product Code	RowNr
2	ID0003	2
3	ID0003	3		2
4	ID0001	4		3
5	ID0002	5		6
6	ID0003	6		14
7	ID0004	7		15
8	ID0004	8
9	ID0004	9
10	ID0005	10
11	ID0006	11
12	ID0006	12
13	ID0007	13
14	ID0003	14
15	ID0003	15
16	ID0001	16
17	ID0002	17
18	ID0003	18
19	ID0003	19
20	ID0003	20
21	ID0003	21
22	ID0003	22
23	ID0003	23
24	ID0003	24
25	ID0007	25
26	ID0003	26
27	ID0003	27
28	ID0001	28
29	ID0002	29
30	ID0003	30
31	ID0004	31
32	ID0004	32
33	ID0004	33
34	ID0005	34
35	ID0006	35
36	ID0006	36
37	ID0007	37
Input

Cell Formulas
Range	Formula
M3:M7	M3	=INDEX(FILTER(K1:K37,J1:J37="ID0003"),SEQUENCE(5))
K2:K37	K2	=ROW()
Dynamic array formulas.

 

[yomarcos]

Strange, please check 2 things:

1. type errors because in my test (see below) it works;
2. does your filter has also blank results because then this will not work and it becomes difficult and will involve LET function (which you can't use on Google sheets)

Book1
	J	K	L	M
1	Product Code	RowNr
2	ID0003	2
3	ID0003	3		2
4	ID0001	4		3
5	ID0002	5		6
6	ID0003	6		14
7	ID0004	7		15
8	ID0004	8
9	ID0004	9
10	ID0005	10
11	ID0006	11
12	ID0006	12
13	ID0007	13
14	ID0003	14
15	ID0003	15
16	ID0001	16
17	ID0002	17
18	ID0003	18
19	ID0003	19
20	ID0003	20
21	ID0003	21
22	ID0003	22
23	ID0003	23
24	ID0003	24
25	ID0007	25
26	ID0003	26
27	ID0003	27
28	ID0001	28
29	ID0002	29
30	ID0003	30
31	ID0004	31
32	ID0004	32
33	ID0004	33
34	ID0005	34
35	ID0006	35
36	ID0006	36
37	ID0007	37
Input

Cell Formulas
Range	Formula
M3:M7	M3	=INDEX(FILTER(K1:K37,J1:J37="ID0003"),SEQUENCE(5))
K2:K37	K2	=ROW()
Dynamic array formulas.

I have just tried it and it doesn't work on Google sheets but it works on Excel.

 

[jorismoerings]

No expert on Google Sheets so with a little help from the ever expanding internet, google came up with
The formula =SEQUENCE(5) gives the same result as =ARRAYFORMULA(ROW(A1:A5)

With that we reversed the situation into: formula will work on Google Sheets but not in Excel.

 

[yomarcos]

Nobody is a Google Sheets expert hahaha!!
The problem is not the SEQUENCE formula. =SEQUENCE(5) returns 1 2 3 4 5 in different rows. The problem is putting it inside the INDEX formula. The INDEX formula in Google sheets returns only ONE value

 

[yomarcos]

I ended up using SORTN which sorts the values alphabetically which is not something I was trying to do, but it also limits the amount of values. I did have to TRANSPOSE/SORTN/TRANSPOSE to match what I needed though.