Function Problems

How to Use Excel Formulas to Match a Retailer and Apply a Discount

John WilsonJohn Wilson Nov 10, 2026 871 views

Question details

The user needs to match a retailer's discount percentage from a lookup table and use it to calculate the final discounted cost across multiple rows of data.

How to Match a Retailer and Apply a Discount Using Excel Formulas
Product
Excel
Device & OS
not provided
Scenario
Calculating discounted costs based on retailer-specific discount percentages stored in a separate lookup table.
Observed behavior
The user wants to automate the calculation of discounted costs across many rows using dynamic arrays or traditional lookup functions like XLOOKUP or VLOOKUP.
Before you start

Ensure your retailer data and discount percentages are organized in a clear lookup table (e.g., columns F and G) and that your main data table has a corresponding column for retailer names to match against.

Solution 1Recommended

Use XLOOKUP with Dynamic Arrays (Modern Excel & WPS Office)

Ideal for newer versions supporting dynamic arrays. XLOOKUP simplifies the formula and automatically spills the results down the column.

XLOOKUP is a powerful, modern replacement for VLOOKUP. By combining it with dynamic array features, you only need to type the formula once in the top cell, and it will calculate the discount for the entire column automatically.

1
Identify your data ranges

Locate your main data range (e.g., Retailers in A2:A7, Original Costs in C2:C7) and your lookup table (e.g., Retailers in F2:F7, Discounts in G2:G7).

2
Select the target cell

Click on the first empty cell where you want the discounted cost to appear.

3
Enter the XLOOKUP formula

Type the formula =C2:C7*(1-XLOOKUP(A2:A7,F2:F7,G2:G7,0)) into the formula bar.

4
Apply the formula

Press Enter. The formula will automatically spill down and calculate the discounted costs for all referenced rows.

Use XLOOKUP with Dynamic Arrays (Modern Excel & WPS Office)
Spill Array Behavior: Because this is a dynamic array formula, make sure the cells below your target cell are empty. If there is existing data blocking the spill range, you will get a #SPILL! error.
WPS Spreadsheet Formula Solution

Easily Apply Discount Formulas with WPS Spreadsheet

WPS Office provides a highly compatible and free alternative to Microsoft Excel. WPS Spreadsheet fully supports advanced functions like XLOOKUP, VLOOKUP, and dynamic arrays, allowing you to seamlessly calculate retail discounts.

  1. 1. Open your dataset: Launch WPS Spreadsheet and open your document containing the retail data.
  2. 2. Select the output cell: Click on the cell where the discounted price should be displayed.
  3. 3. Input the formula: Type your preferred XLOOKUP or VLOOKUP formula matching your retailer lookup table.
  4. 4. Apply across rows: Press Enter, and if not using dynamic arrays, drag the fill handle to apply the calculation across all rows.
Fully compatible with Microsoft Excel formulas (.xlsx files).Supports modern functions like XLOOKUP and dynamic arrays.Free and lightweight office suite for Windows, Mac, and Linux.Intuitive interface for managing large retail datasets and lookup tables.
microsoft office alternative - wps office

Frequently Asked Questions

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

This happens when the retailer name in your main table doesn't exactly match the name in your lookup table. Check for extra spaces or spelling errors. You can also use the IFERROR function to output a default value like 0% if no match is found.

What does the '0' at the end of the XLOOKUP formula do?

The '0' instructs the XLOOKUP function on what to return if a match is not found. In this discount formula, it returns 0 (meaning a 0% discount), preventing the calculation from breaking with an error message.

Can I use these discount formulas across different worksheet tabs?

Yes. Simply click on the target tab while selecting your lookup ranges, and the spreadsheet will automatically add the sheet name reference (e.g., 'LookupTable!F2:F7') to your formula.

Why isn't my XLOOKUP formula spilling down the column?

Spill behavior requires empty cells below the formula. If there is any existing data or text blocking the spill range, you will encounter a #SPILL! error. Clear the cells below the formula to fix this.