Index Match Advanced: 3 Most Effective Formulas for Multiple Criteria

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.

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

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

🚩Let’s connect on social:

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

[deepakkanade]

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.

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

[UlyssesHaq]

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

[excelisfun]

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

[GordonWafimbi]

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

[philipmcdonnell]

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. 👍🏻😎

[Waydisturbed]

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

[matthewrichmond]

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

[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!

[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!!

[ArshadAli.kapilvastu]

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

[wasabiu]

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!

[kbalaji]

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

[madanmohanverma]

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

[walterjamesagorilla]

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.

[euniceesinam]

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

[thekingofkush]

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!

[ratneshbansal]

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!

[Ofageover]

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

[dhavalshah]

Queen of Excel, Leila Thank you for this video