# Help on the IF Function

#### JimWM2000

##### New Member
I'm trying to formulate a speadsheet that will configure commissions based on a commission code. The commission code represents the % commission distributed to each sales person.

For Instance: Code #1 means that Jeff will receive 8% commission, Thom will receive 0% and Mike will receive 0%.

I've 18 different codes for commissions. Any suggestions are greatly appreciated.

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### wilkisa

##### Well-known Member
Perhaps a VLookup would be better suited for this

#### Yogi Anand

##### MrExcel MVP
Hi Veronica:

Welcome to the Board!

I believe use of the VLOOKUP function will work for you -- this will require you to set up a table of commission values corresponding to either names of SalesPersons/SalesVolume.

If you care to post some sample data, we can talk about it even some more.

Regards!

Yogi

#### bmacias

##### Board Regular
Veronica.

Also, look into the "choose" function. We use it to do pretty much what you are describing. except we only have 4 codes instead of 18.

Lets say:
a1 = 1000 (The salary or sale amount)
b1 = 1 (the code you want, to us 1 equals 2%)
c1 = =choose(b1,.02,.03,.04,05)*a1

Hope this helps.

#### JimWM2000

##### New Member

The Choose function seems to work for one value but how do I get it to work when a code represents 3 seperate commissions.

Ex: Code #4

Tom 5% Jeff 6% Mike 2%

#### JimWM2000

##### New Member
On 2002-10-23 15:27, Yogi Anand wrote:
Hi Veronica:

Welcome to the Board!

I believe use of the VLOOKUP function will work for you -- this will require you to set up a table of commission values corresponding to either names of SalesPersons/SalesVolume.

If you care to post some sample data, we can talk about it even some more.

Regards!

Yogi

Thanks for your input...I'm not very savy at this sort of thing yet...However, here is some sample data for you to view...

Code
#1 Jeff 8%
#2 Thom 10% Jeff 1%
#3 Thom 5% Jeff 6%
#4 Thom 5% Jeff 6% Mike 2%

Contract Amount Code Thom Jeff Mike

I'm trying to save time by not entering a formula for each salesman everytime...I want excel to recognize the code. For ex. upon entering #1 Thom would receive a 0 Jeff would receive 8% of the contact amount and Mike would receive a 0.

Hope this helps!

#### IML

##### MrExcel MVP
Set up a table with your codes going down and Names accross the type and populate the correct percents. It mike look like this
blank Jim bob Joe
Code1 1 2 3
Code2 4 5 6

You can name this COM_TAB via the name box.

The formula
=INDEX(COM_TAB,MATCH(A2,INDEX(COM_TAB,0,1),0),MATCH(A1,INDEX(COM_TAB,1,0),0))

will return the right percent based on the persons name in A1, and the code in A2. This entries must exactly match how they appear on the table (maybe employee numbers would be a better key?)

Any time commission rates change you only need update the table. Give the amount of variables you have here, an if statement doesn't appear to be feasible.

#### Yogi Anand

##### MrExcel MVP
On 2002-10-24 13:37, JimWM2000 wrote:
On 2002-10-23 15:27, Yogi Anand wrote:
Hi Veronica:

Welcome to the Board!

I believe use of the VLOOKUP function will work for you -- this will require you to set up a table of commission values corresponding to either names of SalesPersons/SalesVolume.

If you care to post some sample data, we can talk about it even some more.

Regards!

Yogi

Thanks for your input...I'm not very savy at this sort of thing yet...However, here is some sample data for you to view...

Code
#1 Jeff 8%
#2 Thom 10% Jeff 1%
#3 Thom 5% Jeff 6%
#4 Thom 5% Jeff 6% Mike 2%

Contract Amount Code Thom Jeff Mike

I'm trying to save time by not entering a formula for each salesman everytime...I want excel to recognize the code. For ex. upon entering #1 Thom would receive a 0 Jeff would receive 8% of the contact amount and Mike would receive a 0.

Hope this helps!

Hi Veronica:

I am going to suggest two methods for your ease of input --
1. the first method involveds using named ranges and the INTERSECTION operator
2. the second method involves use of Custom Lists

These methods may or may not work for you -- bust these are viable options for just the type of thing you are trying to do.

In any event, here is the worksheet simulation showing the use of each of the two methods.

By the way, in my simulation, I abbreviated Jeff to J, Mike to M, and Thom to T
y021026h1.xls
ABCDEFGH
1JMT
2#18%0%0%
3#21%0%10%
4#36%2%5%
5
6UsingIntersectionOperator8%2%2%0%
7Insert|Name|Create thencheckTopRowandLeftColumn
8
10
11UsingCustomListJMT
12#10.800
13createaCustomListviaTools|Options#20.0100.1
14key-in#1incellE10thendragtoH10#3.060.020.05
Sheet7
</SPAN>

Regards!

Yogi Anand

Replies
3
Views
188
Replies
0
Views
344
Replies
2
Views
232
Replies
2
Views
227
Replies
0
Views
91

1,147,451
Messages
5,741,202
Members
423,648
Latest member
steel1968

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back