Excel VLOOKUP Function - Both Uses and Hidden Dangers!
Ғылым және технология
The Excel VLOOKUP function has two ways it can be used, but most people only know one way. Also, many users make a huge mistake which can end result in monumental errors. In this video I not only make VLOOKUP fomulas easy, but I also make you aware of the dangers.
Download the Excel File: www.myonlinetraininghub.com/e...
0:39 VLOOKUP Exact Match
2:48 Dynamic Column Reference with COLUMNS
4:59 Dynamic Column Reference with MATCH
6:57 VLOOKUP Approximate Match
8:50 Causes of Common Errors
10:05 Dangers of VLOOKUP
Fix leading and trailing spaces with the TRIM function: www.myonlinetraininghub.com/e...
Microsoft 365 users should use the XLOOKUP function instead: • Excel XLOOKUP Function...
View my comprehensive courses: www.myonlinetraininghub.com/
Connect with me on LinkedIn: / myndatreacy
Пікірлер: 90
Thank you very much for the video Mynda. A great explanation, as always !!
@MyOnlineTrainingHub
3 жыл бұрын
Glad you liked it, Ivan!
For the first time I’m not confused with vlookup thanks Mynda so much Pam
@MyOnlineTrainingHub
3 жыл бұрын
So pleased to hear that, Pamela :-)
So far the best excel teacher I've seen! Thank you very much for great content 😊
@MyOnlineTrainingHub
3 жыл бұрын
Wow, thank you!
Hi Mynda. Thanks for this thorough and clear lesson on VLOOKUP, its uses and pitfalls. VLOOKUP is feeling a bit old and dated now, but when used correctly, it can be a quick way to grab needed information from a lookup table. As always, good learning at your channel :)) Thumbs up!!
@MyOnlineTrainingHub
3 жыл бұрын
Thanks so much, Wayne!
Another great video lesson. Even though I have 365 and am actually more familiar with the newer functions, I’m trying to learn the older methods/functions
@MyOnlineTrainingHub
3 жыл бұрын
Thanks, Patrick!
Very nice teacher, her way of presenting is smooth and clear/ concise. Thanks
@MyOnlineTrainingHub
3 жыл бұрын
Thank you! 😃
Great overview of VLOOKUP!
@MyOnlineTrainingHub
3 жыл бұрын
Glad you liked it, Doug!
Thank you so much! This So useful!
@MyOnlineTrainingHub
3 жыл бұрын
Pleased to hear that, Alejandro!
Amazing! Hoping to learn a lot from you 😊
@MyOnlineTrainingHub
3 жыл бұрын
Happy to hear that, Arun!
a good excel teacher i have ever heard, thank u.
@MyOnlineTrainingHub
6 ай бұрын
Wow, thank you!
Thanks for useful lecture, It's really helpful.
@MyOnlineTrainingHub
3 жыл бұрын
Glad it was helpful, Muhammad!
Great Video!!! Thank you so much!!!
@MyOnlineTrainingHub
Жыл бұрын
Glad you liked it!
Thank you, your a life saver!!!
@MyOnlineTrainingHub
3 жыл бұрын
Happy to help!
Hey u r amazing..can u make videos on analytics also basically showing focus improvement areas based on the current data..
Quiet easily explained. TY.
@MyOnlineTrainingHub
11 ай бұрын
Glad I could help!
Thank you very much for great content
@MyOnlineTrainingHub
Жыл бұрын
Glad you enjoy it!
I love your videos! Can you make one talking about an email generator from excel?
@MyOnlineTrainingHub
3 жыл бұрын
I'll keep it in mind. In the meantime here are some tutorials we've got on emailing from Excel: www.myonlinetraininghub.com/vba-to-create-pdf-from-excel-worksheet-then-email-it-with-outlook www.myonlinetraininghub.com/automating-emailing-pivot-table-reports Hope that helps.
Well Done Mynda
@MyOnlineTrainingHub
3 жыл бұрын
Cheers, Shakira!
Super useful 👍😁👍😁🙏
@MyOnlineTrainingHub
3 жыл бұрын
Glad you think so!
Hi Minda, great video, slight editing mistake at 4:28 - You needed to tab across to show the formula in cell F5 as opposed to E5, giving the range COLUMNS($H:J) returning column number 3.
@MyOnlineTrainingHub
3 жыл бұрын
Yes, you are right. Doh!
Great video Mynda! Though I now really prefer XLOOKUP as u point out.
@MyOnlineTrainingHub
3 жыл бұрын
Cheers, Chris, me too :-)
Thanks for the tips. index and match is far better. to covert text to number i used to select the entire column and click text to column and click finish.
@MyOnlineTrainingHub
3 жыл бұрын
Yes, INDEX & MATCH gets around the limitations and dangers of VLOOKUP, but it can be quite daunting for many people, so I like to start teaching VLOOKUP and then move on from there. Great tip on converting text to numbers!
Nice 👍👍👍
@MyOnlineTrainingHub
3 жыл бұрын
Thanks for the visit!
very nice 👍👍 Please include suggestions for Indonesian subtitles.
@MyOnlineTrainingHub
3 жыл бұрын
Thanks, Hanif. I don't choose the subtitle options, this is up to KZread.
Amazing, thank you. I'm using vlookup to compare two sheets and find the differences. But how can I highlight the differences to analyse why/how the data has changed? When I use the vlookup with conditional formatting the document gets massively big. Is there an easy way to compare the data, highlight the difference and extract that data to analyse? Many thanks
@MyOnlineTrainingHub
3 жыл бұрын
I recommend you use Power Query to compare two lists as explained here: www.myonlinetraininghub.com/excel-compare-two-lists
Hello, please check the access to the Excel file as it cannot be downloaded. Thank you
@MyOnlineTrainingHub
3 жыл бұрын
Sorry, Jorge. File download is fixed.
Mam I learning many thing from your videos... Mam kindly upload more videos of power query ☺️
@MyOnlineTrainingHub
3 жыл бұрын
Great to hear, Anup! More PQ coming :-)
Thanks for the great efforts. I am unable to Download the Excel File, is there any issue with the link?
@MyOnlineTrainingHub
3 жыл бұрын
So sorry, Mutaz. File download is fixed.
Good 👍👍
@MyOnlineTrainingHub
3 жыл бұрын
Thank you! Cheers!
Game changer
@MyOnlineTrainingHub
3 жыл бұрын
Sure is, Benjamin!
These videos are amazing!! I am putting together a spread sheet that uses the VLOOKUP function to bring up sales id, personal details for customers and items purchased. Can you please help me find sample data of items that would be sold at an electronics store along with prices? For example computers, phones, accessories, printers etc. Thank you
@MyOnlineTrainingHub
3 жыл бұрын
Glad it was helpful! Finding sample data is very difficult. I don't have any like you describe.
@nifaefesolai5026
3 жыл бұрын
Thank you for letting me, your videos are 👌 btw
Thank you for knowledge of Excel . I want to request can you put link free download practice file 😁.
@MyOnlineTrainingHub
3 жыл бұрын
You're welcome! The link to the file is in the video description.
HI Mynda nice but why you select f4 to select dolor sign we also do this without f4
@MyOnlineTrainingHub
3 жыл бұрын
Thanks, Mayank! F4 is the keyboard shortcut to applying absolute referencing in formulas. It's quicker than typing in the dollar signs.
I can’t think of any situation where “index match” isn’t a better option. Is there any?
@MyOnlineTrainingHub
3 жыл бұрын
Hi Jeffrey, VLOOKUP is a great place for beginners to start. INDEX & MATCH can be daunting to those new to formulas. Of course, nowadays we should all be using XLOOKUP: kzread.info/dash/bejne/ZIqdr89mp9izY7g.html
Hi Would you please help to sort out my problem? I have a work sheet where VLOOKUP working properly but when i add a cell value that contain sheet name using formula =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255). No issue at all, adding sheet name manually in that cell. but adding with formula i got error.
@MyOnlineTrainingHub
Жыл бұрын
Hard to say without seeing the file. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
What's the difference with/without the dollar symbol being in the following positions: (C5), ($C5) and (E$4)?
@MyOnlineTrainingHub
3 жыл бұрын
The dollar symbol is used for absolute referencing as explained here: www.myonlinetraininghub.com/excel-absolute-references-the-missing-link
@mahj1982
3 жыл бұрын
@@MyOnlineTrainingHub thank you so much. 😁
Vlookup 😎 : it's done, and without dangers Xlookup 👀 : I can't believe, she did that again...!!
@MyOnlineTrainingHub
3 жыл бұрын
Yes, XLOOKUP is the best!
Index match vs xlookup ?
@MyOnlineTrainingHub
3 жыл бұрын
XLOOKUP every day :-)
when I go to enter the formula =VLOOKUP it does not exist? is it possible this feature was turned off?
@MyOnlineTrainingHub
3 жыл бұрын
It's definitely not turned off. Are you trying to use VLOOKUP in Power Query? Please post your question and screenshots on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
Why would you choose to use Vlookup over Xlookup?
@MyOnlineTrainingHub
3 жыл бұрын
The only reason you'd choose to use VLOOKUP is if you have a version of Excel that doesn't have XLOOKUP...i.e. Excel 2019 and earlier, which is the vast majority of current Excel users.
@howardsmith2
3 жыл бұрын
@@MyOnlineTrainingHub Thanks, just wanted to check I hadn't missed something!
you mean XLOOKUP? 😏
@MyOnlineTrainingHub
3 жыл бұрын
um? XLOOKUP: kzread.info/dash/bejne/ZIqdr89mp9izY7g.html