Function Problems

How to Use an Excel Formula to Select Quantity-Based Pricing Tiers

Camila Milosovich Sep 29, 2026 868 views

Question details

The user needs to set up a formula to automatically retrieve the correct unit price from a pricing tier table based on an inputted quantity.

How to Select Quantity-Based Pricing Tiers Using Excel Formulas

Product: Excel
Device & OS: not provided
Scenario: Calculating accurate unit costs based on volume, where the price per unit decreases as the order quantity reaches higher minimum thresholds.
Observed behavior: A lookup formula is required to scan a tiered pricing table and return the price for the highest tier whose minimum quantity is less than or equal to the entered order quantity.

Before you start

Ensure your pricing tier table is set up correctly: the first column must contain the minimum quantities, and these values must be sorted strictly in ascending order (e.g., 0, 10, 50, 100) for approximate match functions to work.

Solution 1Recommended

Use the XLOOKUP Function (Excel 2021 & Microsoft 365)

XLOOKUP is the modern, recommended approach for finding pricing tiers because of its intuitive match mode settings.

The XLOOKUP function is highly flexible and allows you to specify a match mode that looks for the exact quantity or defaults to the next smaller item. This perfectly replicates a minimum threshold pricing tier.

Prepare your tier table

Create a table named 'TierTable' with your minimum units in the first column and the corresponding price per unit in the second column. Ensure the minimum units are sorted in ascending order.

Enter the order quantity

Select an empty cell (for example, B2) and type the quantity ordered by the customer.

Apply the XLOOKUP formula

Click the cell where you want the unit price to appear and type: =XLOOKUP(B2, TierTable[Min Units], TierTable[Price per Unit], "Invalid", -1).

Calculate the total cost

To find the total cost of the items, multiply the retrieved unit price by the order quantity using a formula like =B2*C2 (assuming the unit price is returned in C2).

Use the XLOOKUP Function (Excel 2021 & Microsoft 365)

Match Mode Value: Setting the fifth argument to -1 tells XLOOKUP to return an exact match, or if an exact match isn't found, the next smaller value. This is exactly what you need for minimum quantity thresholds.

Solution 2

Use the VLOOKUP Function (Older Excel Versions)

For older versions of Excel that do not support XLOOKUP, VLOOKUP combined with an approximate match handles pricing tiers flawlessly.

Seamless Formula Support

Calculate Tiered Pricing Effortlessly in WPS Office

WPS Spreadsheet fully supports advanced lookup functions like XLOOKUP and VLOOKUP, allowing you to build dynamic pricing models, quote calculators, and automated invoices without any hassle.

1. Open WPS Spreadsheet: Launch WPS Office and open your Excel workbook containing the pricing tier data.
2. Format your table: Ensure your tier threshold column is sorted from smallest to largest using the built-in Sort & Filter tools.
3. Insert the lookup function: Type =XLOOKUP or =VLOOKUP directly into the formula bar and select your target cells, just as you would in Microsoft Excel.
4. Calculate and save: Multiply the output by the volume for the total cost, then seamlessly save your file in .xlsx format.

Fully compatible with Microsoft Excel formulas (.xlsx), ensuring XLOOKUP and VLOOKUP work perfectly.100% free and lightweight alternative with a familiar user interface.Includes a built-in formula builder to easily construct complex pricing calculations.Seamless file migration and saving across Windows, Mac, Linux, and mobile.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP returning the wrong price tier?

When using VLOOKUP for an approximate match (with the last argument set to TRUE), the first column of your lookup table must be sorted in ascending order (smallest to largest). If your minimum quantities are unsorted or descending, VLOOKUP will return erratic or incorrect results.

Can I use an exact match for tiered pricing?

No, an exact match (FALSE in VLOOKUP or 0 in XLOOKUP) will only work if the customer orders the exact minimum quantity listed in the table (e.g., exactly 10 or exactly 50). Because orders usually fall between tiers (e.g., 25), you must use an approximate match to round down to the nearest minimum threshold.

How do I calculate the total cost once the unit price is found?

After your XLOOKUP or VLOOKUP formula successfully retrieves the unit price into a cell, simply use a multiplication formula to calculate the total. If the order quantity is in B2 and the retrieved unit price is in C2, enter `=B2*C2` in a new cell for the total material cost.

What does the -1 mean at the end of the XLOOKUP formula?

The `-1` represents XLOOKUP's 'match mode'. It instructs the function to first search for an exact match. If an exact match is not found, it returns the next smaller item. This behavior is ideal for selecting the correct bracket in a minimum-quantity pricing tier.

Excel & Data Problems