How to use list instead of multiple if statements on sales commission spreadsheet

bythecshore

Board Regular
I'm trying to design a formula for a commission spreadsheet and can't figure this out.

The way it works is that if the salesperson sells up to \$60k he gets 15%. If he sells \$60k to \$65k, he gets 17%. If he sells \$65k to \$70k he gets 18%. And so on; there are 12 commission levels.

What I need to do is enter the sale amount in a cell and have it calculate the commission amount in a cell next to it. It should look up the commission percentage from a list that's in the spreadsheet, that lists the sales brackets and corresponding percentages.

I guess I could do this with multiple IF statements but that would be a nightmare (at least for me). There must be a simpler way to do this, right?

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

mse330

Well-known Member
Try the below formula ... You just need to set up your commission table

Last edited:

jtakw

Well-known Member
Hi,

OP's description sounds more like a "Tiered" commission schedule:

Add the rest of your Commission levels to Columns D and E, Column F is the "Delta", difference between current commission level compared to previous commission level.
Adjust range in formula to include complete table.

bythecshore

Board Regular
Thanks for the fast responses. The LOOKUP worked perfectly. (I didn't try the SUMPRODUCT because the other one worked right away, but thanks to you too.)

mse330

Well-known Member
Glad to help & thanks for reporting back

Replies
3
Views
488
Replies
1
Views
110
Replies
1
Views
39
Replies
3
Views
304
Replies
2
Views
220