Search code examples
google-sheetsgoogle-sheets-formula

Spreadsheet formula using two variable in two array


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.


Solution

  • 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))&")$")),
      "*****"),"*****")))
    

    enter image description here