How to Apply Vendor Discounts Using an Excel Formula
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.

- 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.
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.
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.
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.
Click the cell in your main worksheet where you want the final discounted price to be calculated and displayed.
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.
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 the IFS Function for Simple Discount Tiers
If you only have two or three vendors, you can use the IFS function directly in the cell without creating a separate lookup table.
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. Open your workbook: Launch WPS Spreadsheet and open the file containing your price lists.
- 2. Organize your lookup table: Create a dedicated worksheet to store your vendor discount rates.
- 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. Calculate and save: Reference your price cell from the other sheet, apply the discount formula, and save your document directly as an .xlsx file.

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.




