This is a follow-up to my previous post
Query using two variable in two array
However, as I test further, when the combination table only has one row. The result was not as expected.
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 |
Using the following function as suggested in previous post:
=let(Σ,filter(A13:B,A13:A<>""),Λ,A4:E8, reduce(tocol(,1),sequence(rows(Σ)),lambda(a,c,vstack(a,filter(Λ,xmatch(choosecols(Λ,1),index(Σ,c))),wraprows(rept("*",5),columns(Λ),rept("*",5))))))
https://docs.google.com/spreadsheets/d/1cuNkoR4Rn7Lt0u-aCB4487JnucMKYgx0ETn6R_w7-ds/edit?usp=sharing
Expected Result
Col1 | Col2 | Col3 | Col4 | Col5 |
---|---|---|---|---|
A | aawek | ert | gh | dsfg |
B | asfdklj | vnv | ghc | dfgs |
***** | ***** | ***** | ***** | ***** |
Actual Result
Col1 | Col2 | Col3 | Col4 | Col5 |
---|---|---|---|---|
A | aawek | ert | gh | dsfg |
***** | ***** | ***** | ***** | ***** |
I tried to alter every variable in the formula but in vain.
You can use:
=REDUCE(A3:E3,SEQUENCE(COUNTA(A13:A)),LAMBDA(a,i,IFNA(VSTACK(a,
FILTER(A4:E8,REGEXMATCH(A4:A8,"^("&JOIN("|",INDEX(A13:B,i))&")$")),
"*****"),"*****")))