All solutions FormulaVLOOKUP Expert Solution – How to Use a VLOOKUP Formula

Excel - How to Use a VLOOKUP Formula - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc