Dynamic Arrays & XLOOKUP
Use the modern formula engine: one formula that returns many results, and the lookup function that replaces VLOOKUP.
By the end of this lesson you can
- Explain what a dynamic array is and what 'spill' means
- Use FILTER, SORT, and UNIQUE to build a live list from a data set
- Write an XLOOKUP and say why it is safer than VLOOKUP
- Recognize the #SPILL! and #NAME? errors and know what each one means
Lesson Notes
Read through the key concepts before you try the challenge.
One formula, many answers
You maintain the supply tracker at Lakeside Medical Associates.
Your supervisor wants a live list of every item below its reorder level, sorted by category, on its own sheet. The old way is a helper column, a filter, and a copy-paste every time the data changes — which means it is stale within a day and someone eventually forgets to redo it.
Your task: Write one formula that produces the whole list and updates itself.
A dynamic array formula returns more than one result. You write it in a single cell and Excel fills — spills — the results into the cells beneath and beside it automatically. The spilled range has a blue border when you select the formula cell, and you cannot type into it; it belongs to the formula.
This changes how you build a spreadsheet. Where you would previously have copied a formula down 900 rows, one formula now covers the range and grows or shrinks as the source data changes. Nothing to re-copy, and no risk of a row at the bottom being missed.
| Function | Returns | Example |
|---|---|---|
| FILTER | Only the rows meeting a condition | =FILTER(A2:E900, D2:D900<E2:E900) |
| SORT | A range sorted by a column you choose | =SORT(A2:E900, 2, 1) |
| UNIQUE | The distinct values in a range | =UNIQUE(B2:B900) |
| SEQUENCE | A list of numbers | =SEQUENCE(12) for 1 to 12 |
| XLOOKUP | A matching value from another range | =XLOOKUP(A2, Items, Prices) |
XLOOKUP, and why VLOOKUP was worth replacing
VLOOKUP has three long-standing problems. It can only look to the right of the lookup column. It refers to the return column by position number, so inserting a column silently breaks it. And it defaults to an approximate match, which means a typo returns a wrong answer rather than an error.
XLOOKUP fixes all three. It takes the lookup range and the return range as separate arguments, so direction does not matter and inserting a column cannot break it. It defaults to an exact match. And it has a built-in argument for what to return when nothing is found.
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Formula | =VLOOKUP(A2, Items!A:D, 4, FALSE) | =XLOOKUP(A2, Items[Name], Items[Price]) |
| Can look left | No | Yes |
| Breaks if a column is inserted | Yes — the position number is now wrong | No — the return range moves with it |
| Default match type | Approximate, unless you add FALSE | Exact |
| Handles 'not found' | Returns #N/A; needs IFERROR wrapped around it | Fourth argument: =XLOOKUP(A2, names, prices, "Not found") |
Building the live reorder list
Produce a self-updating list of every supply item below its reorder level, sorted by category.
- 1
Start with FILTER alone and check what it returns.
=FILTER(A2:E900, D2:D900<E2:E900) returns the matching rows and nothing else. Building one function at a time is how you find which part is wrong when something misbehaves — nesting first and debugging afterwards is much harder.
- 2
Add a message for the case where nothing matches.
=FILTER(A2:E900, D2:D900<E2:E900, "All items in stock") — without the third argument, a fully stocked week returns #CALC!, which looks like a broken spreadsheet rather than good news.
- 3
Wrap it in SORT to order by category.
=SORT(FILTER(...), 2, 1) sorts the filtered result by its second column, ascending. Sorting the output rather than the source means the underlying data is never rearranged, so nothing can be scrambled.
- 4
Leave the cells below and to the right of the formula empty.
The result needs room to spill. Anything already sitting in the spill range produces #SPILL!, and the fix is to clear those cells — not to change the formula.
Result: One formula that produces the full reorder list and updates the moment any quantity changes.
Build dynamic array formulas one function at a time, always give FILTER an if-empty argument, and keep the spill range clear.
| Error | Means | Fix |
|---|---|---|
| #SPILL! | Something is blocking the range the result needs | Clear the cells in the spill range |
| #NAME? | Excel does not recognize the function name | Check the spelling — or check whether your version has it |
| #CALC! | A dynamic array returned an empty result | Add the if-empty argument to FILTER |
You write =XLOOKUP(A2, Items, Prices) and Excel returns #NAME?. What is the most likely cause?
Challenge
Apply what you've learned in this lesson.
Build a live summary sheet that needs no maintenance.
- In a workbook with a supply table, use UNIQUE to produce a list of every distinct category on a new sheet. Add a category to the source data and confirm the list grows on its own.
- Beside it, use XLOOKUP to pull each category's reorder threshold from a lookup table. Include a fourth argument so a missing category shows a message rather than #N/A.
- Write a FILTER formula returning every item below its reorder level, sorted by category with SORT. Give it an if-empty message.
- Deliberately type something into a cell inside the spill range. Record the error you get and what you had to do to clear it.
Finished this lesson?
Progress is saved in this browser only. It is not a grade — official progress lives in Brightspace.