lambda - Creating Nested List with Google Sheet Formulas from Pre-Made Tables (on 3 levels) - Stack Overflow

Previously, I had asked how to improve my dynamic nested list formula based on two levels.

Now, what if I had a third level of sub-subitems? I tried adapting the previously given formulas, but no luck.

For example, if I have a list of items

A	B
1	Item 1
2	Item 2
3	Item 3

Previously, I had asked how to improve my dynamic nested list formula based on two levels.

Now, what if I had a third level of sub-subitems? I tried adapting the previously given formulas, but no luck.

For example, if I have a list of items

A	B
1	Item 1
2	Item 2
3	Item 3

A list of subitems

C	D
Subitem 1	Item 1
Subitem 2	Item 1
Subitem 3	Item 2
Subitem 4	Item 3
Subitem 5	Item 3

And a list of sub-subitems

E	F
Sub-subitem a	Subitem 1
Sub-subitem b	Subitem 1
Sub-subitem c	Subitem 3
Sub-subitem d	Subitem 3
Sub-subitem e	Subitem 5

My result should be:

G
Item 1
Subitem 1
Sub-subitem a
Sub-subitem b
Subitem 2
Item 2
Subitem 3
Sub-subitem c
Sub-subitem d
Item 3
Subitem 4
Subitem 5
Sub-subitem e

• google-sheets
• lambda
• google-sheets-formula

Share Improve this question Follow edited Feb 3 at 17:26 mgmussi asked Feb 3 at 17:06 mgmussimgmussi 56811 gold badge44 silver badges2323 bronze badges 2

• where did you get Sub-subitem a/b/c/d/e from? – z.. Commented Feb 3 at 17:10
• There, I had copied the wrong table for columns E and F – mgmussi Commented Feb 3 at 17:27

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

You can use REDUCE with VSTACK to apply this kind of recursive logic. TOCOL

=TOCOL(
  REDUCE(TOCOL(,1),TOCOL(B2:B,1),LAMBDA(a,item,VSTACK(a,item,
    REDUCE(TOCOL(,1),IFNA(FILTER(C2:C,D2:D=item)),LAMBDA(b,subitem,
      VSTACK(b,subitem,IFNA(FILTER(E2:E,F2:F=subitem))))))))
,1)