google-sheetsgoogle-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.

SAMPLE SHEET

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

Formula I tried (map lambda), but in vain.

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

Representative Screenshot

enter image description here

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