Index Match Advanced: 3 Most Effective Formulas for Multiple Criteria

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
This INDEX MATCH Advanced video shows you how you can use Index & Match to lookup multiple criteria. For example when you have more than one header in a column or more than one row header - i.e. you don't have unique fields to match. You might be tempted to do multiple index match formulas inside one another, or use an IF function here. The solution is actually quite simple.
⬇️ Download the workbook here: pages.xelplus.com/index-match...
★ Complete Excel Dashboard course: www.xelplus.com/course/excel-...
🔑 Key Concepts Covered:
▪️ Understanding the basics of INDEX & MATCH for single criteria.
▪️ Expanding to multiple criteria lookups with examples and explanations.
▪️ Methods to handle data in separate columns for actual and budget values.
▪️ Techniques for creating reports by selecting between actual/budget and profit/revenue.
▪️ Three distinct methods for solving multi-criteria lookups:
Utilizing helper columns for a simple approach.
Array solutions with Ctrl + Shift + Enter (CSE) for a more direct method.
Array solutions without CSE for a balance between simplicity and advanced functionality.
► How to do Index Match in Excel: To learn the basics behind Excel's Index and Match, watch this video: • How to use Excel Index...
00:00 Looking Up MULTIPLE Criteria with INDEX & MATCH
04:21 Looking Up Multiple Criteria (Array Solution with CSE)
07:51 Looking Up Multiple Criteria (Array Solution without CSE)
In the video I show you 3 different ways to write the INDEX MATCH formula in Excel. One method uses helper cells to create unique keys for the match function to find.
Method 2, uses array formulas with CSE (Control Shift Enter) to convert the MATCH function to an array function.
Method 3, avoids CSE and also avoids using helper cells in Excel. It does this by using a second INDEX formula inside the match function (INDEX does not require Control Shift Enter).
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/index-match-advanced-file

[@bobgreenfield9158]

I am watching on my phone. I have trouble seeing the equations.

[@excelisfun]

Awesome INDEX video!!! It a great solution that so many business people encounter!

[@LeilaGharani]

Thanks Mike. Very true! I get this question all the time....

[@odeseusX]

Leila & Mike, discovering both your channels has been an absolute revelation for me!

[@marvinreyes7459]

You Do Some Awesome explanations too Man and I love how you speak :D

[@livinindubai]

2 of my favorite excel gurus 😃

[@vishugupta250]

Two genius at one place... 😘😘

[@CEOako]

This is the reason why I loved index match function.

[@Waydisturbed]

The Excel Goddess! No matter what crazy scenario in which I find myself, Leila Gharani has the answer!

[@LeilaGharani]

😘

[@GordonWafimbi]

That extra index within the match to avoid CSE is genius, thank you,... Really learning from you channel! Keep it up.

[@whimpypatrol5503]

That extra index within the match to avoid CSE is apparently no longer necessary with EXCEL 360 since C20:F20&C21:F21 = INDEX(C20:F20&C21:F21,0)

[@matthewrichmond1542]

I finally get it!! After all these years getting frustrated with lookups this is going to be so helpful. Thank you for your amazing videos

[@LeilaGharani]

YES! YES! I'm very happy to hear that :)

[@Ofageover50]

These are the best tutorials I've found anywhere!!!

[@rahulbakshi285]

You are great Leila... Awesome I'm a big fan of your teachings...

[@dhavalshah2686]

Queen of Excel, Leila Thank you for this video

[@UlyssesHaq]

My mind is blown away by the beauty and simplicity of these insanely complex formulas. Your method is very helpful. Thank you!

[@stephen3149]

Great teaching style! Wonderful format with great examples You break formula's down and explain them step by step. Well done thanks. Great learning video, series thanks

[@LeilaGharani]

You're very welcome Stephen. Glad to hear that.

[@arnavgupta5386]

Index match..... Crystal clear 😊 Thanks!!!!

[@philipmcdonnell7168]

