Save Fail - Formula longer than 8192 characters

vfernandes7 

You can use a combination of IF, VLOOKUP, and CONCATENATE functions to create a single formula that checks several conditions to calculate commission.

Here are the steps:

1. Create a new workbook in Excel and name it “Commission Calculator”.
2. Type Sales Person in cell A1, Customer in cell B1, Product in cell C1, Price in cell D1, and Commission Rate in cell E1.
3. Enter the sales person name for the first transaction in cell A2. In cell B2, enter the customer name. In cell C2, enter the product name. In cell D2, enter the price of the product. In cell E2, enter the commission rate.
4. In cell F2, enter the following formula: 
5. =IF(AND(A2="Sales Person 1",B2="Customer 1",C2="Product 1",D2=100),VLOOKUP(D2,{0,0;100,0.05;200,0.1;300,0.15},2),IF(AND(A2="Sales Person 2",B2="Customer 2",C2="Product 2",D2=200),VLOOKUP(D2,{0,0;100,500;200,1500;300,3000},2),""))&IF(AND(A3="Sales Person 3",B3="Customer 3",C3="Product 3",D3=300),VLOOKUP(D3,{0,0;100,0.05;200,0.1;300,0.15},2),IF(AND(A3="Sales Person 4",B3="Customer 4",C3="Product 4",D3=400),VLOOKUP(D3,{0,0;100,500;200,1500;300,3000},2),""))

This formula checks if Sales Person is “Sales Person 1” and Customer is “Customer 1” and Product is “Product 1” and Price is 100 then it uses the first VLOOKUP function to calculate commission otherwise it checks if Sales Person is “Sales Person 2” and Customer is “Customer 2” and Product is “Product 2” and Price is 200 then it uses the second VLOOKUP function otherwise it returns an empty string.

I hope this helps!