Excel Basics 14: Excel VLOOKUP Function Made Easy! 9 Examples to Make Your Job Easier!
ฝัง
- เผยแพร่เมื่อ 7 พ.ย. 2017
- Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: people.highline.edu/mgirvin/Y...
Download Full Written Notes: people.highline.edu/mgirvin/Y...
Class web page link: people.highline.edu/mgirvin/A...
Topics in this video:
1. (00:01) Introduction
2. (01:10) Should we use IF or VLOOKUP?
3. (02:12) What does VLOOKUP do?
4. (03:22) Examples of Lookup Tables
5. (06:14) What does “V” in VLOOKUP mean?
6. (06:41) Formula 1: Exact Match VLOOKUP to get Product Price & Data Validation Dropdown List: =VLOOKUP(E23,A23:C27,3,0)
7. (09:50) VLOOKUP can do 2 different tyoes of Lookup
8. (11:29) Formula 2: Exact Match VLOOKUP to get Product Price for an Invoice: =VLOOKUP(A41,$A$23:$C$27,3,0)
9. (13:04) Add Data Validation List to a cell to create a drop-down list of valid lookup values for VLOOKUP Function
10. (18:03) Formula 3: Approximate Match VLOOKUP to get a Bonus Commission %: =VLOOKUP(B21,$F$21:$G$27,2)
11. (24:11) Formula 4: Approximate Match VLOOKUP to get a Bonus Commission % & Calculate Bonus Amount: =VLOOKUP(B33,$F$21:$G$27,2)*B33
12. (28:15) What VLOOKUP Does: Puts 1 of multiple things into a cell or formula.
13. (29:01) Formula 5: Exact Match VLOOKUP to get Employee E-mail and Phone Number: =VLOOKUP(A16,A2:I11,6,0)
14. (31:56) Formula 6: Exact Match VLOOKUP to List of Employee Salaries: =VLOOKUP(A23,$A$2:$I$11,9,0)
15. (32:48) Formula 7: Approximate Match VLOOKUP to get Tax Amount: =VLOOKUP(D4,A4:B8,2)
16. (34:23) Formula 8: Approximate Match VLOOKUP to get Commission Amount for Payroll Table: =VLOOKUP(E13,$A$13:$B$17,2)
17. (36:31) Formula 9: Approximate Match VLOOKUP to get Commission Amount for Payroll Table: =VLOOKUP(E4,$H$4:$I$9,2)
18. (38:08) Summary - Conclusion
![[MIRRORED] LISA - ROCKSTAR 🎸 MV version #LISA #BLACKPINK #ROCKSTAR](http://i.ytimg.com/vi/-U58DuCCtZk/mqdefault.jpg)
Civilizations advance because of people like ExcelIsFun. Thank you very much from the bottom of my heart.
You are welcome, Trader!!! Thanks for your kind words : )
Sir,
Your every video is more than excellent. I am so confident that there is no other advanced tutorial and trainer like you. I am not interested to follow any other trainer's tutorial if you have tutorial in which I am interested to learn. If it is possible, I will give a lot of Thumbs Up in every of your videos. We need trainer like you to make the world more better. I usually suggest to download and follow your videos whoever want to learn excel meeting with me. Thanks a lot to keep love and patience in your teaching field.
Dear Md. Monirul Islam, Thank you very much for your kind words and support in telling others about the free Excel Education that I provide to the world : )
Sincerely, Mike excelisfun Girvin
This is hands-down the best excel tutorial I have ever seen. Congratulations!
Glad it was helpful!
Good day, Professor Girvin. I was your student in the college. Now, I need to remember Vlookup function for my work. I am very grateful that I have this opportunity.
Yes!!! This is why I post all my Highline Class videos at TH-cam for free : ) So you can access them anytime. Glad they are here for you, Libi!!!
You promised FUN and you delivered! Thank you Mr. Girvin!😊
You are welcome for the fun!!!
I'm from Bangladesh. You are my 1st and best Excel teacher... Immense respect for you......wish you and your family good health.
Thanks for the well wishes! Glad the videos help : )
You give such a hard-working content for free. Ohh, I can't believe it. I bet, you are not only the best teacher but also a highly generous person. Really, you have a GIGANTIC Heart.
Yes, sir! It is my duty as a human on the planet earth to try and make the world a better place. This is one way I try to do that. I am happy the content helps : )
I lately found you. Just basic level 14th video i completed. And yesterday you saved my life. Thank you very much.
You are welcome for the "saved my life" fun, Chamchijjie!!!
This is a very expensive knowledge to get in college right here for free. YOU ARE THE REAL DEAL, PLEASE MAKE MORE. Your channel is worth subscribing.
Glad you like it! It is expensive because each time you watch you have to click Thumbs Up and leave a small comment - but I guess it is better than paying money : )
Hello @mike sir you videos are far more better than some of the institute of my region, Thankyou very very much for your support and my hearty gratification to you....
You are welcome very much!!!!
Thank you from the depth of my heart, really one can not find any video better then these videos present on this channel❤❤❤
I am glad to help : )
Mike no one can teach/share knowledge better than you,not even close.With each video m gaining confidence to use Excel like a pro.Thanks
Thank you for your kind words, vivek!! Thanks for your support on each video with a comment and thumbs up: it helps to promote the videos and get this free Excel Education to more people in the world!!!
This video just saved me 8 hours of work.
Yes!!! Saving lots of hours of work is why I have been posting for the last 15 years here at TH-cam lol
You are amazing! I've learned a lot from your videos and about to attend an Excel test for a possible employment opportunity. Thanks a ton!
Thanks for the comprehensive tutorial!
I wish I were one of your students when I learnt Excel 15 years ago!
You are welcome! I wish i had a good teacher also back in the early 90s...
Thank you so much! I always remember to click F2 to verify now. It's like whenever I answer the Homework, I can hear your voice. You are the best teacher! Even like me, a non-native English speaker, I am able to understand your teaching very well!
Thank you sir for another charming video of VLOOKUP!! what an excellent up-gradation to the IF function.
Once again thank you for all your efforts.
Yes, a very good upgrade : )
You the best,just a few days of watching all your Excel Basics and i feel so accomplished already...You the BEST ,Thanks
That is why I post so you and others can have fun and become proficient with Excel!! Thank you for your support with your comment, Thumbs Up and Sub : )
You are a BORN teacher!!! The way break things down into such simple to understand concepts is FANTASTIC!!!!
Yes! I am glad that this all works for you : ) Thank you for your continued support!!!!!
I wanted to say thank you for all of your videos because it have helped me and all my friends get through many of our college classes. Also, because of your amazing informational videos on here it has help me at my current job as an accountant. Each time when I need help I know I can refer to your TH-cam channel and I can always count on a video you published to help me out. Thank You and keep up the good work.
You are welcome! I am glad that the videos help you in school and in your job. Be sure to help promote the excelisfun channel by always clicking Thumbs up, leaving a comment when you are inspired and telling all your friends to subscribe and learn for free : )
I really loved the way you teach. I wish I could have had you as my teacher for all the courses that I learned till and going to learn in future.
I will be here at TH-cam for you!!!!
I love your end sentence so much " Because there's always lots more videos to come from excelIsFun"
Glad you love it, Mostafa!!!!
Hello Mike! Amazing content! You are a great trainer. Thank you so much!
Thank you very much for these classes. Very easy to understand and practise through your homework tasks. Appreciate your kind service to the society. We will forever be indebted to your gesture.
You are welcome! It has been my goal for almost 10 years at TH-cam to bring Excel to the world. No need to be indebted, just pay back with a Thumbs Up and small comment on each video that you watch - it helps a lot : )
This is the most comprehensive and engaging Excel tutorial that I have come across. Thank you very much for your commitment to teaching and helping others to master material that can oftentimes be difficult to maintain an interest in. Much appreciated
You are welcome very much, michvmmify! I can provide this free education because of support from viewers like you with your comments, Thumbs Ups and Subs : )
Great video, as always! I think I finally have it locked into my brain the difference between approximate match and exact match in VLOOKUP. (For some reason, I sometimes forget what those mean -- but now it's clear).
Awesome!!! Knowing that is crucial to being great with Excel. : )
Awesome overall. Homework was an excellent challenge as well and helped push my understanding to a new level. Enjoyed the mixing of IF and VLOOKUP. Hard work pays off! Thanks!
Glad the video, notes, Excel files, homework and hard work pay off!!! Thanks for your support with your comment, Thumbs Up : )
This video has helped tremendously this week. I’m your #1 fan. I have everybody watching your videos.
Glad it helps! Thanks for telling people about this free Excel Channel! Thanks also for the support with your comment, Thumbs Up and Sub : )
Thank you sooo much. I've been trying to find how to do this and no other video I've seen details it like you do. You are my go-to guy now :)
Glad to help. I have been providing detailed Excel for 13 years here at TH-cam. Nice to connect with you!
I felt so clever when I created a massive string of IFS as a formula for my gradebook. I've seen other videos on VLOOKUP, but this is the first time it has ever made sense to me. Thank you! Also, I'm thinking back on how much time I've lost because I didn't go down to the last cell and hit F2 to check the formula. You really hammer that point home in these videos.
Glad to help, Tim : )
As usual, mind blown. Thanks.
Yes, Mind Blown is a positive direction is always good!!! Thanks for your support : )
Whoa,,, thanks so much Mr. Girvin for your such passionate teaching on the ExcelIsFun VIDS,,,,
GOD bless for all your good works,,,,
You are welcome, TRUSTin WWJD!!!! I am happy to post these for you : )
As i know that Vlookup is one of the most powerful functions thank u Mr. Mike for showing up with explaining how it really works
You are welcome!
I just wanted to say thank you for this. You actually DID make this fun to learn. I've been a subscriber for a while but only come back when I'm stuck with something... Now, I'm interviewing with a company that requires me to know what a H&V lookup was. I've never used it before. THANK YOU! Your video may have landed me this job! PS I even did the homework. :)
I am happy to help. This is what I have been doing for 13 years: fun with excel to get jobs, keep jobs and get promoted quickly. Post back and let me know about the job : )
@@excelisfun I will Thank you. 2nd interview is tomorrow afternoon.
@@SFD-Horses goo luck with the 2nd interview!!!
@@excelisfun THANK YOU! I accepted their offer on 12/3! And they have Office 365 which means I can use your SEQUENCE tutorial. I'm experimenting so I am proficient on my first day.
@@SFD-Horses You are going to do awesome!!!!
Hey
Thank you so much
I learned a lot from your videos.
You are simply awesome👏👍
Glad the videos help, Surbhi!!! Thanks for the support with your comment, Thumbs Up and Sub : )
I love how you explain vlookup
It's very easy for beginner like me to understand
Thanks
Very much I do like
I used the if function for categorizing my students marks, something which is much vulnerable to human error
I’m from now going to enjoy the vLookup
another great video for understand how the function works, thanks for great lesson!
You are welcome!!!!
Great job again. In the last HW for this sheet I actually used different combination of the functions, than in your resolved version, but yours was a bit shorter. But I got the same values, so here's for practicing and developing intuition.
I love it! Creating your own solution in a new way is TRUE fun : )
Thank you for these video tutorials! they are amazing! You rock :)
You are welcome, cindy!!!!
You are a great teacher.... God bless u🙏🙏
Thank you so much for this fantastic lesson! Can't wait to see what Excel magic tricks you'll teach me in the next video.
Must have VLOOKUP in your bag of tricks. With many more to come, Nyuklehead!!!
I'd consider myself an excel pro... But these videos are absolutely amazing for reviews and potentially picking up new tidbits.
VLOOKUP is an awesome function))) it’s so useful!!!! Thank you for lesson!!!!!
You are welcome, _AnBu_!!!! Thanks for your support : )
Very well explained. Thank you!
You are welcome!
BIg thank you to the best excel teacher ever!
Mind blowing stuff! This is fantastic.
Glad you like it!!
Hey Mike, In Sheet HW(1an) the solution in cell B8 should be =VLOOKUP(A8,$A$11:$E$17,3,0) as opposed to =VLOOKUP(A8,$A$11:$E$17,2,0). Thanks again for your amazing work, I am smashing through this series and loving every minute!
Thanks for the editing, Nick! I have updated the file so our Teammates will have the correct file : )
Loving it!, Thank you sir
excellent explanation! love it!
Thank you for your step by step!! SO helpful!!
You are welcome for the so-helpful, Cathy!!! Thank you for the support with your comment, Thumbs Up and Sub : )
Another great lesson! Thanks a lot! Thumbs up!
You are welcome, Elmar : ) : )
Amazing. So clear. Thanks for your effort. :)
You are welcome for the clarity!!!!
Thank you so so so much!! You are such a blessing!!!
You are welcome so much, Theresa!!!
You make us habitual of working on excel like we working professionally..
Yes! Working professionally in Excel is efficient, profitable and fun!!!! Thanks for your support, Sever Engineer, with your comments, Thumbs Ups and your Sub : )
Great as usual mike .... this channel is a gold mine ..... explore to ur satisfaction.....But this will turn into a diamond mine after March :D
Thank you for your kind words!
Amazing explanation!
Glad it helps!!!!
Indeed One of the most important!
For sure, VLOOKUP is often used, solves many Excel problems and employers love to ask potential employees if they know what it does : ) Thanks fro your support !!!
Extremely excellent
Sir I can't compliment you enough, thanks for your teachings. I feel so expert at excel. Keep up the great job.
You are welcome! Thank you for your support with your comments, Thumbs Up and Sub : )
Thank for your excellent tutorial
You are welcome, Shahin : ) : )
Even before watching worth to say thank you.
You are welcome!
Thanks Mike. Very well explained :)
You are welcome!
Great video. Your homework problems are really awesome and helpful. God bless you. ❤️😭❤️
Homework is fun : )
@@excelisfun indeed
Very good I learnt about vlook up table and its utility.
Glad it is very good for you, Mahesh : )
u r a master teacher ..god bless u....giving free knowledge ..can u teach how to make complecated forms in excels with rows and coloums..merge ,center ,orientation of text....we many times receive forms in excel...with too many data...i always wonder what excel tools are used ,,,to create such forms..
Amaaaaazing videos. Thank you thank you. I now love excel
You are welcome! I am glad that your are falling in love with Excel. Thanks for your support with your comment, Thumbs Up and Sub : )
EXCELlent video. Thanks Mike
You are welcome!
Great Work. It has helped me a lot to understand lots of features of Excel. I wanted to point out that there is a problem when trying to download the course excel file of this video. I was able to download the excel files from your previous videos but not this one and i observed that the same problem appears in many of the following videos.
Thanks again. Excellent!
You are welcome again !!! Thank you for your support!
as always THANKS !
Thanks a lot for all lessons you make.❤
You are welcome for the lessons that I make, Lamees!!!! Thank you for helping support my efforts with your comments, thumbs up and of course your Sub : )
@@excelisfun Thanks for your nice words, you are the best in teaching.. I was looking for someone like you to study excel. I have learned a lot just from basics lessons.. How if I continue with advanced lessons...
@@lameessy2302 , Here is the playlist of my advanced class: th-cam.com/play/PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw.html
But please help with comments and thumbs up on each video. It is the pay you can give for the class : )
Great job, appreciate it sir
One of the most important Excel functions for last 30 years: VLOOKUP. Glad you like it all, bavaji : ) : )
Amazing! I love excel, thank you so much, again! =)
Yes!!! I love to hear that you love Excel, Debry!!!! You are welcome so much again! Thanks for your continued support with your thumbs ups and comments : )
Thanks for today lesson
You are welcome, Febri!!!
Man of the People!
Just a guy having fun with Excel : )
Sir by watching your videos it fees me like excel is very easy. Tanq You. :)
Glad to help you feel the power and easiness of Excel, Bharat!!
Amazing as usual. Was looking forward to learning this. Curious to see if there is a video that will go over Index-Match, since I've heard it's sometimes preferable to VLOOKUP. Thanks a bunch!
I am glad that the videos help! Thanks a bunch for clicking Thumbs Up and leaving a comment on each video that you watch as support for the free resource. And Sun so you always know when new vids come out. INDEX and MATCH? Not in this class, but I have 100s of vidoes of that topic already. Here is a great "getting started" one: th-cam.com/video/5jbNQg2pcrU/w-d-xo.html
Loved the hw no 3
thanks a lot mike!
You're welcome!
Wow awesome... thank you so much
You are welcome so much, Maha!!! Thanks for your support with your comment, thumbs up and your Sub : )
Thanks mike for this review very fun
Glad it wad fun! Thank you for your support : )
Made life so much easier!
Glad it helped! Thanks for the support with your comment, Thumbs Up and Sub : )
Great vedio sir ❤️
Thank you!
VLOOKUP you are welcome!!!!
Thank you for this great Video.
I am not sure how reduce my time working on 2 differents workbooks.
some infos I need are in severals cells in the same worksheet from workbook A. And the lookup value is the chq number.
I would like vlookup to gather for instance the amount, payment date, bank ref, customer ref from Workbook A and find all these values and populate them on Worbook B in specific cells that need to be completed with the same info.
I hope this makes sense. Can V-lookup do this? Thanks a million.
again a awesome Video bro thank you for that
You aer welcome!
*are 😂
thanks a lot from Egypt
You are welcome! Thanks for your support : ) It helps a lot!!
Thanks again!
You are welcome, Stephanie!!!
You are the best trainer! Love all your videos!
Can you recommend a good TH-cam channel for SQL tutorials? Thanks so much!!!
I do not know a good one for SQL. Glad you like the Excel videos. Thank you for your support with your comment, Thumbs Up and Sub : )
thank you God Bless you Sir
You are welcome! Thanks you for the support : )
Thanks!
First of all, a big thank you for all this content, you're doing a life-saving and a time-saving job :) I was wondering in the 4th example, if bonus commission should be related to the wage of each employee. I mean, the logic is the same, we should find out if employees achieve to sale 10.000 or more to get a bonus, but the actual amount of the bonus shouldn't be related with a column named "wages" instead of sales? e.g. employee has a wage of 3000$ per month, if he sales 14.000, he should have taken a bonus of 1%, i.e. 30$.
Hoping for your response!!
Sir you are really great, thanks
You are welcome, Girish!! Thank you for the support with your comment, Thumbs Up and Sub : )
Very good video. I use VLOOKUP all the time.
Glad it is good. Thanks for your support : )
GOLD video thanku sir!!!!!!!!!!!!!!!!!!!!!!!!!!!!
You are welcome!!!!!!!!!!!!!!!!!
excellent tip with vlookup
Glad it helped, MrRamonm718!!! Thanks for your support : )
great video
Glad it helps!!!!
Thank you for the video on IF and IS functions. Mike, when I downloaded the file to follow along I wasn't able to type in any cells. The formulas were different.
I have no idea what is happening. What version of Excel, what country are you in?
Thanks Mike. (9 Examples). :)
You are welcome SQRT(81) times, WRH!!!!