Function Problems

How to Assign Costs Based on Quantity Ranges in Excel

Algirdas Jasaitis Oct 1, 2026 868 views

Question details

The user needs to calculate and assign a specific cost based on dynamic quantity ranges (e.g., 1-30, 30-60, 60-150) in a spreadsheet.

How to Assign Costs Based on Quantity Ranges in Excel

Product: Excel
Device & OS: not provided
Scenario: Setting up a pricing or cost calculation sheet where unit costs change depending on the total quantity ordered or processed.
Observed behavior: The spreadsheet needs to automatically evaluate a quantity input and return the correct cost associated with that specific quantity tier without manual data entry.

Before you start

Ensure that your minimum quantity thresholds are defined clearly and sorted in ascending order, as approximate match functions rely on ascending order to calculate correctly.

Solution 1Recommended

Use VLOOKUP with an Approximate Match

Create a lookup table and use the VLOOKUP function set to TRUE to automatically find the correct cost for any given quantity.

To assign costs across continuous ranges, the most efficient method is using VLOOKUP configured for an approximate match. This prevents you from having to write a massive, nested IF formula.

Create a Lookup Table

Set up a lookup table in a blank area of your sheet with your minimum quantity thresholds in one column and the corresponding costs in the next. For example, in cells I1 to J3, enter 1 and $30, 30 and $28, and 60 and $26.

Select the Output Cell

Click on the cell where you want the calculated cost to appear (for example, next to your first quantity entry in cell B2).

Enter the Formula

Type the formula =IFERROR(VLOOKUP(A2,$I$1:$J$5,2,TRUE),0) into the formula bar. Replace 'A2' with the cell containing your quantity, and '$I$1:$J$5' with the absolute reference of your new lookup table.

Apply Formula to Other Cells

Press Enter to calculate the cost. Click on the cell again, grab the small square fill handle in the bottom right corner, and drag it down to apply the formula to the rest of your data.

Handling Boundaries: A VLOOKUP TRUE match returns the value of the largest threshold that is less than or equal to your lookup value. Ensure you clarify whether a boundary quantity like exactly 30 belongs to the $30 tier or the $28 tier when structuring your table.

Efficient Data Processing

Easily Manage Quantity-Based Pricing with WPS Spreadsheet

WPS Office offers a powerful, free Spreadsheet application that fully supports advanced data analysis functions like VLOOKUP and IFERROR, allowing you to manage complex tiered pricing models with ease.

1. Open Your Pricing Workbook: Launch WPS Spreadsheet and open your document containing the quantity data.
2. Set Up Your Thresholds: Create a clean, two-column reference table with your minimum quantities sorted from smallest to largest next to their respective costs.
3. Apply the Formula: Use the VLOOKUP formula with the TRUE parameter to automatically cross-reference and assign the accurate costs to your main data set.

Fully compatible with Microsoft Excel (.xlsx) formats, formulas, and functions.Flawlessly executes approximate and exact match lookup formulas for dynamic pricing.Lightweight, fast, and offers a highly familiar user interface for a seamless transition.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP formula returning an incorrect cost for my quantity?

This usually happens if your lookup table is not sorted in ascending order by the minimum quantity column. An approximate match VLOOKUP requires the first column of your table array to be sorted from smallest to largest to evaluate ranges properly.

Can I use the IFS function instead of VLOOKUP for quantity ranges?

Yes. If you are using a newer version of Excel or WPS Office, you can use the IFS function (e.g., =IFS(A2<30, 30, A2<60, 28, A2>=60, 26)). This avoids creating a separate lookup table, though a table with VLOOKUP is generally easier to maintain if your pricing tiers change frequently.

What does the IFERROR function do in this specific formula?

The IFERROR function acts as a safety net. It catches any errors that VLOOKUP might produce (for example, if the lookup quantity is zero or smaller than your lowest threshold) and returns a clean default value, like 0, instead of displaying an #N/A error on your sheet.

Excel & Data Problems