LIST.COMBINE

[tboulden]

LIST.COMBINE takes two lists and returns a single list.

This is a beginner's version of List.Combine from Power Query; although lists are comma-delimited in Power Query and display vertically, commas delimit columns in arrays and display horizontally. I've opted to have List.Combine display vertically here; if you prefer horizontally, change k,SEQUENCE(i+j) => k,SEQUENCE(1,i+j).

This is a "dumb" version as it is agnostic about the orientation of the lists its combining. A smarter version that handles 2-D and orientations is @RicoS COMBINEARRAYS.

=LAMBDA(
    list_1,
    list_2,
    LET(
        i,COUNTA(list_1),
        j,COUNTA(list_2),
        k,SEQUENCE(i+j),
        IF(
            k<=i,INDEX(list_1,k),
            INDEX(list_2,k-i)
        )
    )
)

LAMBDA_Testing_ListCombine.xlsm
	A	B	C	D	E	F
1	Anonymous:	1		1	2	3
2		2
3		3		A
4		A		B
5		B		C
6		C
7
8	Named:	1
9		2
10		3
11		A
12		B
13		C
Scratch

Cell Formulas
Range	Formula
B1:B6	B1	=LAMBDA(list₁,list₂,LET(i,COUNTA(list₁),j,COUNTA(list₂),k,SEQUENCE(i+j),IF(k<=i,INDEX(list₁,k),INDEX(list₂,k-i))))(D1#,D3#)
D1:F1	D1	={1,2,3}
D3:D5	D3	=CHAR(SEQUENCE(3,1,65))
B8:B13	B8	=List.Combine(D1#,D3#)
Dynamic array formulas.