Key Consulting Excel Skills - How to use Index Match in Excel for Consultants

[@FirmLearning]

Do you also want to improve your PowerPoint skills? Here is my course on Slide Writing: link.firmlearning.com/slides

[@shiprasinni1064]

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....

[@FirmLearning]

Happy to hear this helped! :) Best, Heinrich

[@FCF27]

Wow we really see the McKinsey background here well structured and super interesting! I have learned a lot thank you Heinrich

[@FirmLearning]

Hi, thanks for your comment! Super happy to hear the Excel video was helpful :) All the best to you! Heinrich

[@StockSpotlightPodcast]

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.

[@FirmLearning]

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

[@briankane9900]

The best video on the internet to learn INDEX-MATCH!

[@FirmLearning]

Glad you think so! Thanks for watching Brian!

[@Vinz_Larr358]

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.

[@FirmLearning]

Hi Vincent, thanks for your comment! Trust me that it is not only INSEAD alumns that use INDEX-MATCH though ;) Best! Heinrich

[@Vinz_Larr358]

Hi Heinrich, you are right, i meant an MBA graduate and he was an engineer as well. Great vids, thanks. Best!

[@traceybracke3844]

Thank you for your explanation! Your teaching style is excellent!!

[@FirmLearning]

Appreciate it, thanks for the feedback Tracey! Best, Heinrich

[@judyl.7811]

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.

[@artzuh]

Glad to see I could be a senior consultant in Excel at least.

[@huskidusk]

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

[@FirmLearning]

Yes, though you cannot use these shortcuts in dynamic models / should never rely on the order of elements ;) Best, H

[@huskidusk]

@@FirmLearning agree. I was just kidding.

[@siddharthmajhi1]

This video cleared the topic really well. Thanks Heinrich! :)

[@FirmLearning]

Great to hear that, thanks Siddharth! :) Best, Heinrich

[@preetipimpalnerkar2910]

Great video !!! Index Match seems easy after watching your video.. Thanks !

[@FirmLearning]

Happy to hear that - thanks Preeti! :) Best, Heinrich

[@kushagrasharma4853]

Thank you for this video Heinrich! This was very helpful!

[@FirmLearning]

Great to hear that - thanks for watching Kushagra! Best, Heinrich

[@vyacheslavpolnikov460]

Hi, Heinrich! Do you plan to develope complete Excel course to share other must have Excel futures for MBB consultants?

[@FirmLearning]

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

[@voicification]

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.

[@FirmLearning]

Times are changing indeed :) Thank you for watching! Best, Heinrich

[@j.s.6080]

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

[@simonwanner211]

Great video, I was just wondering if its possible to get the excel data? Would be great!

[@EstrellaJian]

Great video, looking forward to seeing more!

[@FirmLearning]

Thanks! Yes, will continue creating Excel-based videos in the future :) Best, Heinrich

[@kloekegels3345]

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.

[@FirmLearning]

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

[@kloekegels3345]

@@FirmLearning Yes, if you plan on sharing it outside of your organisation, at the moment, it is not a good option. 😅

[@iangwaltney2316]

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.

[@FirmLearning]

Can see where you are coming from :) Thanks for watching! Best, H

[@ricardo.juarezp]

It was very interesting!! Thanks for sharing with us. Where can I learn about excel applied to consulting? Thanks!

[@FirmLearning]

Hi Ricardo, have a playlist with Excel content on the channel, suggest you check it out :) Best! Heinrich

[@jper1245]

Xlookup has completely replaced index match and vlookup for me at this point

[@FirmLearning]

Yes it for sure is a great new function! Best, Heinrich

[@steveshoulders]

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.

[@spilledgraphics]

Batman (Xlookup): doing the slap. Robin (Index & Match). receiving the slap.

[@chengdesiree]

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

[@GrowWithWill]

This was the only valuable skill I learned from consulting 😂

[@FirmLearning]

Cannot value Excel skills enough ;) Best, H

[@jbond5834]

