Excel Basics 14: Excel VLOOKUP Function Made Easy! 9 Examples to Make Your Job Easier!
HTML-код
- Опубликовано: 6 окт 2024
- Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: people.highlin...
Download Full Written Notes: people.highlin...
Class web page link: people.highlin...
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
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 : )
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 : )
You promised FUN and you delivered! Thank you Mr. Girvin!😊
You are welcome for the fun!!!
This is hands-down the best excel tutorial I have ever seen. Congratulations!
Glad it was helpful!
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 : )
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 RUclips for free : ) So you can access them anytime. Glad they are here for you, Libi!!!
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 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 : )
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!!!
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!!!
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!!!!
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!!!!!
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 : )
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 : )
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 : )
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 RUclips for you!!!!
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 RUclips. Nice to connect with you!
This was great for explaining and using VLookup. I’ve tried and failed a number of times at understanding it. I love that you reinforce many of the things we learn throughout the lesson and in subsequent lessons. However, in the homework section, which I’d nailed in every lesson until I got to the very last one in this class when you mention about what to return if the cell is blank. I simply couldn’t remember how to use isblank and ifna as you kind of glossed over it in the previous lesson and didn’t show how to use it with vlookup, so this was a really tough one. In the end, I had to keep pressing F2 on the answers page. Now that I have done it a few times, I have a sheet at work that I can test it on multiple times for practice. I’m sure it’ll became second nature. I’ve learned so much from these lessons and hugely appreciate that they’re free. Knowledge is power.
Heather Bell Bikes (love the name), just put VLOOKUP in the first argument of INFA and the two double quotes "" in the second argument. Bam: that should work : )
@@excelisfun Perfect, thanks for the help. I was so confused!
I like to cycle, hence the name 😊
@@heatherbellbikes Excel and Bikes go together perfectly : )
@@excelisfun Just not at the same time, though!
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. : )
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!
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...
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!!!!
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 : )
I'd consider myself an excel pro... But these videos are absolutely amazing for reviews and potentially picking up new tidbits.
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 RUclips lol
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 RUclips 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 : )
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!
You are a great teacher.... God bless u🙏🙏
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 : )
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 : )
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 : )
BIg thank you to the best excel teacher ever!
I love your end sentence so much " Because there's always lots more videos to come from excelIsFun"
Glad you love it, Mostafa!!!!
I love how you explain vlookup
It's very easy for beginner like me to understand
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 : )
another great video for understand how the function works, thanks for great lesson!
You are welcome!!!!
Great video. Your homework problems are really awesome and helpful. God bless you. ❤️😭❤️
Homework is fun : )
@@excelisfun indeed
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 RUclips 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 : )
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!
Even before watching worth to say thank you.
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 : )
Hello Mike! Amazing content! You are a great trainer. Thank you so much!
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 : )
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 : )
Very well explained. Thank you!
You are welcome!
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!!
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 : )
GOLD video thanku sir!!!!!!!!!!!!!!!!!!!!!!!!!!!!
You are welcome!!!!!!!!!!!!!!!!!
VLOOKUP is an awesome function))) it’s so useful!!!! Thank you for lesson!!!!!
You are welcome, _AnBu_!!!! Thanks for your support : )
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
Amazing explanation!
Glad it helps!!!!
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 : )
As usual, mind blown. Thanks.
Yes, Mind Blown is a positive direction is always good!!! Thanks for your support : )
Amazing. So clear. Thanks for your effort. :)
You are welcome for the clarity!!!!
Thank you for these video tutorials! they are amazing! You rock :)
You are welcome, cindy!!!!
Man of the People!
Just a guy having fun with Excel : )
Very good I learnt about vlook up table and its utility.
Glad it is very good for you, Mahesh : )
Thank you so so so much!! You are such a blessing!!!
You are welcome so much, Theresa!!!
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.
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 !!!
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!!!
Thank you!
VLOOKUP you are welcome!!!!
Mind blowing stuff! This is fantastic.
Glad you like it!!
Another great lesson! Thanks a lot! Thumbs up!
You are welcome, Elmar : ) : )
Extremely excellent
Loving it!, Thank you sir
Very good video. I use VLOOKUP all the time.
Glad it is good. Thanks for your support : )
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..
Thanks for today lesson
You are welcome, Febri!!!
Made life so much easier!
Glad it helped! Thanks for the support with your comment, Thumbs Up and Sub : )
Loved the hw no 3
thanks a lot from Egypt
You are welcome! Thanks for your support : ) It helps a lot!!
You are the best trainer! Love all your videos!
Can you recommend a good RUclips 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 : )
Great job, appreciate it sir
One of the most important Excel functions for last 30 years: VLOOKUP. Glad you like it all, bavaji : ) : )
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!
great video
Glad it helps!!!!
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 : )
Thank for your excellent tutorial
You are welcome, Shahin : ) : )
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!
thanks a lot mike!
You're welcome!
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 : )
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 : )
Thanks again!
You are welcome, Stephanie!!!
EXCELlent video. Thanks Mike
You are welcome!
Great vedio sir ❤️
thank you God Bless you Sir
You are welcome! Thanks you for the support : )
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: ruclips.net/p/PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw
But please help with comments and thumbs up on each video. It is the pay you can give for the class : )
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!!
i got ur channel link from a twitter mention...have to say u r a goldmine of excel.
i got ur channel link from a twitter mention...have to say u r a goldmine of excel.....is there any way that i can give donation...that wont b much as i m still a student,but still i would like to donate.....me from INDIA
No need to donate, but PLEASE click Thumbs Up on each video that you watch and leave a small comment on each video that you watch - that helps me and is better than a donate : )
No need to donate, but PLEASE click Thumbs Up on each video that you watch and leave a small comment on each video that you watch - that helps me and is better than a donate : )
Sir you are really great, thanks
You are welcome, Girish!! Thank you for the support with your comment, Thumbs Up and Sub : )
Mike, with all respects, thank you.
You are welcome! With all respect, be sure to click that Thumbs Up and leave a comment on each video : )
Already gave that Thumb :) and wait the next one impatientlly
Thanks you do much! I can't wait till the next one either : )
I like your work make a budget
Thanks again. Excellent!
You are welcome again !!! Thank you for your support!
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: ruclips.net/video/5jbNQg2pcrU/видео.html
Thank you very much ^_^
You are welcome very much!!!
excellent explanation! love it!
Thanks Mike. (9 Examples). :)
You are welcome SQRT(81) times, WRH!!!!
Very Good Video...
Glad you like it! Thanks for your support with your comment, Thumbs Up and Sub : )
excellent tip with vlookup
Glad it helped, MrRamonm718!!! Thanks for your support : )