Data Analyst Explains When to Use VLOOKUP vs XLOOKUP vs INDEX MATCH
Vložit
- čas přidán 29. 06. 2024
- GET YOUR DATA JOB ➡ mochen.info/
ULTIMATE DATA PORTFOLIO: Everything you need to get your data job ➡ mochen.info/ultimate-data-por...
AI JOB QUEST: Change the way you apply to jobs ➡ mochen.info/ai-job-quest-lp/
PATREON ➡ / mo_chen
1:1 MENTORSHIP PROGRAM: I put my heart and soul into this program. I'm here to help you succeed ➡ mochen.info/1-1-mentorship-lp/
CourseCareers: Start the FREE Data Analytics Introduction Course Now ➡ coursecareers.com/a/mochen?co...
Portfolio Projects Playlist: End-to-end, FREE guided portfolio projects covering Excel, SQL, Tableau, Power BI and Python ➡ • Portfolio Projects
┋Community & Socials┋
/ discord ⬅ Join our Discord Data Community
/ mo_chen1 ⬅ My IG is personal -- only follow if you're interested in non-data stuff as well
/ mo-chen1 ⬅ Daily tips & advice on how to enhance your data analysis skills
/ mo_chen_1 ⬅ Figuring out what I want to do in this space...
┋Professional Certificates┋
CourseCareers ➡ coursecareers.com/a/mochen
DataCamp Courses & Certifications ➡ datacamp.pxf.io/c/4216860/111...
Google Data Analytics Professional Certificate ➡ www.coursera.org/google-certi...
Google Advanced Data Analytics Professional Certificate ➡ www.coursera.org/google-certi...
Tableau Business Intelligence Professional Certificate ➡ imp.i384100.net/TableauBIAnalyst
Microsoft Power BI Data Analyst Professional Certificate ➡ www.coursera.org/professional...
IBM Data Science Professional Certificate ➡ imp.i384100.net/IBMdataScience
Google Project Management Professional Certificate ➡ imp.i384100.net/GoogleProject...
┋Timestamps┋
00:00 Intro
00:41 VLOOKUP
06:19 INDEX MATCH
15:35 XLOOKUP
19:18 Outro
Wanted to know these for years. Took 20 mins and now I know. Thank you Mo!!!
I'm so glad you liked the video! Thanks so much for watching 😁
Excellent video! You clearly explain each function with the pros, cons, and use cases for each. Well done! I learned a lot. Thank you!
Thumbs up for that "F4" callout, I was struggling to fix my fill option and you saved me! Excellent video.
What a fantastic tutorial, Mo. I am so impressed on how EASY and clear your explanation was. Instead of having us memorize formulas, you really explained the components of it in a way that just clicked for me. I appreciate your channel.
Hey Al 👋 I'm very glad my explanation was easy to understand! Thanks so much for watching 😄
1 - I disagree that if you are looking left or right that you should be using either vLookup or Index Match, you should just use index match in both cases.
2 - XLookup is easier for new users to audit the formula, so if its a simple file or references, I would just use Xlookup
3 - If you are auditing different worksheets, and you are referencing a cell in one worksheet, and searching for it in another worksheet, and you want to audit this row by row to make sure whatever its returning is correct, I would use Index Match in every case.
Why? - Because you can press Ctrl + [ to take you to the column that its referencing without using the mouse, and then you can press Ctrl + G then press enter to take you back to the index match formula
Also, Index match is the most computationally efficient
Thank you for the tip regarding jumping to the reference cells, I didn't know about that. I agree, I have started just usind Index Match exclusively and ditched Vlookup altogether. The problem with Vlookup is that once you start setting up multiple sheets with a web of different references, you can't add or remove columns without messing up your Vlookup formulas, which makes it harder to modify your sheets if your systems grow more complex. It creates scaling issues. In my opinion Vlookup is only appropriate for very simple spreadsheet structures as a quick and simple option to look up a value, but really it just seems like index match is the better function.
I'm not really familiar with Xlookup since I've been in the habit of using index match for a while, so haven't seen a need to switch what I'm doing since it already does exactly what I need it to. But I'm curious about the advantages and use cases for Xlookup since a lot of people seem to swear by it. But as for Vlookup, I still don't see an advantage to using it over index match, so probably will just leave it in the trash.
There's 3 reasons I would use XLookup:
1. It's simple/easy, good for new users or ppl who don't have an interest in mastering Excel
2. You are doing a Vlookup on an external file that isn't open. The Xlookup will work, while the index-match will have reference errors, unless the external file is open
3. If you're doing one of those logic formulas, based on multiple conditions, it's easier to examine or audit with an xlookup formula because its a more simple formula@@kenm6226
I think in both cases you should select an entire table for
table array step
Thanks Mo, Finally a tutorial that cuts right to the chase... your explanation is "spot on" - you have a skill in explaining in simple terms that really connect with me.
That's very kind of you to say, thank you 😄
Best tutorial I've seen. Very effective to make us understand which formula to use or not. Excel always felt to me like a mad scientist's laboratory.
You, real power, amazed! Thanks
Thanks a lot Mo. I just built an outstanding bill of material configuration template to complete my internship project. Couldn't be more grateful!
I’m so glad the video helped!
Great content, as a person starting a career in data analytics all over, your content is a great help, keep up your hard-working, thanks 😍
Hi Lamis, thanks so much for the nice words and thank you so much for watching! I will try my best to keep on delivering good content : )
Thanks Mo. You are great - explained in very simple manner easy to understand for all.🙏
Excellent way of delivery .
Awesome tutorial. This is a mighty impact.
Great tutorial thank you!
Brilliant Mo👍
❤thanks for your sharing this video. It's valuable to me 😊😊
Thank you so much ❤
Perfect explenation!
Glad it was helpful!
Thank you Sir
tHANKS A LOT SIR. MAY GOD BLESS YOU AND YOUR FAMILY AND CAREER
Maaaaaan, I had troubles with this exact problem about a week ago, took me ages to figure ir out, most of the explanations were not as easy as this one, i wish i had found this video sooner
this might be a stupid question, but why do you want to lock in (F4) the look up range? What happens thats different if you don't?
I think this is very powerful. However i have question. If the lookup array is not a real value instead a result of vlookup value result from another sheet, can i still use it? I have problem using the formula cause it keeps showinv error #N/A message. Thank u
Hey Mo. Thank you for sharing this informative video. I have been using MS Excel for quite some time for my regular office stuff, and now want to learn it as a data analyst. I googled for the courses but ended up confused that which one to choose. I would appreciate if you could share a link to the course to learn MS EXCEL in detail. Thanks
Hi Mubeen, Udemy has some great courses like this one www.udemy.com/course/microsoft-excel-2013-from-beginner-to-advanced-and-beyond/ or this one www.udemy.com/course/excel-for-analysts/. You can't go wrong with either I think, but make sure you have a browse yourself as well. Thanks a lot for watching!
Thank you so much for this tutorial! please can I get the dataset you used!
I actually don't know where I saved this dataset, sorry 😅
Hi Mojo, Excellent tutorial. Please can I get the dataset you used?
I'm glad you liked it! The link to the dataset is in the description 😁
I'm confused about your VLOOKUP formula example which refers back to the range in the orders table. It looks like you are hard-coding the order table range, but couldn't the order table add new rows by the user as new orders get recorded? Can you set the range to include the current selection plus any future additional rows that get created?
How do you turn on and off that focus highlight bubble that darkens everything but where you put your cursor? That is very useful!
I have an extra app on Windows called Microsoft Power Toy. It has several great features. I often use the function to have always on top one window. It also has this function, I guess basicly is double hit ctrl.
Thank for this video. Can you help me create an excel calculator? I will send you a video of what i need. It's icm911 where to go yh for now.
Hello Mo, I recall a video of yours showing a link to an Excel cheatsheet available for purchase.
I couldnt find it now, may I seek for assistance to point me in a right direction? Been crawling through your videos and website but couldnt find it now.
It's part of the many bonuses for my Ultimate Data Portfolio now :) mochen.info/ultimate-data-portfolio-lp/
Xlookup is so much easier than Index Match.
To join I should pay $4/monthly, what do you provide in those classes?
I need it for inventory analysis
Xlookup😍
What a powerful function indeed!!
@@mo-chen 🤗
Keep it up! How about leveraging ChatGPT in excel to do data analysis
@@JackChen1219 Great video idea, might be doing a video on it soon!