starts from 3:50. u are welcome

[@FirmLearning]

Thanks for watching! Best, H

[@jbond5834]

@@FirmLearning ahahha

[@TheMarinho1]

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.

[@FirmLearning]

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

[@nishankbani3257]

Thanks. Very elegant style of explanation.

[@FirmLearning]

Thanks!! :) Best, H

[@youzhou5116]

Hello, thanks for sharing this video! Is it possible to share the finished excel with us? So we can practice. :)

[@FirmLearning]

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

[@jaypatankar]

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.

[@sarahlee6976]

liked, subcribed, sharing with my friends . SOO GOOD!!

[@FirmLearning]

Thank you Sarah, much appreciated!! :) Best, Heinrich

[@superfreiheit1]

Do you have on Mckinsey a rule how to describe a excel files properly. Like Sales_Revenues_31_12_2020 ?

[@FirmLearning]

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

[@donakarunaratne6012]

Thank you so much for this- This is the best!

[@FirmLearning]

Very glad it is helpful! Best, Heinrich

[@aamarkhan7448]

Yes this feature is very useful!

[@FirmLearning]

Appreciate it :) Best, Heinrich

[@kohkoh17]

Awesome tips and walk through

[@FirmLearning]

Thank you Kelvin! Best, Heinrich

[@vigneshmuthukrishnan9468]

Thank you for this. This was awesome

[@FirmLearning]

Thanks happy to hear!! Best, H

[@SVanDykTX]

Great video. This is a great tip.

[@FirmLearning]

Thanks for watching!! Best, Heinrich

[@NguyenTran-im9ou]

thank you very much😊

[@FirmLearning]

Thank YOU for watching and the comment, appreciate it! Best, Heinrich

[@ME911119]

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.

[@FirmLearning]

Good suggestion, thanks! Best, H

[@g19anson]

You can then just drag the vlookup formula as you can make the column dynamic.

[@labtimeRP]

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 KZbin channel? Are you contractually obliged to disclose it? Thank you

[@FirmLearning]

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

[@labtimeRP]

@@FirmLearning thank you for your reply. Really appreciated!

[@RP1D2]

Very useful and clear explaination

[@FirmLearning]

Thanks Rajesh - appreciate your time watching! Best, Heinrich

[@farooqawad5443]

Can you do an Index Match vs XLookUp video?

[@FirmLearning]

Hi Farooq thanks for the suggestion, might do that in the future. Thanks for watching! Best, Heinrich

[@michael28218]

Use index+match!

[@FirmLearning]

Exactly! ;) Best, H

[@_truthart]

Please find an alternative screen capture tool, so that the Excel "ribbon" (toolbar) is visible in your videos.

[@yeobaoshin]

Anyone knows what is the shortcut used at 17:44 to paste all the formulas?

[@FirmLearning]

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

[@XCMcard]

Your content is so valuable! Looking forward for future tutorials

[@FirmLearning]

Thank you man, appreciate it! Yes, stay tuned for future videos on this topic :) Best, Heinrich

[@Nonalcoholicdrink]

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

[@FirmLearning]

As with most things in life, once you get it, it is easy! :)

[@Gehtdichgarnichtsan805]

Very useful! Thanks a lot! 👍

[@FirmLearning]

Thanks, appreciate it! Best, H

[@slipptube]

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

[@FirmLearning]

Thanks for sharing! Yes, vlookup performance can be bad. Best! Heinrich

[@selvd004]

Great video

[@FirmLearning]

Thanks!! Best, Heinrich

[@saxet812004]

Thanks!

[@dannytetreault]

I love your accent!

[@FirmLearning]

Thanks Dan :) Best, Heinrich

[@saimahesh9115]

Danke Heinrich!

[@FirmLearning]

Danke fürs Zuschauen! LG, H

[@manfredullrich483]

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?

[@FirmLearning]

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

[@AngloAm]

Watching this - not sure why they don't teach Index Match first as it's really how your mind would work.

[@soren884]

