In this video, we'll set up the XLOOKUP function 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.
The Excel XLOOKUP function is a modern and flexible replacement for older functions like VLOOKUP, HLOOKUP, and LOOKUP. XLOOKUP supports approximate and exact matching, wildcards (* ?) for partial matches, and lookups in vertical or horizontal...