Function Problems

How to Return a Bonus Based on the Highest Matching Threshold in Excel

Jason MillerJason Miller Oct 22, 2026 870 views

Question details

The user needs to find and return the correct bonus amount for a specific sales figure by matching it against a tiered threshold table.

Product
Excel
Device & OS
not provided
Scenario
Calculating employee bonuses based on tiered performance or sales thresholds where the exact sale amount might not exist in the table.
Observed behavior
Requires an approximate match lookup method to evaluate the sales amount against the highest threshold tier that is less than or equal to the sale amount.
Before you start

Before applying any approximate lookup function, ensure that the threshold values in your lookup table are sorted in ascending order (smallest to largest), otherwise, the formula will return incorrect or unpredictable results.

Solution 1Recommended

Use XLOOKUP for an Exact or Next Smaller Item Match

XLOOKUP is the most flexible modern function for finding the highest threshold that is less than or equal to your search value.

The XLOOKUP function allows you to explicitly define a match mode. By setting the match mode to -1, it searches for an exact match or falls back to the next smaller item, which is perfect for evaluating tiered bonuses.

1
Select the destination cell

Click on the cell where you want the calculated bonus amount to be displayed.

2
Enter the XLOOKUP formula

Type the formula: =XLOOKUP(C18, $L$18:$L$22, $M$18:$M$22, 0, -1). In this formula, C18 is the sale amount, $L$18:$L$22 is the sorted threshold column, $M$18:$M$22 is the bonus column, 0 means return nothing if an error occurs, and -1 specifies the exact match or next smaller item.

3
Apply to other rows

Press Enter to execute the formula. Click the bottom-right corner of the cell and drag the fill handle down to apply the formula to the rest of the sales amounts.

Use XLOOKUP for an Exact or Next Smaller Item Match
Use Absolute References: Remember to use absolute references (like $L$18:$L$22) for your threshold and bonus arrays by pressing F4, so the table range does not shift when you copy the formula down.
Master Spreadsheets with WPS Office

Calculate Tiered Bonuses Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced lookup functions, including VLOOKUP, XLOOKUP, and INDEX/MATCH. You can effortlessly manage your sales thresholds, calculate bonuses, and analyze data with a familiar, high-performance interface.

  1. 1. Open your sales workbook: Launch WPS Spreadsheet and open the file containing your sales records and threshold tables.
  2. 2. Check data sorting: Highlight your threshold table and use the 'Sort Ascending' feature under the Data tab to ensure the criteria are properly sequenced.
  3. 3. Insert the lookup function: Click the 'Insert Function' (fx) button next to the formula bar, search for XLOOKUP, and input your references using the dialogue box.
  4. 4. Auto-fill the column: Double-click the fill handle on your completed cell to instantly calculate bonuses for all remaining rows.
Fully compatible with Microsoft Excel formulas and the .xlsx file format.Supports modern functions like XLOOKUP for dynamic and flexible data analysis.Provides intuitive formula prompts and error-checking tools.Free, lightweight, and fast to launch across multiple operating systems.
microsoft office alternative - wps office

Frequently Asked Questions

Why is my VLOOKUP returning the wrong bonus amount for some sales?

When using an approximate match (the TRUE parameter) in VLOOKUP, your lookup column must be sorted in ascending order (from smallest to largest). If the table is unsorted or sorted descending, the formula will return incorrect or #N/A errors.

What is the difference between match mode 1 and -1 in XLOOKUP?

In XLOOKUP, a match mode of -1 finds an exact match or falls back to the next smaller item, which is required for minimum sales threshold tiers. A match mode of 1 finds an exact match or the next larger item, which is used when applying penalties based on exceeding limits.

Can I use nested IF statements instead of lookup functions for bonuses?

Yes, you can use nested IF or IFS functions to calculate bonuses. However, if your threshold table has more than 3 or 4 tiers, lookup functions like XLOOKUP or VLOOKUP are significantly easier to read, quicker to update, and less prone to syntax errors.