Since Microsoft 365 rolled out dynamic arrays, one formula can return many results that “spill” into neighbouring cells. Four functions cover most everyday needs.

UNIQUE — a clean list without Remove Duplicates

=UNIQUE(B2:B500)                     ' every distinct region
=SORT(UNIQUE(B2:B500))               ' sorted A–Z

FILTER — rows that match, live

=FILTER(A2:E500, B2:B500="North", "No rows")
=FILTER(A2:E500, (B2:B500="North")*(E2:E500>50000))    ' AND
=FILTER(A2:E500, (B2:B500="North")+(B2:B500="East"))   ' OR

SORT and SORTBY

=SORT(A2:E500, 5, -1)                ' by column 5, largest first
=SORTBY(A2:A500, E2:E500, -1)        ' names ordered by amount

SEQUENCE — numbers on demand

=SEQUENCE(12)                        ' 1 to 12 down
=SEQUENCE(1, 7, TODAY())             ' the next 7 dates across

The spill reference #

If UNIQUE is in G2, refer to its whole result with G2#. A dropdown source of =$G$2# grows automatically when a new region appears.

#SPILL! error

The formula needs empty cells to spill into. Clear whatever is in the way (even a space), or move the formula. Spills also do not work inside Excel Tables — put them beside the table.