**Question description:**

*This user has given permission to use the problem statement for this blog.*

i have 4 different payment commission options for my sales team. I want to use drop down for them to select which type of cell it is. depending on what they select I want the formula to calculate their commission based on where they finished in the month. My main problem is i can't get my calculation in the vlookup to correspond with which row and cell.
Here is an example of the formula i am working with
=IF(revenueF25<40000,comm!D7*0.22,IF(AND(revenue!F25>=40000,revenue!F25<=70000),comm!D7*0.25,comm!D7*0.28)*10)

Solved by S. Y. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
08/04/2018 - 09:36

Welcome, thank you for choosing Got It Pro-Excel! I'll be glad to assist you with your concern.

User
08/04/2018 - 09:36

hey there

Excelchat Expert
08/04/2018 - 09:37

I understand you need help in vlookup function. Do you have a sample of the file so I can help you with your concern?

User
08/04/2018 - 09:37

not really but i can show you on the spreadsheet of what i have in mind

Excelchat Expert
08/04/2018 - 09:38

Sure, please show it on the document preview. Thanks

User
08/04/2018 - 09:41

so basically I want a drop down under type of sale and depending on what they choose either snb, s&L or com the results comes out under the commission section

Excelchat Expert
08/04/2018 - 09:41

ok I see

User
08/04/2018 - 09:42

I have the if forumula figured out because their commission goes up depending on where they are in the month but the % changed depending on what they sale

Excelchat Expert
08/04/2018 - 09:42

You have different calculations depending on type of cell correct

Excelchat Expert
08/04/2018 - 09:42

can you create a table on the formula for each type of cell? what are the differences between the 4 calculations?

User
08/04/2018 - 09:43

just the percents they get paid

Excelchat Expert
08/04/2018 - 09:43

also, what is the fourth sale

Excelchat Expert
08/04/2018 - 09:43

yep please let me know what % each sale has

Excelchat Expert
08/04/2018 - 09:43

You may add it in COlumn H

User
08/04/2018 - 09:44

so complicated

Excelchat Expert
08/04/2018 - 09:45

also, I can see that Revenue is also included on your sample formula, please also give the formula for revenue

Excelchat Expert
08/04/2018 - 09:45

I'm sorry but for me to help you fix your formula, I would need to know what your formula looks like

User
08/04/2018 - 09:45

if they are above 40k total for the month it is 22%, if they are btw 40k and 70k it is 25%, if they are above 70% it is 30%. If they select snb i want to add a 10% uplift

User
08/04/2018 - 09:47

but if they sale commercial f they are above 40k total for the month it is 25%, if they are btw 40k and 70k it is 28%, if they are above 70% it is 35%. If they select snb i want to add a 10% uplift

User
08/04/2018 - 09:49

that is more like what I am wanting. There are 4 types of sales. existing customer and snb (new customers)

Excelchat Expert
08/04/2018 - 09:49

sorry you mean less than 40k or greater than 40k? between 40k and 70k is also greater than 40k

User
08/04/2018 - 09:49

less than

Excelchat Expert
08/04/2018 - 09:49

ok thank you

User
08/04/2018 - 09:50

this is what i was thought would work

User
08/04/2018 - 09:50

=IF(SCAR!F26<40000,D7*0.22,IF(AND(SCAR!F26>=40000,SCAR!F26<=70000),D7*0.25,D7*0.28))

User
08/04/2018 - 09:51

scar represents the total revenue and D7 is margin.

Excelchat Expert
08/04/2018 - 09:52

10% uplift for commercial as well?

User
08/04/2018 - 09:53

just commercial snb

User
08/04/2018 - 09:53

there are 4 types of sales

Excelchat Expert
08/04/2018 - 09:54

Oh I see, so only 10% uplift if the Sale is commercal snb

User
08/04/2018 - 09:54

or s&l snb

Excelchat Expert
08/04/2018 - 09:54

all others same forumula

User
08/04/2018 - 09:54

same formula just different %

Excelchat Expert
08/04/2018 - 09:55

Ok, for commercial formula is 25% for <40k? but the first formula you give is 22%?

Excelchat Expert
08/04/2018 - 09:56

So everything else has the same base formula except for Commercial?

Excelchat Expert
08/04/2018 - 09:56

the base formula is before adding the %uplift

User
08/04/2018 - 09:56

s&l would be 22% 25% and 30%

User
08/04/2018 - 09:56

commercail would be 25% 28% and 35%

Excelchat Expert
08/04/2018 - 09:56

ok got it

User
08/04/2018 - 09:56

if SNB behind it 10% uplift

User
08/04/2018 - 09:59

i can get it to pull up the calculation depending on the sale type but i don't know how to get it to follow the calculation on the row i am on if that makes sense

Excelchat Expert
08/04/2018 - 10:00

Alright, please give a moment to solve your problem. Thank you!

Excelchat Expert
08/04/2018 - 10:14

Sorry I'm still working with the formula, thanks for your patience

User
08/04/2018 - 10:14

not a problem at all.

Excelchat Expert
08/04/2018 - 10:21

Hi will it be ok to just add new column for the uplift?

User
08/04/2018 - 10:21

sure

Excelchat Expert
08/04/2018 - 10:26

ok to resolve your problem we use this formula

Excelchat Expert
08/04/2018 - 10:26

=if(isnumber(search("s&L",A2)),if(D2<40000,B2*22%,if(and(D2>=40000,D2<=70000),B2*25%,B2*30%)),if(isnumber(search("Commercial",A2)),if(D2<40000,B2*25%,if(and(D2>40000,D2<=700000),B2*28%,B2*35%))))

Excelchat Expert
08/04/2018 - 10:26

that is the base formula to compute s&l vs commercial

Excelchat Expert
08/04/2018 - 10:26

then this is the formula to get the %Uplift:

Excelchat Expert
08/04/2018 - 10:26

=if(or(A2="commercial snb",A2="s&L snb"),10%,"")

Excelchat Expert
08/04/2018 - 10:27

Then I just added the two in total commission column c: =F2+(F2*G2)

User
08/04/2018 - 10:27

so i can change the type of sale and it change the calculations

Excelchat Expert
08/04/2018 - 10:27

yes

Excelchat Expert
08/04/2018 - 10:28

you can try now

User
08/04/2018 - 10:28

awesome

Excelchat Expert
08/04/2018 - 10:28

Do you have any questions regarding the solution provided?

User
08/04/2018 - 10:28

I couldn't figure out how to put multiple arguments

User
08/04/2018 - 10:29

Thanks a bunch

User
08/04/2018 - 10:29

will for sure use you again if I get stuck

Excelchat Expert
08/04/2018 - 10:29

Thanks for using Got It Pro-Excel. Please give your kind feedback for our service. Have a good day! :)

Excelchat Expert
08/04/2018 - 10:32

Feel free to end this session as soon as you are ready for us to continue helping other customers. Thank you.

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*