Exceljet

Quick, clean, and to the point

XLOOKUP with multiple lookup values

In this video, we'll set up XLOOKUP to return multiple values in a dynamic array.

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.

=XLOOKUP(E5,Table1[Quantity],Table1[Discount],,-1)

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 range E5:E11.

=XLOOKUP(E5:E11,Table1[Quantity],Table1[Discount],,-1)

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.

Dynamic Array Formulas are available in Excel 365 only.

Related shortcuts

Author 
Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.