EXPAND() function

SanthoshKunder 

I use EXPAND for a few reasons:

1. Avoiding 'Jagged' arrays.  The by-the-book use here, nothing fancy. You might also see error checking used in place of EXPAND as such:

=IFNA(VSTACK(Regions,Oceans),"")

2. Adding padding to an array. In this case, blanks after each item:

=TOCOL(EXPAND(names,,2,""))

3. Unpacking scalars.  Essentially, this function repeats a given item x number of times based on the color looked up:

=LET(
    Unpack, LAMBDA(a, v,
        LET(
            k, XLOOKUP(v, colors, counts),
            VSTACK(a, EXPAND(v, k, , v))
        )
    ),
    REDUCE("Colors Unpacked", colors, Unpack)
)

EXPAND's weakness is the inability to accept arrays in the pad_with argument.

SanthoshKunder One useful scenario I can think of would be what's shown in the picture below. I'm sure there are more.