In this worksheet, we have an example we looked at previously.
On the left, we have quantity-based discounts, and on the right, we have some random quantities.
Let’s set up XLOOKUP to return all results in a single dynamic array.
As a first step, I’m going to convert the table on the left into a properExcel Table.
Back in cell F5, I’ll enter the XLOOKUP formula we used previously.
Thelookup_arrayis the Quantity column, and thereturn_arrayis the Discount column.
I’ll skip thenot_foundmessage, and I’ll setmatch_modeto -1 for exact match or next smallest.
When I enter the formula, and copy it down, we get correct results.
This works fine, but dynamic arrays give us another way to calculate all results with a single formula.
First, I’ll undo that copy.
Instead of providing E5, I provide all lookup values in the range E5:E11.
When I press Enter, all results spill onto the worksheet in a single dynamic array.
Like all dynamic arrays, all cells display the same formula, but only the first cell is editable.
Back in the results area, we immediately see a new result.
