How to Return a Bonus Based on the Highest Matching Threshold in Excel
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 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.
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.
Click on the cell where you want the calculated bonus amount to be displayed.
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.
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 VLOOKUP with Approximate Match
If you are using an older version of Excel that does not support XLOOKUP, VLOOKUP with the TRUE parameter handles tiered data perfectly.
Use an Approximate INDEX and MATCH Formula
For complex tables where the threshold criteria are not located in the very first column of your table array, INDEX and MATCH provide a robust alternative to VLOOKUP.
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. Open your sales workbook: Launch WPS Spreadsheet and open the file containing your sales records and threshold tables.
- 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. 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. Auto-fill the column: Double-click the fill handle on your completed cell to instantly calculate bonuses for all remaining rows.

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.




