Excel Magic Trick 1479: Lookup Bulk Quantity Discount Prices Using VLOOKUP Function

Download Files: Start Excel File: excelisfun.net...
Entire page with all Excel Files for All Videos: people.highline...
Learn about how to
In this video learn the Basics of VLOOKUP to looks up a price based on a Bulk Quantity Discount. Also learn about the MATCH Function to automatically pick out the correct Column Number for the VLOOKUP Function.
1. (00:01) Introduction
2. (00:14) Use VLOOKUP and Column Index Number to lookup a price based on a Quantity Discount
3. (05:14) Use VLOOKUP and MATCH to lookup a price based on a Quantity Discount
4. (09:44) Summary

[@momi3652]

i can't believe you are teaching all this knowledge and giving study material for free, god bless you MIKE.

[@excelisfun]

Yes, it is true : ) I have been providing free Excel Education for the world since 2008 : )

[@5pctLowBattery]

Thanks Mike! This is the kind of videos I joined RUclips to watch. This past year I’ve gone astray watching videos about WH drama.

[@excelisfun]

Don't go astray! Welcome back to watching Excel videos and having fun : )

[@LeilaGharani]

Thank you for the VLOOKUP & MATCH duo :)

[@ismailismaili0071]

the match function is really great for Vlookup especially if we have more then a column to lookup. thank you Mr. Mike for your reminder

[@excelisfun]

You are welcome for the reminder!

[@margaretwarren1814]

I have learned so much from you Mike i can now teach my Grand Children the basic of Excel

[@excelisfun]

That is so wonderful!!!!! What an awesome grandparent you are!!!! Thanks for your support : )

[@chrism9037]

Great use of MATCH within VLOOKUP,! Excellent Mike!

[@excelisfun]

Glad you like it, Chris! Thanks for your support : )

[@GeorgeAJululian]

Thanks very helpful and useful

[@sachinrv1]

Fantastic; as usual. I would have liked if you had used the sentence "the result of match function is always a whole number and in the syntax of vlookup, the column index number we have to provide is also a whole number; hence they compliment each other". But never the less VERY VERY USEFUL VIDEO FOR WORKING PROFESSIONALS.

[@joes.8351]

Very good explanation, I will definitely use this function once I'm all set.
Previous manager simply applied a 10% discount at each price break. It just seems off, but I'm so rusty that I'm unsure why they did that. I think I just need to revisit job cost accounting.
Thanks again for videos. I always learn a lot from them.

[@excelisfun]

Awesome! Glad it helped - Two more to come with variations on this one.

[@2minexcel4u]

Thanks for all your videos! they are always a great help!

[@MalinaC]

I love approximate match in VLOOKUP! Thanks for this video :)

[@excelisfun]

You are welcome, Teammate!

[@MalinaC]

I see a new logo! Nice! :)

[@excelisfun]

I should have changed it a long time ago, but just been too busy...

[@gsolimama]

THANK YOU.

[@margaretwarren1814]

You are a great Man

[@excelisfun]

Thank you!

[@DougHExcel]

excellent video...thanks for putting it up!

[@excelisfun]

You are welcome, Teammate : )

[@alialbayati9368]

thanks Mike.

[@excelisfun]

You are welcome, Ali!!!!

[@mohamedchakroun4973]

Dont mind your video are always handy so automatically we put thumbs up thanks for review :-)

[@excelisfun]

Thank you very much for the auto Thumbs Up. I especially appreciate it since most humans are not like that at all : (

[@mohamedchakroun4973]

Dont mind you are making a big work there....for me every video i watched i learn something new or i consolidate my knowledge because if we dont refresh our memory we forget many things and i recommand your videos intensively you are a great man hat off :-)

[@excelisfun]

I am glad you can learn something in each video! Thanks for recommending the vids to others : )

[@Luciano_mp]

Obrigado Mike.

[@excelisfun]

You are welcome! Thanks for the support with your Thumbs Ups, Comments and Sub : )

[@AsifAli-ge5lf]

You are magician my frnd.......
Amazing video... 👍👍👍

[@excelisfun]

Glad you like it! Magic with Excel is fun : )

[@pmsocho]

Great explanation!

[@excelisfun]

Thanks Teammate : )

[@johnborg6005]

Thanks Mike :)

[@excelisfun]

You are welcome, John! Thank you for your consistent support : )

[@BillSzysz1]

Great :-)) Thanks.

[@excelisfun]

You are welcome, O Masterful PQ Artist : )

[@SyedMuzammilMahasanShahi]

Thanks Sir Mike for this amazing video :p

[@excelisfun]

You are welcome, Syed Muzammil Mahasan Shahi!!!

[@Christyjess]

Thank you

[@excelisfun]

You are welcome! Thanks for the support : )

[@ashokvarma6292]

Excellent

[@excelisfun]

Glad you thought it was EXCELlent!!! Thanks for your support : )

[@shambapednekar9337]

Excellant

[@excelisfun]

Glad you like it! Thanks for your support : )

[@bonarhya25146]

Hi, really enjoying your videos, they are so clear and easy to understand. I wanted to go to the download page for the excel files for the entire video series but the link does not work unfortunately. Downloaded this file ok though so was able to follow along with this one.

[@excelisfun]

I fix it for you! Thanks for watching and supporting with your comment, Thumbs Up and Sub : )

[@bonarhya25146]

Excellent, Thank you so much :-)

[@julianwilliams99]

thanks

[@excelisfun]

You are welcome! Thanks for the support : )

[@nafizhaq4554]

Guy you should be doing a video on complex financial modelling 😀

[@excelisfun]

I have hundreds of videos on financial models and over 3000s videos in all.
Here is a finance videos class (10 years old but all the cash flow models are spot one): ruclips.net/p/PL90E1F26C7B85E78F
Here is an Analytics Modeling Class: ruclips.net/p/PLrRPvpgDmw0mSJCZaqQPFj0eto4qnzkCZ
When you watchm please support with Thumbs Up and comment on each video that you watch.

[@andreasebastian4726]

Mike, I have a question. I have something that says. Use the VLOOKUP function to lookup the Quantity Discount Rate based on Quantity Ordered.... how should that look...???

[@midwesttumblersllc3822]

What if I am trying to have it calculate a discount based on number of items ordered? So I have a total cost in J9 and i want it to figure up a discount amount based on the % I put in F 22-27?

[@ramie9855]

Thanks!

[@excelisfun]

Glad it helped! Thanks for your support : )