XLOOKUP Array Formula to Lookup Different Product Commission Rates for each Sales Rep. EMT 1804

Step-by-Step Tutorial: Using XLOOKUP Array Formula to Lookup Different Product Commission Rates for Each Sales Rep

1. Identify the Data Setup:

• In your Excel worksheet, ensure you have a table that includes sales representatives, products, commission rates, and sales data.

2. Add a Helper Column Using XLOOKUP:

• Select a cell where you want to display the commission amount for a specific sales rep.
• Use the XLOOKUP function to create a helper column directly in the cell.
• Highlight the sales rep column and concatenate it with the product column using the "&" symbol.
• Use the XLOOKUP function to look up the correct commission rate based on the concatenated values.
• Multiply the commission rate with the sales amount to get the commission amount for each row.

3. Filter Data for Specific Sales Rep:

• In a new column, use a formula to check if the sales rep is equal to the desired sales rep (e.g., Joe).
• This formula will return TRUE for rows corresponding to the selected sales rep and FALSE for others.

4. Sum the Commission Amounts:

• Use the SUM function to sum the commission amounts for the specific sales rep.
• Enter the formula, referencing the column with TRUE/FALSE values.
• Press "Ctrl + Enter" to apply the formula to the current cell and keep it selected.
• Double-click the fill handle at the bottom right corner of the cell to copy the formula down to all relevant rows.

5. Alternative Method using INDEX and MATCH:

• If you prefer an alternative method, you can use INDEX and MATCH functions instead of XLOOKUP to achieve the same result.
• Concatenate the sales rep and product columns to create a lookup value.
• Use the MATCH function to find the position of the lookup value in the lookup array.
• Use the INDEX function to retrieve the commission rate based on the matched position.
• Multiply the commission rate with the sales amount to calculate the commission amount.

6. Verify Results:

• Check that the commission amounts calculated using either XLOOKUP or INDEX/MATCH methods align with the expected values for each sales rep.

By following these steps, you can effectively use the XLOOKUP array formula or the INDEX and MATCH functions to lookup different product commission rates for each sales rep in Excel.