Explanation
In the example shown, the table in B4:C13 contains quantity-based discounts.
As the quantity increases, the discount also increases.
The table in E4:F10 shows the discount returned by XLOOKUP for several random quantities.
XLOOKUP is configured to use the quantity in column E to find the appropriate discount.
If no match is found, XLOOKUP will display nothing instead of #N/A.
At each row, XLOOKUP looks up the quantity in column E in the range B5:B13.
When an exact match is found, the corresponding discount in column C is returned.
When an exact match is not found, the discount associated with the next smallest quantity is returned.
XLOOKUP supports approximate and exact matching, wildcards (* ?)
for partial matches, and lookups in vertical or horizontal ranges….
