Use dynamic array

[sharshra]

I have 2 tables. Desired output should contain only those rows from source table which has `no´ in the entry column & the name is present in reference table. I´m getting an incorrect result. I´m sure I´m making a mistake & I have to use dynamic array to get the desired output. Can the experts in this forum help please?


Source table:

excel problems.xlsx
	B	C
2	name	entry
3	dfds	1
4	gfh	no
5	uir	7
6	qewr	8.4
7	xcvz	no
8	cmvn	2.55
9	dhgdh	no
10	teywu	3
11	sdf	4
12	gd	no
13	hkfi	no
Sheet5

Reference table:

excel problems.xlsx
	E
2	name
3	dfds
4	qewr
5	xcvz
6	cmvn
7	dhgdh
8	teywu
9	sdf
10	gd
Sheet5

Desired output:

excel problems.xlsx
	G	H
2	name	entry
3	xcvz	no
4	dhgdh	no
5	gd	no
Sheet5

Incorrect output:

excel problems.xlsx
	K	L
2	name	entry
3	gfh	no
4	xcvz	no
5	dhgdh	no
6	gd	no
7	hkfi	no
Sheet5

Cell Formulas
Range	Formula
K3:L7	K3	=LET( f,FILTER(Table2,(Table2[entry]="no")*(NOT(ISERROR(XLOOKUP(Table2[@name],Table3[name],Table3[name]))))), f)
Dynamic array formulas.

 

[lrobbo314]

How about this?

Excel
	A	B	C	D	E	F	G
1	name	entry		name		Name	Entry
2	dfds	1		dfds		xcvz	no
3	gfh	no		qewr		dhgdh	no
4	uir	7		xcvz		gd	no
5	qewr	8.4		cmvn
6	xcvz	no		dhgdh
7	cmvn	2.55		teywu
8	dhgdh	no		sdf
9	teywu	3		gd
10	sdf	4
11	gd	no
12	hkfi	no
Sheet3

Cell Formulas
Range	Formula
F2:G4	F2	=FILTER(A2:B12,ISNUMBER(XMATCH(A2:A12,D2:D9))*(B2:B12="no"))
Dynamic array formulas.

 

[dreid1011]

Perhaps this:

=FILTER(B3:C13,(C3:C13="no")*(IFNA(XMATCH(B3:B13,E3:E10,0),"")<>""))

 

[sharshra]

Perfect. Thanks to both @lrobbo314, @dreid1011 Both solutions are working correct, but unfortunately I can mark only one as solution.

 

[dreid1011]

Perfect. Thanks to both @lrobbo314, @dreid1011 Both solutions are working correct, but unfortunately I can mark only one as solution.

You're welcome, and glad we could help.