Mysteries of VLOOKUP Function Revealed! 15 Amazing Examples! (Excel Magic Trick 1514)
HTML-код
- Опубликовано: 9 июл 2024
- Need to learn all about VLOOKUP? Microsoft Excel MVP & Highline College Professor, Mike “excelisfun” Girvin, presents 15 amazing VLOOKUP examples, from the basics to advanced.
Download Excel START File: excelisfun.net/files/StartFil...
Download Source file: excelisfun.net/files/SourceDa...
Download Excel FINISHED File: excelisfun.net/files/Finished...
In this video Learn all about VLOOKUP. Learn from Basics to Advanced. See 12 amazing examples that will help you become a VLLOKUP Excel Master! Video taught by Microsoft Excel MVP and Excel RUclipsr, Mike Girvin.
Topics:
(00:06) Introduction
1. (01:32) VLOOKUP is everywhere
(04:24) The different between Exact Match & Approximate Match Lookup
2. (06:00) VLOOKUP to Lookup Product Price (Exact Match Lookup)
3. (12:25) VLOOKUP to Lookup Straight Commission Rate (Approximate Match Lookup)
4. (18:32) Data Validation List & VLOOKUP
5. (21:45) Copy VLOOKUP Down a Column. Learn about Relative and Absolute Cell References.
6. (27:00) Dynamic Lookup Table: Excel Table feature
7. (31:47) Dynamic Data Source: Use Power Query to import Lookup Table
8. (36:14) VLOOKUP to Lookup Variable Commission Rate (Approximate Match Lookup)
9. (41:00) VLOOKUP & MATCH Function for Two-Way Lookup (Lookup Employee Information)
10. (48:02) Fuzzy Lookup = Incomplete Lookup Value
11. (51:50) VLOOKUP & IFNA Functions to Avoid Errors
12. (53:05) Partial Text Lookup & Converting Text Number to Number
13. (56:28) Avoid Zeros from VLOOKUP to Empty Cells
14. (58:27) Multiple Table Lookup with VLOOKUP and INDIRECT Functions
15. (01:04:40) Two Lookup Values
(01:08:29)Summary
If there will come a time when all yt channels will go away, yours is the only one that truly has to be kept online. It's pure gold!
I love the way you show mistakes, and explain everything in bite sized pieces; every little detail and then repeat it a couple of times, so much easier to grasp and memorize.
Congratulations on 500k! This week is VLOOKUP week :) Love the detailed explanation and variations!
VLOOKUP Week: I love that!!! Thanks for the congrats, Teammate : )
@@excelisfun Hello :)
I was trying to search google about this but I cannot find it.
My question is: to teach excel do I need to have any / some Microsoft finished course or pure knowledge is enough without "MS Papers"?
@@ExcelInstructor I have zero idea. There are no rules for who gets to teach and who does not. Teaching is always contingent on what the contract says
@@excelisfun yeah, im simply asking that if I wish to teach excel (at least the stuff I know and can do) do I need some special course from Microsoft? SoI wont get sued by Microsoft for teaching excel without proper documents from them.
@@ExcelInstructor !?!?!? What. No way : )
Mike has always and will always be the best Excel teacher I have met on RUclips
Thank you : ) : )
I don't really have a use for all this excel magic, but it certainly is entertaining, you are an excel God.
Thanks, TheHolyphantom! I am glad that it not useful AND magically entertaining : )
I never get tired of watching this fun. Thanks Mike...
Go Fun Team!!!!!!!!
Very useful. This combination Vlookup and MATCH is great. Thank you
Try this the Newest Tips in Vlookup
Vlookup dari Bawah ke Atas /
Vlookup Dari Data Ter-update /
Reverse Vlookup Return Value from Below
ruclips.net/video/yaD8PD7SObQ/видео.html
First I click like and then I watch your videos.
Thanks for your support, PR!!!!
HEY!! You got Power Query into this example! I have been going thru playlists studying Power Query, Power BI and Power Pivot and this video really hit home. I can feel it in my bones! Its all starting to click! 👍
I am so glad that it is all starting to click! That means you are getting real power : )
OMG! I learned more a lot in your videos than attending my computer classes! Good job. Continue helping students. Thanks a lot
I am glad that the video helped you to learn, Adrian!!!! Thanks for the support with your comment, Thumbs Up and Sub : )
I have viewed many RUclips instructional clips. You are by far the BEST. Precise, to the point, no deviation away from the subject matter and ahhhhs and emmmms.
You have designed a step by step instruction by smart usage of the worksheets.
Yes. You are right. I have been using long “If” argument when Vlookup can do the job. Thank you. Great job.
Yes, you and I and many others use the long IF and then we learn VLOOKUP - and Bam! our lives become much more fun!!!! Thank you for your kind words, Leong Peng Kong, and thank you for your support with your comment, Thumbs Up and Sub : )
Mike,
This is Murali from India. I saw the whole video and I have a great fan of yours. I have also subscribed your channel. You are doing an awesome job there. Keep up the good work and we excel lovers are inspired by your marvelous effort that you put for each one of your videos.
We continue with the lessons, thank you very much
Among Excel instructors that are available on RUclips, I must say that you are the best of the bests. Hat off!
Thanks for the hats off, Duy Hung!!! I have 3000 other videos and almost any type of topic or class that you might want concerning Excel. Have you subbed and seen the home excelisfun channel? Anyway, thanks for your support with your comments, thumbs ups and Sub - it helps me to keep being the best : )
Thank you, Mike. This one reminds me of your VLOOKUP Shark Week video that I enjoyed during the summer. And, of course, there is VLOOKUP in every Excel full series. An important subject matter.
Yes, this is an updated and more carefully edited version of that older video from back in early 2012!!!! Yes, even though we have new tools like Relationships, Data Models, Power Pivot and Power BI Desktop, VLOOKUP is still one of the most important topics because there are hundreds of uses where we need it for calculating and other Excel spreadsheet solutions. Plus, it is still one of the key questions asked in job interviews. Thank you very much, M. SZ. for your support of the excelisfun channel here at RUclips : )
Unbelievably professional
This is so awesome. Thanks Mike for this EXCELlent video.
You are welcome, Syed!!! Thank you for your amazingly consistent support : )
Great examples Mike, very useful!!
It is a LOT about VLOOKUP, Chris! Glad they are great for you, and of course, I always appreciate your consistent support : ) : )
It's always fun to come back and refresh the lessons. I just did. Awesome!!!! Love your channel the detailed lessons and the homework's. The level of organizations for the follow along and homework workbooks makes life easy
I am glad that the organized materials help, Joel!!
Thank you! Another great video.
You are welcome for this one too!!
Again another great video! Thank you,
You are welcome, Mamadou!!!
Hey! The way you taught us v lookup..i think this is the best ever learn video available on RUclips for learning you v lookup... I will recommend this video whosoever wants to learn the correct applicability of v look up. Once again thnks for making effortless video.
You are welcome, sachin! Thank you very much for your support with your comment, Thumbs Up and telling your friends : )
Truly brilliant, the vlookup match function just helped me reduce a heavy nested if statement to a much concise formula
I love to hear that a trick in this video helps you to become more efficient! Thanks for your support, Amit, with your comment, Thumbs Up and Sub : )
Thank you very much , great as always 🙏
Hi Mr. Mike again you made it so easy for every single person to understand how to use LOOK up function even if he doesn't know any thing about excel thank you.
You are welcome! I did try to break it down and show all possibilities so that who ever watches will have most questions answered : ) Thanks for you for your support, Ismail : )
ExcelIsFun you're welcome
@@wayneseymour1 sir my question is I have 9 digiti MICR Code I want it to break into this in some parts.
Like I have breaked it down in left 6 using left function and breaked it down in 3 digits using mid function and took left 6 and mid in vlookup formula all was ok but I want it to break down furthermore.
Like this =vlookup (concatenate (left(a2,1),mid(a2,4,3)), table aray, 1,0)
Is this possible.
Please do this for it will help me.
In solving some of the things.
Because I am not getting any answers by using this.
I want 1st digit and middle 3 digits fixed and all remaining digits should be variable.
Mike for me you are the best Excel teacher i came across as you make Excel for Fun ;)
Michael thank you for sharing your excel knowledge with us! I find your videos and exercises very useful!
Glad they are useful for you!!
This is simply fantastic. You have given multiple examples for vlookup. Also I love it when i get to work on the sheet as I see you do it
Glad you like it, maria!!!! There are many more videos with the Excel workbooks at this excelisfun channel : ) If you need a specific topic just search or ask me : )
You Da Real MVP !
Glad you like the video, afzongho!!!! Thanks for the support : )
Excellent video!
Excellent explanation thank you very much
You are welcome for the EXCELlent video!!!! Thank you, mahesh .n, for your support with your comment, Thumbs Up and Subscription : )
Hi Mike.. an excellent and comprehensive tutorial on VLOOKUP and various creative techniques to use it effectively to do work. Since I have spend time with your Busn 216 and Busn 218 video series, I found it all very clear and understandable. I can't tell you how fortunate we are as a community to have you sharing your knowledge and teaching us all how to be better at EXCEL and at our jobs. You are the master. Thanks for your generosity and your continued support and contribution to the community of students, professionals and peers who benefit from your excellent videos and also the sample files that make it so easy to follow along and practice the concepts during the tutorial. Thumbs up!
You are welcome, Wayne!!! I am happy to provide resources for the last ten years for all that want to learn : ) Thank you for your support!
I put like before watching ! I know these videos are amazing ! Thanks!
Thank you, Veronica!!!! Thanks for your support : )
Same
i have been using the vlookup function for past 3 years...but after watching your lecture..ohhh..its simply adorable,amazing...you have given simple and super examples....& all these examples must be useful during the work....Thank you sir..
Many Thanks Mr. Girvin
Many You are welcomes, Hazem!!!
Nice video as usual. and yes you did it : 500k subscribers very well done and well deserved. thank you.
You are welcome for the video : ) Thank you for kind words, elhassan!!!!
I went here just because I was curious about the magic but watching this you solved 4 problems I was having and didn't know a solution even existed. Thank you!!!!
Thank you soooooo much sir.
I absolutely appreciate your work. Thank you, thank you so much.
Thanks, VTstudent! I appreciate your support with your comment, thumbs up and of course your SUB : )
Thank you Mike!
You are welcome, Ki!!!
Nicely explained. Thanks Mike.
Glad it is vice for you, Salim!!!!
best teacher
This is fantastic !!! Thank you.
Glad it is fantastic for you, Tammara!!!!
Mike, take a bow!
I can't do that because it would take time away from making more videos ; )
Thanks for your support, amit kumar!!!!
FANTASTIC
Glad you liked it, Maria!!!
Amazing! This is goldmine!
Glad it is a goldmine for you!!!
very well done. Mike you did it again your videos are so so valuables and clear. thank you for your sharing.
I am glad that the videos are of value to you and that they are clear!!! Thanks for your support, elhassan simlaoui, with your comment, Thumbs Up and Sub : )
Thanks Michael, worked out fantastic
Yes!!!! I love to hear that : )
Dude ! You are the best!
You are welcome, Jorge!!!
Thanks for another great video on this very useful VLOOKUP function. So many cool examples showing what is possible with this powerful tool. My personal favorite was #14 with all those pipe price tables coming together like a well-plumbed charm. All of the different examples are superb, surely very helpful for people in all sorts of fields. Also, thank you for the time-stamps and helpful files that we can practice and learn with.
:)
You are welcome for the video and time hyper links and files, AJ!!!! Thank you for your support of the excelisfun channel here at RUclips : )
It’s really a great tutorial! Complete and very useful. Thanks for your hard work.
You are welcome, Kawsar!!! Thank you for your support with your comment, thumbs up and Sub : )
So we are back in future.... Everyone starts excel with Vlookup... Thanks for great examples....
Never imagined vlookup video could be one hour long.. Hah
One hour!?!? It should have been two hours, but I had to keep it short : ) Thanks you for the support, RRR : )
Hello Mike, you've brought us a few more tricks to boost Vlookup, sensational, you're the guy. Thanks again, top content.
Glad that there were a few more tricks for you, Luciano!!!! Thanks for you for your support : )
Thanks a lot !!!!! the video has a lot of details and the explanation goes deeply and steps by step, Muchas gracias!!!
You are welcome! Thanks for the support with your comment, Thumbs Up and Sub : )
You are EXCEL SUPER MAN.....
Thank you Mike
🤗
You are welcome, Katerina!!!!
Excellent video!!! Getting ready for an exam in order to get a promotion!!
One of the best & most comprehensive explanations about the versatility of the Vlookup functions with its use & applicability in a wide variety of situations & ways. Thanks a lot for the efforts taken , God Bless
You are welcome a lot for the VLOOKUP efforts! Thanks for your support, Rajesh Jawajala!!!!!
Hi Mike, I love your Videos. The quality of your videos are very high, some teacher should watch you. Greets
Thank you for your kind words, shaver71!!! And thank you for your support : )
Bundle of thanks!!
You are welcome!
Excellent presentation Thank yu :)
I am glad it is EXCELlent for you, mostafa4321!!!! Thank you for the support with your Comments, Thumbs Ups and Sub : )
Very useful. Thank you so much
Glad it is useful! You are welcome so much, kaiqin!!!! Thanks for the support with your comment, Thumbs Up and Sub : )
Thanks Mike
You are the best sir.
Glad you like the video, Fuad Khan!!! Thank you very much for the support with your comment, Thumbs Ups and Sub : )
Hi,
Thanks for uploading an exhaustive video on age old and most popular VLOOKUP. Not many still know how versatile VLOOKUP can be when used with other functions. Also the approximate match feature of this function is something really amazing in specific situations. Thanks again :)
You are welcome!!! I agree: VLOOKUP has been around for a long time and it is still useful today : )
you are just amazing!!
Glad you like the video, mahesh!!! Thank you for the support with your comment, Thumbs Up and Sub : )
really awesome the use of table I haven't seen anyone in this combination
Glad it is helpful, Sever!!!
Thanks as always.
You are welcome, as always!!!
I watched the video for the second time and the coolest thing is that more you get the experience more you assimilate
Unfortunately, I can't hit the thumbs up twice so I give it in this comment👍🙂
Yes, sir, I appreciate your love for Excel, your hard work to learn it, and for your support, especially with your second comments when you watch videos a second time : )
Thanks Gel!
You are welcome! Lots of fun Gel Boom examples in this video : ) Thanks for your support !!!!
Great video
You are the best....really, I have learnt so much from you. I love the way you explain , I started right at the beginning, example #1, and you explain it so well . Congratulations and THANK YOU.
You are welcome, yasmin!!!!
Perfectly explained!
Glad it was good for you, Ilya G!!! Thanks for the support with your comment, Sub and Thumbs Up : )
I am BIG Fan! My name is Ilya Gordeev.
Excellent tutorial for beginners. I am following.
I am glad that the videos help, Dillip!!! Thanks for your support with your comment, Thumbs Up and Sub : )
Excel is really fun. You have made it funnier :)
Yes!!! Glad to hear that it is funnier for you : ) Thanks for the support, Ziaur!!!
If there is a VLOOKUP God, it'd be Mike. ✔️
Just a guy having fun with Excel : )
Amazing like always Master...No comment ... Thumbs up :-) and thumbs down for whom putting thumbs down
Thanks for your kind words! And thanks for your support, Mohamed Chakroun!
U are the master in excel, i have learnt a lot through ur vdo, now i am going to training in my org..thanks a ton!
You are welcome! That is great that you can take what you learn and "play it forward" and teach others! Thanks for your support with your comment, Thumbs Up, Sub and by telling all your friends : )
I ENJOYED MYSTERIES OF VLOOKUP..............MAGIC LOOK UP....THANKS A LOT SIR
!!!!!!!You are the best
Just trying to have fun with Excel and make the world a better place by providing free Excel Education here at RUclips : ) Thanks for your support, Yoram Avraham!!!!
Excellent Teaching , I learn lot, Thank you very much
You are welcome for the EXCELlent teaching, John!!! Thanks for your support with your comment, thumbs up and your Sub : )
in my job I got a problem same situation as Commission Rate EX, and ...now I have a solution, good job mike!!!
Awesome! Do you mean the Variable Commission Rate Example? If yes, here is the real video to watch that shows how to make the actual Lookup Table: ruclips.net/video/eFj1MxraX1Q/видео.html
Thanks for your support, DIGITAL COOKING!!!
amzing explation done by you
Glad the videos help!
U R a great guy, top teacher, u deliver the info very easily & give us everything we need to learn :)
Thanks, Slim! Glad it all helps you : ) : )
Greeeeat video!!!!!! congrat
Glad it is great for you, Reversal89!!! Thank you for your support with your comment, Thumbs Up and Sub : )
After following most of your videos, I can at least say that I'm Understanding Excel in this part of the World. Thank you Mr. Excel.
You are welcome!!! But I am not Mr Excel. That is a different RUclipsr called Bill "Mr Excel" Jelen. I am excelisfun : )
Excellent video
Glad it was EXCELlent for you, HABIBUL!!!! Thanks for the support with your comment, Thumbs Up and Sub : )
Great video. Are they any videos with transactions codes, roles, and employees? Thank you!!!
It's always best👍👍
You are welcome : )
Congratulations.....
For 500k subscribers...
Thanks for the Congratulations, Tulsidas, and thanks for your support : )
Thx 🙏
You are welcome!
Great great great you are awesome ....👌👌👌👌👌👌👌👌👌👌👌👌👌
Thank you for the many, many Okay hand symbols : ) : )
THANK YOU SIR FOR EVERYTHING, THIS VIDEO IS DOPE. IN ADDITION, AT 1:06:26 (THE LAST SHEET(15)), I USED THIS FORMULA :
=VLOOKUP(D5&"R",$A$5:$B$18,2,FALSE) ; INSTEAD OF USING THE CELL , I USED "R"
BY ACCIDENT AND IT WORKED FINE FOR ME
Thank you
You are welcome, Shahin!!!!
I want to be a data analyst. Your video are helping me a lot
Glad the videos help! Thank you for the support on each video with a small comment, Thumbs Up, AND... be sure to Subscribe, because I have EXACTLY what you want to become a Data Analyst: Starting early next week I will be posting a 10 Video Class about Learning the Basics of Data Analysis in Excel. Then after that series you can switch over to the MSPTDA class (19 videos already posted with about 15 more to come), which is an advanced Data Analysis Class with Microsoft Power Tools for Data Analysis. Here is the link for MSPTDA: ruclips.net/p/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1 But if you are just starting, wait and watch the 10 Data Analysis Basics Videos that I will post over the next two weeks, then switch over. See you in the comments, Sazzdur!
Nice to see that you are a fellow Washingtonian 😉😉
Yes, glad you liked the video fellow Washingtonian!!!!!!
VLOOKUP fun! Thanks, Mike!
You are welcome, Teammate Malina!!!! VLOOKUP is fun : )
you are amazing
Glad the video was amazing, Mohamed! Thanks for your support : )
Nice :)
Glad it is nice for you, TBsThaiBoxing!!!!!
Perhaps my formula does not belong in a VLOOKUP video but I always like to use the INDEX-MATCH combination so I don't have to worry about the VLOOKUP column being on the left. Here is the formula I have in example (9) cell B27 =INDEX($A$8:$G$17,MATCH($A$27,$A$8:$A$17,0),MATCH(B26,$A$7:$G$7,0)). Thanks again for the VLOOKUP video. I always learn new things from your videos!
Yes, INDEX can do almost everything that VLOOKUP can do, but it is still so widely used and has no real performance differences as compared to INDEX and MATCH, and job interviews like to ask about it, I must still teach it, and in fact, I prefer VLOOKUP and MATCH for math two way lookups because for me, it is easier to type the formula out and it only uses two functions. Thanks for your support, Rob!!!