Two minutes in and I had the answer to the question that had been bugging me for hours! I had transposed the Index and Match ranges so it was returning and N/A. So clear. Thanks. 👍🏻😎

[@deepakkanade5041]

I depended heavily on helper cells till now, but no more now. Thank you, Leila. Your every video is a treat to Excel lovers and learners.

[@rafakiyo]

Impressive how Leila saved my life so many times!!! hehehe Everytime when I have a problem I will check if my Guardian angel can help me.... most of the times she can! 😇🥰 Thank you!!

[@ArshadAli07.kapilvastu]

Your clarity in excel is amazing. The way you have explained it here is unmatchable. Keep it up.

[@thekingofkush4447]

Thank you for making this quick and easy. This video was so good, I wish I found this years ago! YOU are good at explaining the logic and steps. You've got a new excel formula nerd fan!

[@nohaynombrelibreeeee]

Leila, you saved me for the second time. Thank you!!!! ❤️ I am forwarding your videos to all my friends and colleagues. The world should now how great you are!

[@LeilaGharani]

You're very welcome. Thanks for your support and the kind words!

[@mannydeguzman5373]

You miss, have saved me from my 5 hour agony from figuring this out. Turns out I was doing columns THEN rows instead of rows THEN columns. Thank you very much!

[@markchristmas4465]

Thank you for this helpful video! The way you explain excel is so much easier to comprehend than other instructors.

[@johnadair6242]

This is fabulous! Thank you so very much.

[@mark91345]

I followed step-by-step and it works... and I understand the thinking behind it (that matters a lot!).

[@LeilaGharani]

So true! I'm glad the video was helpful.

[@hattemghanoom9557]

Thank you Leila for another example of a very smart but yet easy to understand and implement video.

[@LeilaGharani]

Thank you for watching Hattem. Glad you enjoy the videos :)

[@kbalaji9516]

super.. am going to user index & match hereafter, so many years i was doing things in lookups. Thank you.. for such a learning videos..

[@Stepford]

Brilliant! Thanks again Leila. Very easy to understand and follow as usual!

[@LeilaGharani]

Thanks Steve! Glad you like it :)

[@Theportraitdude]

Your content is an absolute LIFESAVER. Thank you so much for all the videos, your content is so easy to understand!

[@mannymtzgomez]

Thank you Leila! It really help me getting a quick data from a Table (7728 Cells - 112 Rows - 69 Columns) in a single table using the List and both options you taught in this video. Now i can get the data from any row/column combination i need with a few clicks.

[@aishwaryaravi8656]

Very clear & understandable. Well done, Cheers

[@euniceesinam2573]

You're the best...certainly the best I've seen among many instructors. Keep making our lives better in Excelandia😊

[@mikedsjr]

Thank you for your clear instructions on all methods.

[@kendrewt]

excellent examples and illustration! Thank you so much!

[@er.vijayakumarselvaraj6762]

Great Way of teaching!... thank you

[@vasuchawla2430]

easy to understand and follow, saved me so much time compared to other articles without videos

[@tejaswaroop920]

Mam I'm really impressed with ur way of explaining n thanks a lot

[@LeilaGharani]

You're very welcome Teja.

[@nikkit.2224]

Leila, thanks for this great content. It was a great refresher course, love your style of teaching! Stay gold human!

[@tonedh2auto]

Great video and even better presentation. Your explanation of these more complicated functions is amazing. Thank you very much.

[@schlagerclub]

Now that's what I call play around with knowledge and gaining wonderful experience. Thanks Leila! Another great inspiration! :-)

[@walterjamesagorilla803]

I recommend your channel to my friends who are heavily relying in excel functions to transform data and they find your video tutorials very useful.

[@laxmigandhi444]

This is excellent and well explained Laila! You definitely have helped me so much. Thank you

[@sachinrana184]

Your all videos are so different from others. Simple and easy to understand for everyone. Thanks you so much.💯

