How to Use Excel Formulas to Match a Retailer and Apply a Discount
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.

- 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.
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.
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.
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).
Click on the first empty cell where you want the discounted cost to appear.
Type the formula =C2:C7*(1-XLOOKUP(A2:A7,F2:F7,G2:G7,0)) into the formula bar.
Press Enter. The formula will automatically spill down and calculate the discounted costs for all referenced rows.

Use VLOOKUP with IFERROR (Older Excel Versions)
Best for backward compatibility with older versions of Excel that do not support XLOOKUP or dynamic arrays.
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. Open your dataset: Launch WPS Spreadsheet and open your document containing the retail data.
- 2. Select the output cell: Click on the cell where the discounted price should be displayed.
- 3. Input the formula: Type your preferred XLOOKUP or VLOOKUP formula matching your retailer lookup table.
- 4. Apply across rows: Press Enter, and if not using dynamic arrays, drag the fill handle to apply the calculation across all rows.

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.




