The range G6:L7 contains quantity-based discounts.
Column B contains random quantities used when testing the formula.
This problem can be easily solved with the XLOOKUP function or the HLOOKUP function.
Both methods are explained below.
The syntax used for horizontal lookups is the same as for vertical lookups.
This is not required with named ranges because they automatically behave as absolute references.
If an exact match is found, the corresponding discount in row 7 is returned.
When an exact match isnot found, the discount associated with thenext smallestquantity is returned.
HLOOKUP formula
Working with HLOOKUP is similar to working with theVLOOKUP function.
Both functions require theentire lookup tableas thetable_arrayargument and use an index number to specify return values.
This problem can also be solved with theHLOOKUP functionlike this:
Wheredatais the named range G6:L7.
Download the workbook and try it out yourself.
XLOOKUP supports approximate and exact matching, wildcards (* ?)
HLOOKUP supports approximate and exact matching, and…
