ASEARCH

[Xlambda]

ASEARCH array search, filters the search/find results of an array "as" within another array "a" according with the search type "st". calls T_SF
st=0, any cell, function will filter all the rows with at least 1 occurrence of any of the search values
st=1, all in same row, function will filter all the rows with at least 1 occurrence of all the search values
st=2, all in same cell, function will filter all the rows with at least 1 cell with all occurrences in it.
Note: Extremely useful in real life, for large wide tables with a lot of text info, no need of column filters, the classic formula that I used before recursion lambdas was a challenge.

=LAMBDA(a,as,sf,st,
    LET(d,"|",s,SEQUENCE(COUNTA(as)),t,TEXTJOIN(d,,s),
       x,T_SF(a,as,sf,),y,ATEXTJOIN(x,,,d),z,T_SF(y,s,,),
       w,SWITCH(st,0,z<>"",1,z=t,2,MMULT(--(x=t),SEQUENCE(COLUMNS(a)))>0),
       FILTER(a,w,"not found")
    )
)

LAMBDA 7.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	sample									search/find array
2	a	aY	a	aK	a	a	a	aZ		X	Y	Z
3	b	b	b	b	by	b	b	b		K	W
4	cz	c	cx	c	c	c	ck	c
5	d	d	d	d	d	d	d	d
6	e	exw	e	ezyx	e	ekw	e	e
7	f	f	f	f	f	f	f	f
8	g	gYZKWX	g	g	g	g	g	g
9	h	h	h	h	h	h	hzyxkw	h
10
11	=ASEARCH(A2:H9,J2:L3,0,0)						sf=0	st=0		=ASEARCH(A2:H9,J2:L3,0,1)						sf=0	st=1
12	a	aY	a	aK	a	a	a	aZ		e	exw	e	ezyx	e	ekw	e	e
13	b	b	b	b	by	b	b	b		g	gYZKWX	g	g	g	g	g	g
14	cz	c	cx	c	c	c	ck	c		h	h	h	h	h	h	hzyxkw	h
15	e	exw	e	ezyx	e	ekw	e	e
16	g	gYZKWX	g	g	g	g	g	g		=ASEARCH(A2:H9,J2:L3,1,1)						sf=0	st=1
17	h	h	h	h	h	h	hzyxkw	h		g	gYZKWX	g	g	g	g	g	g
18
19	=ASEARCH(A2:H9,J2:L3,1,0)						sf=1	st=0		=ASEARCH(A2:H9,J2:L3,0,2)						sf=0	st=2
20	a	aY	a	aK	a	a	a	aZ		g	gYZKWX	g	g	g	g	g	g
21	g	gYZKWX	g	g	g	g	g	g		h	h	h	h	h	h	hzyxkw	h
22
23										=ASEARCH(A2:H9,J2:L3,1,2)						sf=1	st=2
24										g	gYZKWX	g	g	g	g	g	g
25
ASEARCH post

Cell Formulas
Range	Formula
A11,J23,J19,A19,J16,J11	A11	=FORMULATEXT(A12)
A12:H17	A12	=ASEARCH(A2:H9,J2:L3,0,0)
J12:Q14	J12	=ASEARCH(A2:H9,J2:L3,0,1)
J17:Q17	J17	=ASEARCH(A2:H9,J2:L3,1,1)
A20:H21	A20	=ASEARCH(A2:H9,J2:L3,1,0)
J20:Q21	J20	=ASEARCH(A2:H9,J2:L3,0,2)
J24:Q24	J24	=ASEARCH(A2:H9,J2:L3,1,2)
Dynamic array formulas.