Return match range dynamically

[cc1987]

Hi,

I'm trying to find a formula that will dynamically return a range containing non-na values only.
I have one table that can contain different numbers of rows/columns and then a second to extract complete values only.

In the second one, I can filter row names & column headers to be complete values but I'm looking for a single formula that could go in cell B8 to then return the values as in the image.

*On a work laptop and cannot install XL2BB for the mini sheet

 

[BSALV]

do you have excel 2021 or 365 ?

 

[cc1987]

do you have excel 2021 or 365 ?

365

 

[BSALV]

B6 and B7 are matrix formulas, but you don't see that in 365, but the cells at their right have to be empty otherwise you get an overloop-error.

Map3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	2020	58	4	34	0	0	0	#DELING.DOOR.0!	#######	#######	#######	#######	#######	#DELING.DOOR.0!	96
3	2021	0	0	0	4	2	44	#DELING.DOOR.0!	#######	#######	#######	#######	50	#DELING.DOOR.0!	#DELING.DOOR.0!
4	2019
5
6	2020	58	4	34	0	0	0	96
7	2021	0	0	0	4	2	44	50
Blad2

Cell Formulas
Range	Formula
H2:N2	H2	=1/0
H3:L3,N3	H3	=+H2
O3	O3	=+N3
B6:H7	B6	=FILTER(B2:O2,ISNUMBER(B2:O2))
Dynamic array formulas.

 

[Fluff]

365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

Another option

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	2020-21	58	4	34	0	0	0	#N/A	#N/A	#N/A	#N/A	#N/A	96
3	2021-22	0	0	0	4	2	44	#N/A	#N/A	#N/A	#N/A	#N/A	50
4	2019-20	28	0	0	0	42	0	#N/A	#N/A	#N/A	#N/A	#N/A	70
5
6
7
8	2020-21	58	4	34	0	0	0	96
9	2021-22	0	0	0	4	2	44	50
10	2019-20	28	0	0	0	42	0	70
11
Primary

Cell Formulas
Range	Formula
M2:M4	M2	=SUM(IFERROR(B2:K2,0))
A8:H10	A8	=FILTER(A2:M4,MMULT(SEQUENCE(,ROWS(A2:M4),,0),--(ISERROR(A2:M4)))=0)
Dynamic array formulas.