I just broke my head today and wasted almost more than four hours struggling with two dimensional vlookup...had huge data exactly u shown....😭 Thank you for sharing this knowledge....
Hahaha, i remember i first saw this combined formula from an Insead Alumni... but after years in excel, many other elegant formulas helped me a lot as well.
Great video! One tip that I like doing is creating named ranges when using Index Match. Makes it much easier for anyone not working in the spreadsheet consistently to understand what's going on.
You can do this way faster. Sort the stores 1 to store n and then take the FC copy and then paste special and choose multiply. Done. Takes three seconds. But I get your point :)
0:40 index-match formula is where distinguish from beginner and more senior cohort. 2:10 Vlookup is more beginner than the index-match. consultant is professional excel user. 4:10 break yearly targets to monthly values. 5:20 2-dimension lookup. 6:55 this will not work because the numbers are not dynamic. 8:20 Vlookup here is not recommended and not professional. see other videos about Vlookup, tons there in the internet. 8:35 intuitive explanation 9:35 match formula is doing, for example: pull up the exact row number here is 3 and exact column number is 4. 11:15 field 1,1. 12:35 now here, you want to use match formula. 13:30 match type is 0 as exact match. 13:50 evaluate expression: press F9. and return the relevant position in this array. is the number-th item returning. 17:55 now we check if it's accurate. 18:40 now we can multiply the targets and get the actual target. 19:145 a triple check: with sum. 20:00 it doesn't matter if we add blank columns, it would automatically expand.
I still remember teaching a older consultant how to use vlookup in 2012. It’s was replaced by index Match and now it seems like xlookup has replaced index match too.
I use xlookup when it needs to be fast e.g. in a call and for easier matches (where I know which row or column I need) and then IndexMatch or sumifsIndexMatch when the data is less structured or just too large to find the needed columns by hand. So both formulas are useful
Hi! Yes, do indeed think about creating an Excel course, similar to my existing course on communications / slide writing. However, at the moment struggling to really find the time for that, so will probably still take some time until I complete it. But will let all of you know once it is out! :) Best, H
Xlookup is indeed super, but fails in the matrix-example that Heinrich showed. Then, INDEX-MATCH is still the one. HOWEVER ! It can be simplified using INDEX-XMATCH. I am sure Heinrich knows what I am referring to. The benefit of index-Xmatch is that it grows nicely as more months are included on the right. In a new sheet, I actually start the top-left cell A1 by entering =Array[#Headers], which is where you will find the monts and the data. This spills the columns headers of the array from A1 .. to .. let's say D1. If someone add colums to the array - more months- , then you simply see more colums headers spilled to G1 for example. Ok, next, step. Direcly, underneath =Array[#Headers], in cell A2, I enter the formula =INDEX( Array; XMATCH ( LookupTable[Field_A] ; Array[Field_A] ) ; XMATCH ( A1; Array[#Headers]) ) . This thing grows automatically in both rows (stores) and colums (months). Too bad I cannot paste a screenshot here. Hope you can visualize it.
For huge data power query is so much better, but for smaller things the new xlookup is also ok. Power query takes only 2 hours to get the basics. Before xlookup, index match was indeed better.
Hi Kloe, thanks for your comment! And also sharing your perspective :) Yes, xlookup is great, though unfortunately not downwards compatible, therefore hard to use as you cannot assume that all your clients work on Office 365. Best! Heinrich
Hi Heinrich, thank you for the great video. Would be great if one could download your sample data, so that one has not to come up if their ownes. It removes a litte more friction and you can use your excel templates as marketing material too.
Thank you Heinrich. I tried doing this and followed your formula exactly but it still returned as an error. Is it because of the Excel version that I'm using? I
Hi You, thanks for your comment! Super happy to hear that. Good idea on updating the Excel, will try to find a bit of time to make it available! All the best to you! Heinrich
instead of writing "2" for the return column, you can use the function COLUMN(A:A) So the function will be Vlookup(Cell,The table, COLUMN(A:A),FALSE) You can replace A:A with whatever column you want to return.
Hi Joe, nothing as a general rule. But on project teams, you often make naming conventions in the beginning. Often you have a format like: 20210213_File name_v3 ... this way the files are ordered by their creation date :) Best! Heinrich
Are xlookup, vlookup and indexmatch still useful in this day and age? Why are people not using power query and data models instead? Please enlighten me if I'm wrong.
Don’t know how many times I have lost work due vlookup crashing excel. Especially lately when I have been working on excel for mac.i however been importing data in a sql database and it gives me a lot of freedoms
This is just Ctrl + C / Ctrl + V (copy / paste). I copy-pasted the formula in field C2 to the other fields. You need to make sure all the references are fixed properly (with the $-signs in the formulas) for it to work correctly though. Best! H
Hi Heinrich, if it is possible I would like to ask two questions. Are programming languages such as R and Python used in consulting firms? If so, how popular are they and in which business area are they used? Additionally, if you work for a consulting firm can you have a RUclips channel? Are you contractually obliged to disclose it? Thank you
Hi! Programming languages are sometimes used by backoffice analytics teams, but usually not by the consultants. Regarding social media presence the policies might be very different firm by firm. For sure does not hurt to be open with that! Best, Heinrich
Hi Torben, yes Power Pivot is very powerful for sure. Most people are not able to use it though (therefore hard to collaborate with colleagues) ;) Best! Heinrich
Why not bringing the stores in the correct order first, then just multiply the yearly revenue with the monthly percentage? And then copy this flow to all other cells?
Hi, you usually do not want to rely on elements being in a specific order, especially with models where you need to regularly update some of the data. Though of course in general your approach is possible. Best, H
Yes, PowerQuery is super powerful as well. Though in general you want to implement things in a more basic way if possible, as most people have no idea what PowerQuery is / how it works. Once you then leave the company or hand the model over to someone else, they cannot properly work with it anymore as they do not understand what you did. Best, H
Yes, XLOOKUP is great. Unfortunately it is not downward compatible with older Excel versions. Therefore in consulting not really an option, as you need to make sure your clients can work with your models as well (e.g., my current company still runs on Office 2010). Best, H
@@FirmLearning I see your point, but everyone who has a proper licence with the new office package is moving to compatible versions. An easy way to check this is: Is the client using W10? If yes: XLOOKUP will work, as they would be on the last possible version (TY Microsoft for the "voluntary" updates) If no, use office365 online to deliver the spreadsheet (it is free)
Dude, vlookup//Index match is fucking outdated. Use Xlookup instead of even xmatch function instead. U'll love it. Btw, having more than one / first match is also important.... for this u need the offset function
Yes, though problem with that is that you need Office 365 for it to work. This is a big no-no in consulting, where you need to make sure that clients can work with it too. E.g., my current employer still runs on Office 2010, which even struggles with functions like IFS or SWITCH. Therefore always important to understand how to build models that are backwards-compatible. But agree that xlookup etc. simplifies many things. OFFSET of course very important as well, maybe something for a future video! :) Best, H
@@FirmLearning well, exactly right, i also use Index match extensivly out of that reason. But i havent seen 2010 since 4 years anymore at clients and think that it is fine to let old shit be old shit st some point. But why teach already outdated shit that everyone can google. Offseg with Index match is a beast.
Good for you if you perceive this to be basic! From what I have seen, I would assume that 95% of Excel users in Corporates are not able to do this. Thanks for watching! Best, H
![ROSÉ (로제) - number one girl [더 시즌즈-이영지의 레인보우] | KBS 241129 방송](http://i.ytimg.com/vi/A9oByH9Ci24/mqdefault.jpg)
Do you also want to improve your PowerPoint skills? Here is my course on Slide Writing:
link.firmlearning.com/slides
I just broke my head today and wasted almost more than four hours struggling with two dimensional vlookup...had huge data exactly u shown....😭 Thank you for sharing this knowledge....
Happy to hear this helped! :) Best, Heinrich
Wow we really see the McKinsey background here well structured and super interesting! I have learned a lot thank you Heinrich
Hi, thanks for your comment! Super happy to hear the Excel video was helpful :) All the best to you! Heinrich
The best video on the internet to learn INDEX-MATCH!
Glad you think so! Thanks for watching Brian!
Glad to see I could be a senior consultant in Excel at least.
Hahaha, i remember i first saw this combined formula from an Insead Alumni... but after years in excel, many other elegant formulas helped me a lot as well.
Hi Vincent, thanks for your comment! Trust me that it is not only INSEAD alumns that use INDEX-MATCH though ;) Best! Heinrich
Hi Heinrich, you are right, i meant an MBA graduate and he was an engineer as well. Great vids, thanks. Best!
Great video! One tip that I like doing is creating named ranges when using Index Match. Makes it much easier for anyone not working in the spreadsheet consistently to understand what's going on.
Thanks for sharing! Yes agree that ranges can be helpful. Personally I do not really like working with them, but can see the value. Best! Heinrich
Great video !!! Index Match seems easy after watching your video.. Thanks !
Happy to hear that - thanks Preeti! :) Best, Heinrich
Thank you for your explanation! Your teaching style is excellent!!
Appreciate it, thanks for the feedback Tracey! Best, Heinrich
You can do this way faster. Sort the stores 1 to store n and then take the FC copy and then paste special and choose multiply. Done. Takes three seconds.
But I get your point :)
Yes, though you cannot use these shortcuts in dynamic models / should never rely on the order of elements ;) Best, H
@@FirmLearning agree. I was just kidding.
This video cleared the topic really well. Thanks Heinrich! :)
Great to hear that, thanks Siddharth! :) Best, Heinrich
0:40 index-match formula is where distinguish from beginner and more senior cohort.
2:10 Vlookup is more beginner than the index-match. consultant is professional excel user.
4:10 break yearly targets to monthly values.
5:20 2-dimension lookup.
6:55 this will not work because the numbers are not dynamic.
8:20 Vlookup here is not recommended and not professional. see other videos about Vlookup, tons there in the internet.
8:35 intuitive explanation
9:35 match formula is doing, for example: pull up the exact row number here is 3 and exact column number is 4.
11:15 field 1,1.
12:35 now here, you want to use match formula.
13:30 match type is 0 as exact match.
13:50 evaluate expression: press F9. and return the relevant position in this array. is the number-th item returning.
17:55 now we check if it's accurate.
18:40 now we can multiply the targets and get the actual target.
19:145 a triple check: with sum.
20:00 it doesn't matter if we add blank columns, it would automatically expand.
I still remember teaching a older consultant how to use vlookup in 2012. It’s was replaced by index Match and now it seems like xlookup has replaced index match too.
Times are changing indeed :) Thank you for watching! Best, Heinrich
I use xlookup when it needs to be fast e.g. in a call and for easier matches (where I know which row or column I need) and then IndexMatch or sumifsIndexMatch when the data is less structured or just too large to find the needed columns by hand. So both formulas are useful
Thank you for this video Heinrich! This was very helpful!
Great to hear that - thanks for watching Kushagra! Best, Heinrich
Hi, Heinrich! Do you plan to develope complete Excel course to share other must have Excel futures for MBB consultants?
Hi! Yes, do indeed think about creating an Excel course, similar to my existing course on communications / slide writing. However, at the moment struggling to really find the time for that, so will probably still take some time until I complete it. But will let all of you know once it is out! :) Best, H
Xlookup has completely replaced index match and vlookup for me at this point
Yes it for sure is a great new function! Best, Heinrich
Xlookup is indeed super, but fails in the matrix-example that Heinrich showed. Then, INDEX-MATCH is still the one. HOWEVER ! It can be simplified using INDEX-XMATCH. I am sure Heinrich knows what I am referring to. The benefit of index-Xmatch is that it grows nicely as more months are included on the right. In a new sheet, I actually start the top-left cell A1 by entering =Array[#Headers], which is where you will find the monts and the data. This spills the columns headers of the array from A1 .. to .. let's say D1. If someone add colums to the array - more months- , then you simply see more colums headers spilled to G1 for example. Ok, next, step. Direcly, underneath =Array[#Headers], in cell A2, I enter the formula =INDEX( Array; XMATCH ( LookupTable[Field_A] ; Array[Field_A] ) ; XMATCH ( A1; Array[#Headers]) ) . This thing grows automatically in both rows (stores) and colums (months). Too bad I cannot paste a screenshot here. Hope you can visualize it.
Batman (Xlookup): doing the slap.
Robin (Index & Match). receiving the slap.
Great video, looking forward to seeing more!
Thanks! Yes, will continue creating Excel-based videos in the future :) Best, Heinrich
For huge data power query is so much better, but for smaller things the new xlookup is also ok. Power query takes only 2 hours to get the basics. Before xlookup, index match was indeed better.
Hi Kloe, thanks for your comment! And also sharing your perspective :) Yes, xlookup is great, though unfortunately not downwards compatible, therefore hard to use as you cannot assume that all your clients work on Office 365. Best! Heinrich
@@FirmLearning Yes, if you plan on sharing it outside of your organisation, at the moment, it is not a good option. 😅
I know so many consultants who prefer v or hlookup and it drives me nuts. Not only is index-match more versatile, its much easier to follow its logic.
Can see where you are coming from :) Thanks for watching! Best, H
Great video, I was just wondering if its possible to get the excel data? Would be great!
Hi Heinrich, thank you for the great video.
Would be great if one could download your sample data, so that one has not to come up if their ownes. It removes
a litte more friction and you can use your excel templates as marketing material too.
Hi, thanks for watching and the suggestion! Will think about how I can set up a more streamlined offer for Excel skills in the future. Best, Heinrich
Thanks. Very elegant style of explanation.
Thanks!! :) Best, H
Yes this feature is very useful!
Appreciate it :) Best, Heinrich
liked, subcribed, sharing with my friends . SOO GOOD!!
Thank you Sarah, much appreciated!! :) Best, Heinrich
Thank you Heinrich. I tried doing this and followed your formula exactly but it still returned as an error. Is it because of the Excel version that I'm using? I
Awesome tips and walk through
Thank you Kelvin! Best, Heinrich
Great video. This is a great tip.
Thanks for watching!! Best, Heinrich
Hello, thanks for sharing this video! Is it possible to share the finished excel with us? So we can practice. :)
Hi You, thanks for your comment! Super happy to hear that. Good idea on updating the Excel, will try to find a bit of time to make it available! All the best to you! Heinrich
This was the only valuable skill I learned from consulting 😂
Cannot value Excel skills enough ;) Best, H
It was very interesting!! Thanks for sharing with us. Where can I learn about excel applied to consulting? Thanks!
Hi Ricardo, have a playlist with Excel content on the channel, suggest you check it out :) Best! Heinrich
Very useful and clear explaination
Thanks Rajesh - appreciate your time watching! Best, Heinrich
instead of writing "2" for the return column, you can use the function COLUMN(A:A)
So the function will be
Vlookup(Cell,The table, COLUMN(A:A),FALSE)
You can replace A:A with whatever column you want to return.
Good suggestion, thanks! Best, H
You can then just drag the vlookup formula as you can make the column dynamic.
Thank you for this. This was awesome
Thanks happy to hear!! Best, H
Thank you so much for this- This is the best!
Very glad it is helpful! Best, Heinrich
Do you have on Mckinsey a rule how to describe a excel files properly. Like Sales_Revenues_31_12_2020 ?
Hi Joe, nothing as a general rule. But on project teams, you often make naming conventions in the beginning. Often you have a format like: 20210213_File name_v3 ... this way the files are ordered by their creation date :) Best! Heinrich
thank you very much😊
Thank YOU for watching and the comment, appreciate it! Best, Heinrich
Are xlookup, vlookup and indexmatch still useful in this day and age? Why are people not using power query and data models instead? Please enlighten me if I'm wrong.
Don’t know how many times I have lost work due vlookup crashing excel. Especially lately when I have been working on excel for mac.i however been importing data in a sql database and it gives me a lot of freedoms
Thanks for sharing! Yes, vlookup performance can be bad. Best! Heinrich
Please find an alternative screen capture tool, so that the Excel "ribbon" (toolbar) is visible in your videos.
Use index+match!
Exactly! ;) Best, H
Anyone knows what is the shortcut used at 17:44 to paste all the formulas?
This is just Ctrl + C / Ctrl + V (copy / paste). I copy-pasted the formula in field C2 to the other fields. You need to make sure all the references are fixed properly (with the $-signs in the formulas) for it to work correctly though. Best! H
I really don’t Unterstands what’s so hard on Index match. It’s just important to understand how it works, than it’s very easy
As with most things in life, once you get it, it is easy! :)
Very useful! Thanks a lot! 👍
Thanks, appreciate it! Best, H
Can you do an Index Match vs XLookUp video?
Hi Farooq thanks for the suggestion, might do that in the future. Thanks for watching! Best, Heinrich
Your content is so valuable! Looking forward for future tutorials
Thank you man, appreciate it! Yes, stay tuned for future videos on this topic :) Best, Heinrich
Great video
Thanks!! Best, Heinrich
Thanks!
Danke Heinrich!
Danke fürs Zuschauen! LG, H
I love your accent!
Thanks Dan :) Best, Heinrich
Hi Heinrich, if it is possible I would like to ask two questions. Are programming languages such as R and Python used in consulting firms? If so, how popular are they and in which business area are they used? Additionally, if you work for a consulting firm can you have a RUclips channel? Are you contractually obliged to disclose it? Thank you
Hi! Programming languages are sometimes used by backoffice analytics teams, but usually not by the consultants. Regarding social media presence the policies might be very different firm by firm. For sure does not hurt to be open with that! Best, Heinrich
@@FirmLearning thank you for your reply. Really appreciated!
starts from 3:50. u are welcome
Thanks for watching! Best, H
@@FirmLearning ahahha
Enter Data model. I rarely use vlookups anymore because it's slow
Hi Kriti, thank you for sharing! Best, Heinrich
xlookup solves most of the vlookup issues and is way easier than index match
Thanks very much for your input! :)
Power pivot & relationships replaces vlookup and index match.
Hi Torben, yes Power Pivot is very powerful for sure. Most people are not able to use it though (therefore hard to collaborate with colleagues) ;) Best! Heinrich
Why not bringing the stores in the correct order first, then just multiply the yearly revenue with the monthly percentage?
And then copy this flow to all other cells?
Hi, you usually do not want to rely on elements being in a specific order, especially with models where you need to regularly update some of the data. Though of course in general your approach is possible. Best, H
This video started with memes, *smashes the like button*
Love it, thank you Lexi!! :) Best, Heinrich
I need that Excel file?.!
Thank you very much for watching! I will consider uploading links to download the files in future!
Watching this - not sure why they don't teach Index Match first as it's really how your mind would work.
Can you recommend some consulting memes sites?
Try @crazymgmtconsultants :) Or watch last weeks video of mine!! Best, Heinrich
Why you do not use PowerQuery?
Yes, PowerQuery is super powerful as well. Though in general you want to implement things in a more basic way if possible, as most people have no idea what PowerQuery is / how it works. Once you then leave the company or hand the model over to someone else, they cannot properly work with it anymore as they do not understand what you did. Best, H
Thanks 😊
Thank YOU for watching! Best, Heinrich
Use XLOOKUP and FILTER.
Yes great functions, though unfortunately not downwards compatible. Best! Heinrich
Cool.
Thanks for watching!! Best, H
How to use index match instead of vlookup for one dimensional
You can fix one dimension by entering a fixed / non-dynamic value for the row or column input. Best! H
XLOOKUP. You're welcome.
Great function! Though unfortunately not backwards-compatible. Best, H
ಧನ್ಯವಾದಗಳು
Thanks for watching! Best, H
@@FirmLearning very helpful
Biggest surprise of this video: How can you draw in excel?
Use XLOOKUP!!!
Yes, XLOOKUP is great. Unfortunately it is not downward compatible with older Excel versions. Therefore in consulting not really an option, as you need to make sure your clients can work with your models as well (e.g., my current company still runs on Office 2010). Best, H
@@FirmLearning I see your point, but everyone who has a proper licence with the new office package is moving to compatible versions.
An easy way to check this is:
Is the client using W10?
If yes: XLOOKUP will work, as they would be on the last possible version (TY Microsoft for the "voluntary" updates)
If no, use office365 online to deliver the spreadsheet (it is free)
Its not faster than vlookup and more complex, i dont see the benefit
Index-Match is supposed to be faster than vlookup regarding processing power. Best, H
XLOOKUP > Index Match
Yes XLOOKUP is great! (though not downwards compatible) Best, H
Warum holen sich Leute eigentlich BeraterInnen ins Haus, wenn diese wohl nichtmal die elementarsten Excel-Funktionen beherrschen? 🤔
Beratung ist mehr als nur Excel ;) Danke fürs Zuschauen! LG, H
Dude, vlookup//Index match is fucking outdated. Use Xlookup instead of even xmatch function instead. U'll love it. Btw, having more than one / first match is also important.... for this u need the offset function
Yes, though problem with that is that you need Office 365 for it to work. This is a big no-no in consulting, where you need to make sure that clients can work with it too. E.g., my current employer still runs on Office 2010, which even struggles with functions like IFS or SWITCH. Therefore always important to understand how to build models that are backwards-compatible. But agree that xlookup etc. simplifies many things. OFFSET of course very important as well, maybe something for a future video! :) Best, H
@@FirmLearning well, exactly right, i also use Index match extensivly out of that reason. But i havent seen 2010 since 4 years anymore at clients and think that it is fine to let old shit be old shit st some point. But why teach already outdated shit that everyone can google. Offseg with Index match is a beast.
RIP VLOOKUP
XLOOKUP for the future!! ;)
yes, but you did not properly iron your shirt. sincerely
Thanks for the feedback, many things I need to continue to work on ;) Best! Heinrich
@@FirmLearning just kidding btw, and you always have to leave to the others some easy cherry picks too
Surely this whole index match being advanced is a joke... Right???
Good for you if you perceive this to be basic! From what I have seen, I would assume that 95% of Excel users in Corporates are not able to do this. Thanks for watching! Best, H
@@FirmLearning Wow! Feeling more grateful now for taking that optional financial modelling course at university.
SumProduct is better
Yes sumproduct is great for sure. Best! H
3:23
Thanks for watching! Best, H
ummm xlookup...
Yes XLOOKUP is great! Problem is, it is not downwards compatible :) Best! Heinrich
3:37