Marc S. answered • 01/23/24
Harvard Grad engineer will teach you number-y things!
In my video, I show a solution involving VLOOKUP, but also a superior on using XLOOKUP.
First, I made a table with the different price breaks. With minimum quantity living in D5:D10, maximum quantity of each level occupying E5:E10, then price in the array F5:F10.
If I had a sample quantity to look up in A7, in A8 (where I want my price), the function is:
=XLOOKUP(1,(A7>=D$5:D$10)*(A7<=E$5:E$10),F$5:F$10). The dollar signs make certain cell references absolute, which is important if I am planning on copying my function to other cells.
To explain what is going on:
The first argument of XLOOKUP is the lookup value, and we are making that 1, for true.
Then the second argument is lookup table. But since this is XLOOKUP and not VLOOKUP, we can make this fairly sofisticated. So we put (A7>=D5:D10)* (A7<=E5:E10) That is two conditions. So it is looking for a condition that satisfies A7 being greater than or equal to the range or minimum quantities and less than or equal to the range of maximums.
Then, the last argument is what to return, and it is whatever value corresponds in the range F5:F10.
The great part about this solution is that (with proper $s) it can be copied so multiple values are solved simultaneously. But also, you can make your search criteria as complicated as you want using different criteria. Just connect them all with multiplication symbols.