Can you recommend some consulting memes sites?

[@FirmLearning]

Try @crazymgmtconsultants :) Or watch last weeks video of mine!! Best, Heinrich

[@SuperMsmystery]

Enter Data model. I rarely use vlookups anymore because it's slow

[@FirmLearning]

Hi Kriti, thank you for sharing! Best, Heinrich

[@superfreiheit1]

Why you do not use PowerQuery?

[@FirmLearning]

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

[@arslan_TM]

I need that Excel file?.!

[@FirmLearning]

Thank you very much for watching! I will consider uploading links to download the files in future!

[@torbenfuessle3204]

Power pivot & relationships replaces vlookup and index match.

[@FirmLearning]

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

[@jediyoda7338]

Thanks 😊

[@FirmLearning]

Thank YOU for watching! Best, Heinrich

[@lexigreenwarwell1004]

This video started with memes, *smashes the like button*

[@FirmLearning]

Love it, thank you Lexi!! :) Best, Heinrich

[@paulshurov9296]

xlookup solves most of the vlookup issues and is way easier than index match

[@FirmLearning]

Thanks very much for your input! :)

[@User-xw4dt]

Use XLOOKUP and FILTER.

[@FirmLearning]

Yes great functions, though unfortunately not downwards compatible. Best! Heinrich

[@matteosposato9448]

Cool.

[@FirmLearning]

Thanks for watching!! Best, H

[@sabeshrajan6443]

How to use index match instead of vlookup for one dimensional

[@FirmLearning]

You can fix one dimension by entering a fixed / non-dynamic value for the row or column input. Best! H

[@coolfix948]

ಧನ್ಯವಾದಗಳು

[@FirmLearning]

Thanks for watching! Best, H

[@coolfix948]

@@FirmLearning very helpful

[@billyoung8118]

XLOOKUP. You're welcome.

[@FirmLearning]

Great function! Though unfortunately not backwards-compatible. Best, H

[@fujanu]

Biggest surprise of this video: How can you draw in excel?

[@cheneunwien4259]

Use XLOOKUP!!!

[@FirmLearning]

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

[@sbIvanov]

​@@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)

[@arvinfantoan929]

Its not faster than vlookup and more complex, i dont see the benefit

[@FirmLearning]

Index-Match is supposed to be faster than vlookup regarding processing power. Best, H

[@TheStenius]

Warum holen sich Leute eigentlich BeraterInnen ins Haus, wenn diese wohl nichtmal die elementarsten Excel-Funktionen beherrschen? 🤔

[@FirmLearning]

Beratung ist mehr als nur Excel ;) Danke fürs Zuschauen! LG, H

[@sulahu]

3:23

[@FirmLearning]

Thanks for watching! Best, H

[@tarpara]

XLOOKUP > Index Match

[@FirmLearning]

Yes XLOOKUP is great! (though not downwards compatible) Best, H

[@andreacazzaniga8488]

yes, but you did not properly iron your shirt. sincerely

[@FirmLearning]

Thanks for the feedback, many things I need to continue to work on ;) Best! Heinrich

[@andreacazzaniga8488]

@@FirmLearning just kidding btw, and you always have to leave to the others some easy cherry picks too

[@v3r0x]

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

[@FirmLearning]

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

[@v3r0x]

@@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.

[@xorolow1]

RIP VLOOKUP

[@FirmLearning]

XLOOKUP for the future!! ;)

[@darrylbaker5271]

SumProduct is better

[@FirmLearning]

Yes sumproduct is great for sure. Best! H

[@sayinmaster111]

Surely this whole index match being advanced is a joke... Right???

[@FirmLearning]

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

[@sayinmaster111]

@@FirmLearning Wow! Feeling more grateful now for taking that optional financial modelling course at university.

[@edwardfranklin4835]

ummm xlookup...

[@FirmLearning]

Yes XLOOKUP is great! Problem is, it is not downwards compatible :) Best! Heinrich

[@jjeane2218]

3:37