Commission % drop down

Copper Contributor

HI all.


So, this is why my brain is hurting.  Please help.


I have a basic spreadsheet that I track my orders, and commission rates. Depending on who the customer is I receive either 2% or 6% commission.  Is there a way to have a cell specific drop down that has 2 different formulas for calculating my commission amount?  For example Cell I7 is the calculated total commissionable $ and Cell J7 is the calculation field for commission bases on who the customer is.  Can I have a drop down that says "2%, 6%" and depending on which I pick it will calculate the commission on Cell I7?


3+ hours and my brain hurts trying to figure this out.

5 Replies
either add another column for that drop down and then column K would have the calculated commission or use a lookup table and use XLOOKUP or VLOOKUP. So if you make J7 the drop down then K7 would be =I7*J7
If you have a lookup table it would be something like =I7*XLOOKUP(A7,Table1[name],Table1[commission],0)
is there a way to post a screen shot of my spreadsheet? Or to reply to you directly?
column A: Customer Name
Column B : Order date
Column C: Ship date
Column D Invoice Date
Column E: Invoice number
Column F: Order total
Column G: Freight charge
Column H: Freight Surcharge
Column I: Net order total (Column F -(G+H))
Column J: Commission amount (column I * 2% or 6%)

Or, can I create a list of customers that are at 2% and a list that are at 6%? Then when I enter the name is column A, Column J will auto populate with the correct %


Yes, create a list.

For example, unique customers in P2:P50, and the corresponding commission percentage in Q2:Q50.

The formula in J2 would be

=XLOOKUP(A2, $P$2:$P$50, I2*$Q$2:$Q$50, "")

or if you have an older version of Excel

=IFERROR(I2*VLOOKUP(A2, $P$2:$Q$50, 2, FALSE), "")

This can be filled down.

If the list of customers is 'smaller' and this list is 'bigger' then a separate list of customers and their % makes sense. If not, then just add the % in column J and then make K the calculated amount. I don't recommend you make 2 lists (1 list of customers with 2% and 1 list of customers with 6%) . It sounds 'convenient' and easy but the Lookup is much easier with 1 list as noted above and when you end up adding a customer with 4% or 8% or 1% then you have more lists. You can easily 'look up' the 2% or 6% by filtering that 1 list or search for a name in that 1 list to find which % they are.