Search code examples
google-sheets-formula

Use Lambda to stack multiple query array

I want to stack multiple query result in a single array.

Table 1 to 3 share the same column structure. In column D, they are either "Y" or "N". I want to have only the "Y" row to be shown. My anticipated results are shown below.

https://docs.google.com/spreadsheets/d/1d0bGT_asoAbJB1vnecwuEcoADI4taZ4ZMbYwsL5DckA/edit?usp=sharing

enter image description here

Table1

A B C D
DIO DASF SDFA Y
SADF ERT RWET N
DGGF SAF FDG Y
DFG ERT FXG Y
FG DFG 34RW N

Table2

A B C D
SAF H HFH N
GFH W FG Y
N GJ WR Y
FGH GG BNV N
2WEF FDG ZZEAS N

Table3

A B C D
FAG HUIO ERE45 N
YTV3 RTERTY IO N
GASFAS YTUYTUI UIP Y
SADFSA SA IOP Y

Anticipated Result

A B C D
DIO DASF SDFA Y
DGGF SAF FDG Y
DFG ERT FXG Y
A B C D
GFH W FG Y
N GJ WR Y
A B C D
GASFAS YTUYTUI UIP Y
SADFSA SA IOP Y

I tried using map lambda, but in vain.

=map({"Table1";"Table2";"Table3"},LAMBDA(colA,arrayformula(vstack(query(indirect(colA),"select * where Col4='Y'")))))

Grateful if you could help, thanks.

Solution

  • Here's one approach you may test out:

    =reduce(tocol(,1),J8:J10,lambda(a,c,vstack(a,query(indirect(c),"Where Col4='Y'",1))))

    enter image description here