Dynamic Arrays
Return multiple lookup matches into separate rows or columns (FILTER + TRANSPOSE)
Turn one-to-many lookups (all reps in a territory or all SKUs on an order) into vertical lists with FILTER or horizontal columns with TRANSPOSE(FILTER(...)).
Why XLOOKUP and VLOOKUP stop at the first match—and how FILTER solves it
FILTER returns all matching source rows, unlike a lookup that selects a single matching row.
FILTER(Names, Dept="Eng", "None") spills ALL matching employees vertically down rows. When building a compact summary table with one department per row, wrapping FILTER inside TRANSPOSE(FILTER(Names, Dept=RowDept, "")) pivots those matches horizontally across adjacent columns!
| Dept Summary | Match Col 1 | Match Col 2 | Match Col 3 |
|---|---|---|---|
| Eng | Ada | Cy | Eva |
| Sales | Ben | — | — |
| Legal (0 matches) | (blank / "None") | — | — |
Horizontal multi-match and sorted multi-match formulas
Wrap FILTER in SORT(...) before TRANSPOSE so horizontal match columns always appear in alphabetical order.
=TRANSPOSE(SORT(FILTER(B2:B6, A2:A6="Eng", "None")))Count how many horizontal columns spilled
COLUMNS(...) measures the exact width of the transposed match array.
=COLUMNS(TRANSPOSE(FILTER(B2:B6, A2:A6="Eng", "None")))Inputs and interpretation
Enter the spilling formula in empty cells outside the source data. XLOOKUP selects a matching row (first by default, or last with an appropriate search mode) and may return several columns; FILTER returns every matching row. COLUMNS measures output width, not match count: the None fallback has width 1 even with zero matches.
Try the example
Use the original values or change them to test your understanding.