[go: up one dir, main page]
Scribd
Upload
54 views6 pages

Advanced MultTable VlookUp

The document outlines the sales commission structure for 23 sales representatives. Commission is calculated based on years of tenure, sales amount, and commission rates that range from 0-21% depending on the sales representative's tenure and amount sold. Representatives are placed into three tenure categories: less than 3 years, between 3-5 years, and more than 5 years.

Uploaded by

DrNarayan KR
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
54 views6 pages

Advanced MultTable VlookUp

The document outlines the sales commission structure for 23 sales representatives. Commission is calculated based on years of tenure, sales amount, and commission rates that range from 0-21% depending on the sales representative's tenure and amount sold. Representatives are placed into three tenure categories: less than 3 years, between 3-5 years, and more than 5 years.

Uploaded by

DrNarayan KR
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
You are on page 1/ 6

Sales Rep

Michael
Mitchelle
Ricky
Roger
Rafel
Sachin
Rahul
Saurav
Anil
Abhinav
Vijendra
Suresh
Mahendra
Zaheer
Praveen
Munaf
Badrinath
Virat
Virendra
Amar
Mukesh
Riyaz

Years
2
1
1
2
6
3
2
3
1
4
4
5
5
6
10
9
12
10
8
4
6
2

Sales
20000
10000
15000
40000
35000
60000
45000
50000
30000
40000
75000
50000
70000
60000
30000
40000
50000
45000
60000
75000
90000
70000

Commission

<3 Years Tenure


Amount Sold
0
1000
5000
10000
15000
20000
25000
30000
35000
40000
45000
50000
55000
60000
65000
70000
75000
80000
85000
90000
95000
100000

>=3 Year and <=5 Years

Commission Rate

Amount Sold
0%
0.25%
0.50%
0.75%
1.00%
1.25%
1.50%
1.75%
2.00%
2.25%
2.50%
2.75%
3.00%
3.25%
3.50%
3.75%
4.00%
4.25%
4.50%
4.75%
5.00%
5.25%

0
1000
5000
10000
15000
20000
25000
30000
35000
40000
45000
50000
55000
60000
65000
70000
75000
80000
85000
90000
95000
100000

Commission Rate
0%
0.50%
1.00%
1.50%
2.00%
2.50%
3.00%
3.50%
4.00%
4.50%
5.00%
5.50%
6.00%
6.50%
7.00%
7.50%
8.00%
8.50%
9.00%
9.50%
10.00%
10.50%

>5 Years
Amount Sold
0
1000
5000
10000
15000
20000
25000
30000
35000
40000
45000
50000
55000
60000
65000
70000
75000
80000
85000
90000
95000
100000

Commission Rate
0%
1.00%
2.00%
3.00%
4.00%
5.00%
6.00%
7.00%
8.00%
9.00%
10.00%
11.00%
12.00%
13.00%
14.00%
15.00%
16.00%
17.00%
18.00%
19.00%
20.00%
21.00%

Sales Rep Years Sales Commission


Michael
2 20000
Mitchelle
1 10000
Ricky
1 15000
Roger
2 40000
Rafel
6 35000
Sachin
3 60000
Rahul
2 45000
Saurav
3 50000
Anil
1 30000
Abhinav
4 40000
Vijendra
4 75000
Suresh
5 50000
Mahendra
5 70000
Zaheer
6 60000
Praveen
10 30000
Munaf
9 40000
Badrinath
12 50000
Virat
10 45000
Virendra
8 60000
Amar
4 75000
Mukesh
6 90000
Riyaz
2 70000

<3 Years Tenure


Amount Sold
0
1000
5000
10000
15000
20000
25000
30000
35000
40000
45000
50000
55000
60000
65000
70000
75000
80000
85000
90000
95000
100000

<3 Years Tenure


Commission Rate
0%
0.25%
0.50%
0.75%
1.00%
1.25%
1.50%
1.75%
2.00%
2.25%
2.50%
2.75%
3.00%
3.25%
3.50%
3.75%
4.00%
4.25%
4.50%
4.75%
5.00%
5.25%

>=3 Year and <=5 Years


Amount Sold
Prd Code Commission
0 A11
1000 A12
5000 A13
10000 A14
15000 A15
20000 A16
25000 A17
30000 A18
35000 A19
40000 A20
45000 A21
50000 A22
55000 A23
60000 A24
65000 A25
70000 A26
75000 A27
80000 A28
85000 A29
90000 A30
95000 A31
100000 A32

Rate
0%
0.50%
1.00%
1.50%
2.00%
2.50%
3.00%
3.50%
4.00%
4.50%
5.00%
5.50%
6.00%
6.50%
7.00%
7.50%
8.00%
8.50%
9.00%
9.50%
10.00%
10.50%

Amount Sold

Prd Code
0 A11
1000 A12
5000 A13
10000 A14
15000 A15
20000 A16
25000 A17
30000 A18
35000 A19
40000 A20
45000 A21
50000 A22
55000 A23
60000 A24
65000 A25
70000 A26
75000 A27
80000 A28
85000 A29
90000 A30
95000 A31
100000 A32

>5 Years
Area Code
AC1
AC2
AC3
AC4
AC5
AC6
AC7
AC8
AC9
AC10
AC11
AC12
AC13
AC14
AC15
AC16
AC17
AC18
AC19
AC20
AC21
AC22

Commission Rate
0%
1.00%
2.00%
3.00%
4.00%
5.00%
6.00%
7.00%
8.00%
9.00%
10.00%
11.00%
12.00%
13.00%
14.00%
15.00%
16.00%
17.00%
18.00%
19.00%
20.00%
21.00%

You might also like