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
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.
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.
Question: While Index Match does seem more complex than Xlookup, it seems tahts once you input the Index Match formula, you can get all the table values in one shot vs having to use separate Xlookup values for each column? So either you do the work up front with one formula, or you do the simpler formula but have to do it multiple times... Is my logic correct here? great video btw!
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!
I have 2 separate workbooks, Same ID#'s on both but in different order, (some are missing on one SS). I have a phone number associated with the ID# on one sheet and I want to add that phone number in a column associated to the ID# on the other SS. What is the best method to do this? Do you have a video that explain this process. Hope this makes sense :-) Thanks for your help!
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
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?
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.
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.
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
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 😁
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
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 😄
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 😄
Thumbs up for that "F4" callout, I was struggling to fix my fill option and you saved me! Excellent video.
Excellent video! You clearly explain each function with the pros, cons, and use cases for each. Well done! I learned a lot. Thank you!
you explained it as an art. My interest in learning excel increased 2x. thank you Mo.🤩
Great Explanation . To the point & most effective . Thanks Chen
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 : )
You explained it very well. Thank you so much!!!
Glad it was helpful!
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.
Wow amazing! All in one an interesting .
Excellent way of delivery .
You, real power, amazed! Thanks
Thanks Mo. You are great - explained in very simple manner easy to understand for all.🙏
Question:
While Index Match does seem more complex than Xlookup, it seems tahts once you input the Index Match formula, you can get all the table values in one shot vs having to use separate Xlookup values for each column? So either you do the work up front with one formula, or you do the simpler formula but have to do it multiple times...
Is my logic correct here?
great video btw!
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?
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!
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 😁
Awesome tutorial. This is a mighty impact.
I have 2 separate workbooks, Same ID#'s on both but in different order, (some are missing on one SS). I have a phone number associated with the ID# on one sheet and I want to add that phone number in a column associated to the ID# on the other SS. What is the best method to do this? Do you have a video that explain this process. Hope this makes sense :-) Thanks for your help!
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 😅
❤thanks for your sharing this video. It's valuable to me 😊😊
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
tHANKS A LOT SIR. MAY GOD BLESS YOU AND YOUR FAMILY AND CAREER
Thank you so much ❤
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?
Brilliant Mo👍
Can I get a link to download that table?
Perfect explenation!
Glad it was helpful!
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.
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/
Great tutorial thank you!
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
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.
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!
Thank you Sir
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