Рет қаралды 1,024,263
Join 400,000+ professionals in our courses here 👉 link.xelplus.c...
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....
★ Complete Excel Dashboard course: www.xelplus.co...
🔑 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.creato...
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.co...
🎥 RESOURCES I recommend: www.xelplus.co...
🚩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