[@faYte0607]

Hi Leila, I stumbled upon your videos by random and love that your videos are high quality and very elegantly created compared to other Excel videos. I hope you continue to make great videos on Excel with emphasis on functions that'll be helpful for a marketing or finance person :)

[@LeilaGharani]

Happy to hear that :) Sure - will do my best. Let me know if there is anything specific you're looking for and I'll gladly add to my list.

[@MySpreadsheetLab]

Great explanation of the different ways to solve this Leila! Each one has pros and cons.

[@LeilaGharani]

Thank you Kevin. Nice to see you here :). Your videos are great too!

[@rotrose7531]

Best excel teacher! You do thing in such a creative way that makes people become to love this application. Thank you.

[@serigamel]

thanks so much Leila, I've been using this formula repeatedly since discovering your channel

[@sarahdeschene3152]

Awesome, I have been somewhat confused or at least vague on how this works, I could do simple functions but you really walked me through the logic and how to build the formulas from the ground up.

[@madanmohanverma9799]

The concept of index and match is now clear ...what a simple and effective way of presenting, not only for this function but for every function she narrates !!! Excellent !!

[@LeilaGharani]

I'm happy to hear that. Thanks for the feedback.

[@subhashsansarwal3914]

What a wonderful & awesome explanation of such a complex program.

[@ingridwang3664]

Thank you for sharing your tips 😇😇😇

[@architpanda7880]

Wow!!! scratched my head out for an entire day, until I found this genius solution. You're the best

[@LeilaGharani]

Glad it helped!

[@setorwudata-adzah3618]

Awesome video on index n match! Very clear and practical. Kudos

[@atrenchcoat]

This is brilliant, thank you!

[@DanielPeraalta]

exactly what I needed thank you!!

[@meaculpanaturale]

wow that seemed simple but it blew my mind to pieces. You made it look so simple. Thanks a bunch!

[@VivianNewman]

Thanks Mrs Gharani - You are a blessing.

[@wasabi4u724]

I watched the prior INDEX/MATCH video, and moved on to this one. This really fits my needs as an engineer with tons of data to evaluate or look up from a 2D data array with thousands of rows/columns. (raw data in CSV format, imported into Excel for the ease of eyes) THANK YOU!

[@LeilaGharani]

Glad it was helpful!

[@benardadautaj6867]

Omg, i have two days looking for a formula that can find my result and i finally find this video. It helps me a lot , thank you very much😊

[@LeilaGharani]

Glad it helped!

[@hninthetsu3155]

Pretty clear and useful. Thanks.

[@rajasekar79]

Your teaching method is very very clear. Thanks for this video.

[@heemanshuchikhlia6085]

Great explanation and easy to follow.. thank you

[@johnshambra8421]

I finally figured this formula out after watching your video. You are the best

[@LeilaGharani]

Glad it helped!

[@ratneshbansal5139]

Immense respect! As a teacher, you foresee what kind of problems a pupil could encounter and give a solution for that! Simply amazing! I have a question though, how could we apply index & match to an array (taking data from an array and putting answers on an array) using CSE! I could work it around by using additional index within match but otherwise with CSE it ceases to be dynamic on the match argument!

[@ZeratulX]

what a nice trick!! Learning a lot from you lately!!

[@jagajyotisahoo9105]

Thanks for the detailed explanation.

[@dseaq]

This is very interesting and super useful. Thank you so much!

[@piyushaggarwala]

Oh my god, this is EPIC. You're so clear with your explanations! Love it!

[@LeilaGharani]

Yay, thank you!

[@mehdizaidi3478]

Wow! brilliant. Thanks Leila.

[@ulligruber1344]

Great video series about VLOOKUP, INDEX and MATCH for complex lookups. Love your teaching style and details Leila 💪 Questions: Is it possible to create a new Excel function including INDEX/MATCH to ease the data selection form the search table furthermore? For big data tables, is there a noticeable difference in search speed, memory usage? Which one would be the better pick there?

