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 proper Excel Table. This step isn’t necessary, but it will make it easy to add more discounts to the table without breaking the formula. Back in cell F5, I’ll enter the XLOOKUP formula we used previously. The lookup_value is E5. The lookup_array is the Quantity column, and the return_array is the Discount column. I’ll skip the not_found message, and I’ll set match_mode to -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. Now, to modify the formula to return all results at once, I just need to do one thing. 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. Now, since we are using an Excel Table for lookup data, I can easily add a new discount. Back in the results area, we immediately see a new result. One limitation you should be aware of is that you can’t use dynamic array formula inside an Excel Table, at least for now. If I try to convert the results area to a table, I’ll get a #SPILL! error.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
