Data manipulation with dynamic arrays

[sharshra]

I have to do the following tasks based on 2 tables. In the first table (comments table), filter the names which doesn´t have 'y' or 'com' in comments. In the original table, check the status for the names filtered from comments table. If the status has 'a', copy that row to the output.

I´m using dynamic arrays to get the output. I´m getting partially correct result as shown in the actual output below. Can the experts in this forum help please to get the desired output?

Comments table:

excel problems.xlsx
	B	C
30	name	comment
31	ds	y
32	fd	y
33	reyu
34	jkl	com
35	nmnm	.,.,
36	vcv	n098m,1
37	opioq	,.-`+
filter

Original table:

excel problems.xlsx
	F	G	H
30	name	status	desc
31	nmnm	a
32	opioq	a	78k
33	fd	a	75s
34	vcv	i	098y
35	jkl	i	23f
36	ds	i	666a
37	mmm	a	9m
38	fd	a
39	www	i
40	yty	i
41	wq	a
42	reyu	a	m8
filter

Desired output:

excel problems.xlsx
	K	L	M
30	name	status	desc
31	reyu	a	m8
32	mmm	a	9m
33	opioq	a	78k
filter

Actual output:

excel problems.xlsx
	S
31	reyu
32	nmnm
33	#CALC!
34	opioq
filter

Cell Formulas
Range	Formula
S31:S34	S31	=LET( o, $F$31:$H$42, oName, $F$31:$F$42, oStatus, $G$31:$G$42, i, $B$31:$C$37, iName, $B$31:$B$37, iComment, $C$31:$C$37, fNoCom, FILTER(i,(iComment<>"y")*(iComment<>"com")), fActive, MAP(INDEX(fNoCom,,1), LAMBDA(x, FILTER(oName, (oName=x)*(oStatus="a")))), fActive)
Dynamic array formulas.

 

[Fluff]

How about

=LET(a,FILTER(B31:B37,(C31:C37<>"y")*(C31:C37<>"com")),FILTER(F31:H42,(G31:G42="a")*(ISNUMBER(XMATCH(F31:F42,a)))))

 

[sharshra]

Thanks for super fast response, @Fluff. I´m getting the desired output
Just one question. I get 0 in the output when desc is blank. How can I retain space & not replace it with 0?

 

[Fluff]

How about

=LET(a,FILTER(B31:B37,(C31:C37<>"y")*(C31:C37<>"com")),FILTER(F31:H42&"",(G31:G42="a")*(ISNUMBER(XMATCH(F31:F42,a)))))

 

[sharshra]

Perfect!!! Thanks a lot, @Fluff

 

[Fluff]

You're welcome & thanks for the feedback.