Data Analyst Explains When to Use VLOOKUP vs XLOOKUP vs INDEX MATCH

Рет қаралды 47,086

Күн бұрын

┋Resources to take your data analytics skills to the next level┋
Ultimate Data Roadmap: Learn applicable data skills that you can actually use in your workplace in 30 days ➡ mochen.info/ultimate-data-roa...
Ultimate Data Portfolio: Learn how to create an outstanding data portfolio in 3 hours ➡ mochen.info/ultimate-data-por...
1:1 Mentorship: Learn how to advance your career ➡ mochen.info/1-1-mentorship-lp/
Analyst Fundamentals Test: Find out your score in 10 minutes ➡ mochen.info
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 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
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

Пікірлер: 46

@x17aerialconsultancy51 11 ай бұрын

Wanted to know these for years. Took 20 mins and now I know. Thank you Mo!!!

@mo-chen 11 ай бұрын

I'm so glad you liked the video! Thanks so much for watching 😁

@ais89x 6 ай бұрын

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

@kenm6226 Ай бұрын

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.

@ais89x Ай бұрын

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

@Fasterwarrior 25 күн бұрын

I think in both cases you should select an entire table for table array step

@user-ff3bb6kb2o Жыл бұрын

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.

@mo-chen Жыл бұрын

Hey Al 👋 I'm very glad my explanation was easy to understand! Thanks so much for watching 😄

@RigorFortis 18 күн бұрын

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.

@carltonshank9797 11 ай бұрын

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.

@mo-chen 11 ай бұрын

That's very kind of you to say, thank you 😄

@patricklacey4946 3 ай бұрын

Thumbs up for that "F4" callout, I was struggling to fix my fill option and you saved me! Excellent video.

@rubymenonbrainsmartdesign3105 3 ай бұрын

Excellent video! You clearly explain each function with the pros, cons, and use cases for each. Well done! I learned a lot. Thank you!

@paulang8267 Ай бұрын

You, real power, amazed! Thanks

@dnyandeosomawanshi9271 Ай бұрын

Thanks Mo. You are great - explained in very simple manner easy to understand for all.🙏

@tasweerhussain6409 2 ай бұрын

Excellent way of delivery .

@lamishelall1059 Жыл бұрын

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 😍

@mo-chen Жыл бұрын

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 : )

@protocolwonder4558 Ай бұрын

Awesome tutorial. This is a mighty impact.

@kroston123 Ай бұрын

Great tutorial thank you!

@user-sn8nb7zw8i Ай бұрын

Thank you so much ❤

@gregglloyd3036 4 ай бұрын

Brilliant Mo👍

@Prakhao5684 2 ай бұрын

❤thanks for your sharing this video. It's valuable to me 😊😊

@kumartoley2637 2 ай бұрын

Thank you Sir

@chicoamorim429 4 ай бұрын

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

@John-kd1bf 6 күн бұрын

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?

@krista95856 Ай бұрын

How do you turn on and off that focus highlight bubble that darkens everything but where you put your cursor? That is very useful!

@rajmohannarayanan5935 3 ай бұрын

tHANKS A LOT SIR. MAY GOD BLESS YOU AND YOUR FAMILY AND CAREER

@safazeroual424 8 ай бұрын

Thank you so much for this tutorial! please can I get the dataset you used!

@mo-chen 8 ай бұрын

I actually don't know where I saved this dataset, sorry 😅

@trayjunie12 2 ай бұрын

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?

@mubeenahmad5566 Жыл бұрын

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

@mo-chen Жыл бұрын

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!

@megapuspitap 4 ай бұрын

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