Formula Errors

How to Fix Excel Discount Formula Not Returning 3% with INDEX and MATCH

WPS Editor Sep 29, 2026 868 views

Question details

The user needs to correct an Excel discount formula that fails to return the expected discount percentage due to incorrect lookup parameters.

How to Fix Excel Discount Formula Not Returning 3%

Product: Spreadsheet
Device & OS: not provided
Scenario: Calculating tiered product discounts based on quantity thresholds using lookup functions.
Observed behavior: The formula returns an incorrect result because HLOOKUP or INDEX uses mismatched header rows, ranges, or row offsets when evaluating quantity tiers.

Before you start

Ensure your lookup data table is properly organized, with the quantity thresholds sorted in ascending order and clearly defined product columns.

Solution 1Recommended

Use INDEX and MATCH with an IF Condition

Use a combination of IF, INDEX, and MATCH to accurately look up both the quantity tier and the product column.

A combination of INDEX and MATCH provides a robust alternative to HLOOKUP. It allows you to define a specific range for your discount percentages and find the exact row and column needed.

Select the target cell

Click the cell where you want the calculated discount percentage to appear.

Enter the IF condition for minimum quantities

Type =IF(J11<150,0, to ensure that any quantity below your lowest threshold (e.g., 150) returns a 0 discount.

Add the INDEX and MATCH logic

Complete the formula by adding INDEX($C$12:$F$21,MATCH(J11,$B$12:$B$21,1),I11)). Ensure you adjust the absolute cell references to match your specific discount table.

Calculate and fill

Press Enter to apply the formula, then drag the fill handle down to calculate the discount for the rest of your items.

Use INDEX and MATCH with an IF Condition

Approximate Match Logic: Setting the MATCH type to 1 ensures the function finds the largest value that is less than or equal to the lookup quantity, which is perfect for tiered thresholds.

Solution 2

Apply Dynamic Array Formulas with XMATCH

For newer spreadsheet versions, use dynamic formulas with IFNA and XMATCH for a concise, single-formula solution.

Advanced Formula Support

Troubleshoot Formula Errors Easily with WPS Spreadsheet

WPS Office Spreadsheet provides comprehensive support for complex lookup functions, making it simple to construct, test, and troubleshoot discount tier formulas.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your discount tables.
2. Use Error Checking: Navigate to the Formulas tab and click 'Error Checking' to identify mismatched lookup ranges.
3. Insert functions easily: Click 'Insert Function' to get guided dialogue boxes for setting up your INDEX and MATCH parameters.
4. Apply the correction: Input the correct formula ranges and press Enter to instantly resolve the incorrect discount outputs.

Fully compatible with Microsoft Excel's INDEX, MATCH, and XLOOKUP functionsBuilt-in 'Evaluate Formula' tool to step through and debug incorrect calculationsFree, lightweight alternative with an intuitive interface for professional data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why does my MATCH function return the wrong quantity tier?

If your data isn't sorted in ascending order and you use a match_type of 1 (approximate match), MATCH may return incorrect results. Ensure your quantity threshold values are sorted from smallest to largest.

What causes an HLOOKUP discount formula to fail in this scenario?

HLOOKUP searches for a value in the top horizontal row of a table. If your data layout relies on a vertical column for quantity tiers, HLOOKUP will fail. You should use VLOOKUP or an INDEX/MATCH combination instead.

How do I handle #N/A errors in my lookup formulas?

You can wrap your primary lookup formula in the IFNA or IFERROR function to specify a default return value. For example, using IFNA(your_formula, 0) will return a 0% discount when a match cannot be found.

Excel & Data Problems