Filter function with dynamic number of rows

[Natit]

Hi,
Here is my filter function.
=FILTER('gil2'!$A$2:$D$37,ISNUMBER(MATCH('gil2'!$C$2:$C$37,'gil2'!$M$8:$O$8,0)))
It works fine, but I would like it to be dynamic so the last row will be a result of "counta" or any other counting function instead a fix number (in the function here it is 37).
TIA
Nati

 

[GraH]

Some sample data would have been useful.
One can use XLOOKUP to return a range.

Made a silly sample to showcase:

Book1
	A	B	C	D	E	F	G	H
1
2	a	s	5			z	y	5
3	f	d	4			z	u	0
4	d	e	87			z	lm	3
5	e	f	6			z	o	7
6	d	g	4
7	e	h	12
8	z	y	5
9	w	t	1
10	z	u	0
11	f	k	50
12	z	lm	3
13	f	p	5
14	z	o	7
15	zzzz	n	5
16	w	,	8
Sheet1

Cell Formulas
Range	Formula
F2:H5	F2	=FILTER(A2:XLOOKUP("Ω",A:A,C:C,,-1,2),A2:XLOOKUP(99^99,C:C,A:A,,-1,2)="z")
Dynamic array formulas.

 

[Natit]

Hi,
Thx for youe answer.
I'm adding my test table.
My question stays the same.
And I will add another one.
Is ther an option that thru the filte function I will show differnt (A,B,E,H...) columns and not only a sequence
Thx again
Nati

Filter_question.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	item	description	price code	inventar		item	description	price code	inventar			unit group a	unit group b	unit group c
2	1893	cat1	1100	10		1893	cat1	1100	10			aa1	bb1	cc1
3	2228	cat2	1100	20		2228	cat2	1100	20			aa2	bb2
4	2358	cat3	1234	30		2933	cat17	7010	43			aa3
5	2562	cat4	1234	30		2743	cat18	7010	55
6	2640	cat5	1234	30		2817	cat19	7010	17
7	2656	cat6	1234	29		2900	cat20	7010	18			data table
8	8011	cat7	1273	70		4019	cat26	9701	63			aa1	1100	7010	9701
9	8513	cat8	1273	60		4047	cat27	9701	54			aa2	1234	8563
10	7118	cat9	1273	50		4097	cat28	9701	19			aa3	9981
11	5723	cat10	1111	56		4154	cat29	9701	70			bb1	1273	9010
12	4328	cat11	1111	57								bb2	1111	1555
13	7010	cat12	1555	73								cc1	6591
14	1538	cat13	1555	34
15	1111	cat14	6591	43
16	4444	cat15	6591	19
17	5555	cat16	6591	31
18	2933	cat17	7010	43
19	2743	cat18	7010	55
20	2817	cat19	7010	17
21	2900	cat20	7010	18
22	2914	cat21	8563	43
23	2936	cat22	8563	23
24	2945	cat23	8563	45
25	2960	cat24	8563	24
26	1544	cat25	9010	52
27	4019	cat26	9701	63
28	4047	cat27	9701	54
29	4097	cat28	9701	19
30	4154	cat29	9701	70
31	9311	cat30	9981	25
32	9412	cat31	9981	27
33	8317	cat32	9981	54
34	4156	cat33	9988	16
35	4188	cat34	9988	15
36	4195	cat35	9988	90
gil2

Cell Formulas
Range	Formula
F2:I11	F2	=LET( row_num,COUNTA($A:$A), FILTER($A$2:$D$37,ISNUMBER(MATCH($C$2:$C$37,$M$8:$O$8,0))))
Dynamic array formulas.

 

[GraH]

EDITED: forgot to add the filter arguments before.

in [F2], try

=LET(
Cls,XLOOKUP(F1:I1,A1:D1,COLUMN(A1:D1)),
Arg,ROW(A2:XLOOKUP(99^99,A:A,A:A,,-1,2)),
Arr_2,C2:XLOOKUP(99^99,C:C,C:C,,-1,2),
Incl,ISNUMBER(MATCH(Arr_2;$M$8:$O$8;0)),
Arr,INDEX(A:D,Arg,Cls),FILTER(Arr,Incl))

 

[GraH]

With XL2BB
[

MR Excel Dynamic filter arrays.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	item	description	price code	inventar		price code	description	inventar	item			unit group a	unit group b	unit group c
2	1893	cat1	1100	10		1100	cat1	10	1893			aa1	bb1	cc1
3	2228	cat2	1100	20		1100	cat2	20	2228			aa2	bb2
4	2358	cat3	1234	30		7010	cat17	43	2933			aa3
5	2562	cat4	1234	30		7010	cat18	55	2743
6	2640	cat5	1234	30		7010	cat19	17	2817
7	2656	cat6	1234	29		7010	cat20	18	2900			data table
8	8011	cat7	1273	70		9701	cat26	63	4019			aa1	1100	7010	9701
9	8513	cat8	1273	60		9701	cat27	54	4047			aa2	1234	8563
10	7118	cat9	1273	50		9701	cat28	19	4097			aa3	9981
11	5723	cat10	1111	56		9701	cat29	70	4154			bb1	1273	9010
12	4328	cat11	1111	57								bb2	1111	1555
13	7010	cat12	1555	73								cc1	6591
14	1538	cat13	1555	34
15	1111	cat14	6591	43
16	4444	cat15	6591	19
17	5555	cat16	6591	31
18	2933	cat17	7010	43
19	2743	cat18	7010	55
20	2817	cat19	7010	17
21	2900	cat20	7010	18
22	2914	cat21	8563	43
23	2936	cat22	8563	23
24	2945	cat23	8563	45
25	2960	cat24	8563	24
26	1544	cat25	9010	52
27	4019	cat26	9701	63
28	4047	cat27	9701	54
29	4097	cat28	9701	19
30	4154	cat29	9701	70
31	9311	cat30	9981	25
32	9412	cat31	9981	27
33	8317	cat32	9981	54
34	4156	cat33	9988	16
35	4188	cat34	9988	15
36	4195	cat35	9988	90
Sheet1

Cell Formulas
Range	Formula
F2:I11	F2	=LET( Cls,XLOOKUP(F1:I1,A1:D1,COLUMN(A1:D1)), Rws,ROW(A2:XLOOKUP(99^99,A:A,A:A,,-1,2)), Arr,C2:XLOOKUP(99^99,C:C,C:C,,-1,2), Incl,ISNUMBER(MATCH(Arr,$M$8:$O$8,0)), Res,INDEX(A:D,Rws,Cls),FILTER(Res,Incl))
Dynamic array formulas.

 

[Natit]

IT works fine ofcourse.
But, I Have a question, and sorry if it sound rude.
Do I need this complicated formula in order just to put the number of rows (which I found with the COUNTA function)?

 

[GraH]

Your COUNTA includes the header and returns a single number. My "Rws" part, returns all row number from row 2 until the end of the range.
Could be using COUNTA with SEQUENCE I suppose.

=LET(
Cols,XLOOKUP(F1:I1,A1:D1,COLUMN(A1:D1)),
Cnt,COUNTA(A:A)-1,
Rws,SEQUENCE(Cnt,,2,1),
Arr,C2:XLOOKUP(99^99,C:C,C:C,,-1,2),
Incl,ISNUMBER(MATCH(Arr,$M$8:$O$8,0)),
Res,INDEX(A:D,Rws,Cols),FILTER(Res,Incl))