Function Problems

How to Apply Vendor Discounts Using an Excel Formula

John WilsonJohn Wilson Oct 12, 2026 871 views

Question details

The user wants to create a formula that identifies a specific vendor and dynamically applies the correct discount percentage to a price located on a different worksheet.

How to Apply Vendor Discounts Using an Excel Formula
Product
Spreadsheets
Device & OS
not provided
Scenario
Calculating discounted prices automatically based on varying vendor discount rates across multiple worksheets.
Observed behavior
Needs a precise method or lookup function to dynamically retrieve and apply discount rates based on matching vendor names without manually adjusting formulas.
Before you start

Ensure both your price data and the vendor discount rates are clearly organized, ideally formatting your lookup data as an official Table for easier and more reliable cell referencing.

Solution 1Recommended

Use XLOOKUP with a Discount Table

XLOOKUP is the most efficient and recommended way to retrieve discount rates from a separate lookup table and apply them to your prices dynamically.

Instead of writing a long, complicated IF formula, storing your vendor names and rates in a structured lookup table prevents errors and makes updates easier.

1
Create a lookup table

On a separate worksheet, create a two-column table. Place your vendor names in the first column and their corresponding discount percentages in the second column.

2
Select the target cell

Click the cell in your main worksheet where you want the final discounted price to be calculated and displayed.

3
Enter the formula

Type the formula: =PriceCell*(1-XLOOKUP(VendorCell, DiscountTable[Vendor], DiscountTable[Rate])). Replace 'PriceCell' with the reference to your original price, and point the XLOOKUP arguments to your new discount table.

4
Press Enter to calculate

Hit Enter. The formula will look up the specific vendor, grab the discount rate, subtract it from 1 (to get the multiplier), and multiply it by the original price.

Use XLOOKUP with a Discount Table
Pro Tip: If you add new vendors in the future, you only need to update your lookup table. The formula will automatically incorporate the new data.
Advanced Spreadsheet Formulas

Calculate Vendor Discounts Seamlessly in WPS Spreadsheet

WPS Spreadsheet supports all advanced lookup and logical functions, including XLOOKUP, IF, and IFS. You can easily manage vendor tables, link cross-sheet references, and perform complex calculations with excellent performance.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your price lists.
  2. 2. Organize your lookup table: Create a dedicated worksheet to store your vendor discount rates.
  3. 3. Insert the XLOOKUP function: Use the formula bar to type =XLOOKUP() or click 'Insert Function' (fx) to select it from the Lookup & Reference category.
  4. 4. Calculate and save: Reference your price cell from the other sheet, apply the discount formula, and save your document directly as an .xlsx file.
Natively supports XLOOKUP and complex nested logical functions100% compatible with Microsoft Excel file formats (.xlsx)Lightweight architecture handles large datasets and cross-sheet references without laggingClean, tabbed interface makes flipping between price sheets and lookup tables easy
microsoft office alternative - wps office

Frequently Asked Questions

Why is my XLOOKUP formula returning an #N/A error?

An #N/A error indicates that the vendor name in your target cell does not exactly match any name in your lookup array. Check for typos or hidden trailing spaces in your text.

Can I use VLOOKUP instead of XLOOKUP for vendor discounts?

Yes, provided your vendor names are located in the very first column of your lookup table. The formula would be formatted like =PriceCell*(1-VLOOKUP(VendorCell, TableRange, 2, FALSE)).

How do I reference a price cell that is located on another worksheet?

When typing your formula, click on the tab of the other worksheet and select the target cell. The spreadsheet will automatically insert the correct sheet reference into the formula, formatted like Sheet2!A1.