Formula Errors

How to Use an Excel LET Formula for Multiple Commission Rates

Adam Davis Oct 9, 2026 870 views

Question details

The user needs to calculate commission rates using LET and XLOOKUP, applying a standard 30% rate but overriding it with a 10% rate for two specific products, all within a single formula without adding helper columns.

How to Use an Excel LET Formula for Multiple Commission Rates

Product: Excel
Device & OS: not provided
Scenario: Calculating supplier commissions where specific products require a different rate than the standard supplier percentage.
Observed behavior: The current LET and XLOOKUP calculation returns a blanket 30% commission for the supplier, failing to account for the required 10% rate for specific exception products.

Before you start

Ensure you have a clear list or cell reference for the exception products, and that your spreadsheet software supports modern dynamic array functions like LET and XLOOKUP.

Solution 1Recommended

Embed an IF Condition inside the LET Formula

Modify the final calculation step of your LET function to include an IF statement that tests for the exception products before applying the standard commission rate.

By defining the product name and the base rate as variables within the LET function, you can efficiently route the calculation to use either 10% for specific items or the standard 30% rate for the rest.

Define the Variables

Select the target cell and start your LET function by defining your variables. For example, assign the product cell to a name like 'Prod' and your XLOOKUP formula to 'BaseRate'.

Add the Exception Logic

For the calculation argument of the LET function, insert an IF statement combined with an OR function to check if the product matches the exceptions (e.g., values in column I or J).

Set the Commission Rates

Configure the true condition of the IF statement to return the 10% (0.1) exception rate, and the false condition to return the standard 'BaseRate' variable.

Finalize and Apply

Close the parentheses for your LET function. A simplified example looks like: =LET(Prod, A2, BaseRate, 0.3, IF(OR(Prod=I2, Prod=J2), 0.1, BaseRate)). Press Enter to apply.

Embed an IF Condition inside the LET Formula

Formula Validation: Because the exact formula depends heavily on your specific table structure, consider testing the logic on a small sample of rows to ensure the XLOOKUP and IF conditions interact correctly.

Advanced WPS Spreadsheet Formulas

Calculate Complex Multiple Commission Rates in WPS Spreadsheet

WPS Spreadsheet fully supports modern dynamic array functions like LET, XLOOKUP, and IFS. You can seamlessly build advanced formulas to handle varying commission rates and product exceptions in a single cell, keeping your workbooks clean and efficient.

1. Open Your Workbook: Launch WPS Office and open your commission tracking spreadsheet in WPS Spreadsheet.
2. Start the LET Function: Select the cell for your commission output and type =LET( to begin defining your custom variables.
3. Combine XLOOKUP and IF: Define your product exception variables and nest an IF function to apply the 10% rate for specific exceptions and 30% for standard items.
4. Apply and Fill: Press Enter to calculate the commission instantly, then drag the fill handle down to apply the formula to the rest of the column.

100% compatibility with Microsoft Excel formulas, including LET, XLOOKUP, and nested IFs.Built-in formula evaluation features to help you troubleshoot and debug complex nested conditions.Free, lightweight, and easy-to-use alternative with a highly familiar spreadsheet interface.

microsoft office alternative - wps office

Frequently Asked Questions

What is the main advantage of using the LET function in a spreadsheet?

The LET function allows you to assign temporary names to calculation results or values. This makes your formulas cleaner, easier to read, and significantly improves calculation speed because the spreadsheet only has to evaluate the named expression once instead of multiple times.

Can I use XLOOKUP inside a LET function for rate lookups?

Yes, you can define a variable in your LET function that stores the result of an XLOOKUP search. You can then use this variable multiple times in the final calculation step of the formula, which is perfect for fetching a base supplier commission rate.

How do I handle more than two product exceptions in my formula?

If you have multiple product exceptions, you can use the OR function within your IF statement to list them. Alternatively, for a longer list, you can use a COUNTIF or XMATCH function against a dedicated range of exception products to determine if the special 10% rate applies.

Excel & Data Problems