# 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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### 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
9key-in='#1'JincellE6andread8%
10
11UsingCustomListJMT
12#10.800
13createaCustomListviaTools|Options#20.0100.1
14key-in#1incellE10thendragtoH10#3.060.020.05
Sheet7
</SPAN>

Regards!

Yogi Anand

Replies
1
Views
266
Replies
0
Views
195
Replies
3
Views
369
Replies
3
Views
1K
Replies
5
Views
178

Threads
1,181,091
Messages
5,928,032
Members
436,583
Latest member
AlxBtx

### 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

### Disable AdBlock

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