Function Problems

Excel Formula to Return a Discount Based on Selected Volume

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs a spreadsheet lookup formula that takes a selected volume quantity and matches it against a reference table to return the corresponding discount percentage.

Product: Spreadsheet
Device & OS: not provided
Scenario: Calculating tiered or specific volume discounts by looking up a user-selected volume amount in a predefined data table.
Observed behavior: A VLOOKUP formula matches the volume in the input column with the lookup table, returning the correct discount value from the adjacent column.

Before you start

Ensure your volume reference table is properly set up, with the lookup values (volumes) in the first column and the return values (discounts) in the second column.

Solution 1Recommended

Use the VLOOKUP Function for an Exact Match

The VLOOKUP function is the most efficient way to search for a specific volume value in a table and return the corresponding discount percentage.

By setting the match type to FALSE, you ensure that the formula only returns a discount if the exact volume is found in the table. You must also use absolute references (dollar signs) to lock the table array so the range does not shift when you copy the formula to other rows.

Select the target cell

Click on the cell where you want the discount percentage to appear, such as cell AE2 in your worksheet.

Enter the VLOOKUP formula

Type the formula =VLOOKUP(AD2,$BB$2:$BC$10,2,FALSE) into the formula bar. Here, AD2 is the volume you are looking up, $BB$2:$BC$10 is your fixed reference table, and 2 tells Excel to return the value from the second column of that table.

Apply the formula to remaining rows

Press Enter to calculate the result. Then, click the small square at the bottom-right corner of cell AE2 and drag it down to fill the formula for the rest of your data.

Locking the Reference Table: Adding the $ signs to the table array ($BB$2:$BC$10) creates an absolute reference, which is critical for preventing errors when you drag the formula down the column.

WPS Spreadsheet

Easily Calculate Volume Discounts with WPS Spreadsheet

WPS Spreadsheet provides powerful built-in functions like VLOOKUP to help you quickly process sales data, calculate discounts, and streamline your workflow.

1. Open your data file: Launch WPS Spreadsheet and open the document containing your volume and discount data.
2. Insert the VLOOKUP formula: Select the output cell and type =VLOOKUP(. WPS will display an intelligent tooltip guiding you through the required arguments.
3. Select your ranges: Click the cell containing your volume, highlight your reference table, type 2 for the column index, and select FALSE for an exact match.
4. Complete the calculation: Press Enter to return the discount percentage, and double-click the fill handle to apply it to all rows instantly.

100% compatible with Microsoft Excel formulas, functions, and file formats.Intelligent formula suggestions that help you prevent syntax errors while typing.Lightweight software that processes large data tables quickly and smoothly.Free to use with a familiar interface that requires no learning curve.

Frequently Asked Questions

Why does my VLOOKUP formula return an #N/A error?

The #N/A error occurs if the selected volume cannot be found in the first column of your reference table. Verify that the volume exists in the table, check for hidden spaces, and ensure you are using the correct cell references.

How can I return a discount for a volume tier instead of an exact match?

If you want to apply a discount based on a range (e.g., any volume between 10 and 20 gets a 5% discount), change the last argument of your VLOOKUP formula from FALSE to TRUE. Ensure the first column of your lookup table is sorted in ascending order for this to work correctly.

How do I format the returned decimal as a percentage?

After applying the VLOOKUP formula, the result might display as a decimal (e.g., 0.15). To format it as a percentage, select the cell or column, go to the Home tab on the top ribbon, and click the '%' (Percent Style) icon.

Can I use XLOOKUP instead of VLOOKUP for this task?

Yes, if your spreadsheet version supports it, you can use =XLOOKUP(AD2, $BB$2:$BB$10, $BC$2:$BC$10). XLOOKUP is often preferred because it automatically defaults to an exact match and does not require a column index number.

Excel & Data Problems