[SOLVED] Query using two variable in two array

Query using two variable in two array

Please refer to my google sheet in this link. https://docs.google.com/spreadsheets/d/1cuNkoR4Rn7Lt0u-aCB4487JnucMKYgx0ETn6R_w7-ds/edit?usp=sharing

I want to query the original data, using the combination of two values in column 1 of the original data. The number of rows in original data may vary. The combination array is calculated using the Cartesian product, and has been handled by formula.

For every combination queried, a separator row is inserted and so on. The anticipated outcome is shown in expected data.

I tried using lambda and query (shown in cell M3). I also tried using filter and index, all of which appear did not accept the array value as input.

I seek your professional advice and input. Thank you.

Original Data

Col1	Col2	Col3	Col4	Col5
A	aawek	ert	gh	dsfg
B	asfdklj	vnv	ghc	dfgs
D	sadf	dsfg	sfdg	hgdfh
E	erty	dfsg	sdfg	gfhre
F	tyher	dsfg	sdfg	dfsg

Combination of Col1

1	2
A	B
A	D
A	E
A	F
B	D
B	E
B	F
D	E
D	F
E	F

Expected Data

Col1	Col2	Col3	Col4	Col5
A	aawek	ert	gh	dsfg
B	asfdklj	vnv	ghc	dfgs
*****	*****	*****	*****	*****
A	aawek	ert	gh	dsfg
D	sadf	dsfg	sfdg	hgdfh
*****	*****	*****	*****	*****
A	aawek	ert	gh	dsfg
E	erty	dfsg	sdfg	gfhre
*****	*****	*****	*****	*****
A	aawek	ert	gh	dsfg
F	tyher	dsfg	sdfg	dfsg
*****	*****	*****	*****	*****
....	....	....	....	....
....	....	....	....	....

Tried function

=arrayformula(lambda(x,y,query(A3:E8,"select * where Col1='"&X&"' or Col1='"&Y&"'",1))(A13:A22,B13:B22  ))

Solution

You may try:

=let(Σ,filter(A13:B,A13:A<>""),Λ,A4:E8,
     reduce(tocol(,1),sequence(rows(Σ)),lambda(a,c,vstack(a,filter(Λ,xmatch(choosecols(Λ,1),chooserows(Σ,c))),wraprows(rept("*",5),columns(Λ),rept("*",5))))))