Using VLOOKUP for a Conditional Discount Formula
HTML-код
- Опубликовано: 22 авг 2024
- Using VLOOKUP for a conditional discount formula. When testing multiple conditions many people use nested IF functions when a VLOOKUP is a much faster and tidier method.
This video tutorial looks at using the VLOOKUP function to return a discount from a table depending upon the quantity ordered. To do this the approximate match option of VLOOKUP will be used.
Find more great free tutorials at;
www.computerga...
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/Ultimat...
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1
7 years later and this video is still helping people like me, very well done!
Excellent!! Thanks 👍
This is great! Really clear video showing an elegant solution to what I was trying to do - thank you very much!
Glad it helped! Thank you 😊
thank you so mucchhhh i've been crazy for formula 'if' but my teacher say there is another way.. and here i am :)
You're welcome 😊
Thanks for this video please how do I make the column B to be in sterling pound instead of percentage
Use the following formula to return the percentage of the total, rather than just the percentage
=VLOOKUP(A4,discounts,2,true)*C4
You may still need to apply the pounds sterling format to the cells.
Thank you so much for your perfect explenation.
You're very welcome!
please how do I make the column B to be in sterling pound instead of percentage
thank you good sir
You're welcome, Adam.
Hello kind sir, i was wondering if there is a way to get how much total discount will you get according to the discount table directly.
Sure. You could use =C4*VLOOKUP(A4,discounts,2,True)
If you wanted to subtract the discount value in one formula, you can use C4*(1-VLOOKUP(A4,discounts,2,True))
@@Computergaga Thats worth a Subscription!👍🏻
Hi, if I have triple diacount on individual product , like 10% +10% +10%. Whats e formula for it?
This can be added into the lookup table to be returned. That is probably easiest. Otherwise , an IF can be added for additional conditions.
But how do you make it calculate the discount and return it there:( I am stressed.
=C4*(1-VLOOKUP(A4,discounts,2,True))
How do I do this if instead of quantity I have discount type A,B,C,D,E thats designated to different percent?
You would set up the lookup table in the same way, but enter the A, B, C, D and E instead of the quantities. Then in the VLOOKUP, everything would be the same except to use False on the last argument instead of True. This ensure an exact match on the letters.
how do I create a percentage commission drop based on a gross margin percentage
Sounds like the gross margin percentage would be the first column to look for, then the percentage commission returned from the second column like in the video.
@@Computergaga the percentage subtraction would be based on the variance percentage