How Do I Capture Which Price to Charge Based on Quantity Sold

I hope you are all having a good day. I have a table that has ranges of quantities and prices per range. For instance, from 1 to 1000 units, the price is $1 per. From 1001 to 5000 units, the price is $0.9 per. Note that I am not saying that, if for example we had 2000 units, that we'd charge $1 for the first 1000 and $0.9 for the next 1000. I am saying that the charge will be $0.9 for each unit since they fall into that second pricing tier. What formula would you use to capture which pricing scheme to use based on my quantity variable?

Originally Posted by MrShorty

I would use one of Excel's lookup functions. VLOOKUP() seems to be the most common starting point. https://support.office.com/en-us/art...8-93a18ad188a1 Unfortunately, almost all examples in the help files and on the internet are for "exact match" (4th argument of VLOOKUP=FALSE) type lookups, where you will want the "approximate match" 4th argument=TRUE be-sure-sort-the-lookup-table/column option. I created this example of how the binary search "approximate match" algorithm works here as part of another spreadsheet: https://www.excelforum.com/tips-and-...p-example.html

Thank you so much!