[@sudhirhiwale9784]

Another brilliance from you Leila. Thanks once again.

[@LeilaGharani]

Thanks for the kind feedback. I'm glad you like the tutorial.

[@KaushalKumar005KK]

😱 Never thought using index and match like this... Thanks 🙏

[@nitinkhare01]

Thanks! all your tips are helping me to be efficient and free up my energy :)

[@santoshpv321]

This is very helpful...As always your videos are way above the others...Thx.

[@alexrosen8762]

Super solutions and explanations. Thank you very much :-)

[@Bender2497]

Super helpful for a file I'm working on to look up values in a table. Much appreciated!

[@linm8649]

Great video! I finally got to understand the combination of Match and Index! Thank you so much Lelia!

[@LeilaGharani]

My pleasure. It's a great tool to have in your kit :)

[@almadesaudade]

I LOVE YOU! Thanks for this!!!

[@tharwatelsayed1397]

simply brilliant , thank you

[@banwarifageriaexcelclasses1447]

nice very effective ...thanks Leila ..actually your all tutorials are very useful and educative .

[@LeilaGharani]

My pleasure. I'm glad you like the tutorials.

[@marvinreyes7459]

You are Great. I was looking forward to changing 6 nested =IF(AND( = bla bla in one formula and I found this great formula you just showed and gave me the same result with less functions.

[@tomd1969]

I learned something today.

[@carolcoulter342]

You are the absolute best teacher of complex excel functions ever!

[@LeilaGharani]

Thanks for the kind words Carol!

[@jackiepham1570]

This is brilliant!!! Thanks so much

[@goodgollywally]

Thank you for these Excel instructional videos. You explain the formulas in a way that makes them easy to learn and understand. Best Excel training videos on the web.

[@LeilaGharani]

That's very kind Walter! I'm glad you find the explanations easy to follow. Appreciate your support.

[@rbarbe1]

Thank you this is one of the best videos I've seen on this subject.

[@LeilaGharani]

I'm glad to hear that! Thank you for your kind comment and your support!

[@speedbeeqs]

Awesome, thanks for your tutorial.

[@yuliandrirony4935]

i love the way you teach

[@LeilaGharani]

I'm glad to hear that.

[@ayazhussain3635]

Thanks for explaining everything in such a clear and easy way....

[@LeilaGharani]

You're welcome Ayaz.

[@shashikantgaekwad7768]

very simple explanation and thank you so much Leila for making it this simple... Thank you soo much..

[@LeilaGharani]

You're welcome 😊

[@nancmadi]

THANK YOU.... I really like how well you explain things.. I will try to use this instead of VLookup to learn better.

[@LeilaGharani]

You're very welcome. I'm glad you like the explanation! It's difficult to get the hang of it. It took me a while....but once it clicks, it's just so much more flexible than Vlookup.....

[@elisapujadas9790]

......very clear and logical...so easy to follow. Thank You Leila.

[@LeilaGharani]

Glad to hear that. You're very welcome Elisa.

[@Hello-Annyeong]

great! 선생님 잘배웠습니다!

[@ericanderson3962]

Thank you very much for sharing your skills. I can only hope someday I'll pay it forward.

[@daquytx8139]

Very helpful. Thank you very much.

[@patryklagwa5549]

Thank you, very helpful!

[@steviebb2631]

Nice explanatory tutorials - thankyou!

[@LeilaGharani]

You're welcome.

[@RomanAngel]

awesome!! Loved this tip. thanks.

[@afzalahmed86]

Amazing, helped me with an idea for work!

[@jhonbernardovlogs3173]

This is very helpful! Thank you

[@balamira297]

Crystal clear explanation!

[@LeilaGharani]

Thank you!

[@baxification]

Thank you!!!

[@yuliandrirony4935]

Its so clear i understood thank you Leila

[@LeilaGharani]

I'm glad to hear that.