How to use Excel Index Match (the right way)
3,620,534
Published 2017-03-04
Quickly learn all you need to know about INDEX & MATCH to get a quick start. This video compares INDEX & MATCH with VLOOKUP, demonstrating why INDEX & MATCH is a superior choice for complex data lookups.
👉 Get access to the complete Excel Dashboard course: www.xelplus.com/course/excel-dashboards/
⬇️ Download the file I used in the video from here: pages.xelplus.com/index-match-basics
What You'll Learn:
▪️ Basics of INDEX & MATCH: Understand the fundamentals of how INDEX and MATCH functions work individually.
▪️ Why INDEX & MATCH is better than VLOOKUP: Discover the limitations of VLOOKUP and scenarios where INDEX & MATCH is more effective.
▪️ Practical Examples: Step-by-step guide to using INDEX & MATCH for real-world data lookup problems, including two-way lookups and matrix-type data arrangements.
▪️ Advanced Techniques: Learn to solve complex data problems that go beyond simple table formats.
00:00 Is INDEX & MATCH the same as VLOOKUP?
00:52 Excel INDEX function explained
05:07 Excel MATCH function explained
06:29 Using INDEX & MATCH together
08:08 Two way lookup with INDEX & MATCH (data validation)
10:30 How to REALLY learn Excel INDEX & MATCH
Why Index Match is better than VLOOKUP & why use INDEX MATCH instead of VLOOKUP?
Here are 3 reasons why Excel experts generally substitute VLOOKUP with INDEX and MATCH.
1. Unlike VLOOKUP, which searches only to the right, INDEX and MATCH can look in both directions – left and right.
2. INDEX & MATCH can perform two-way lookups by both looking along the rows and along the columns to find the intersection within a matrix.
3. INDEX & MATCH is less prone to errors. Assume you have a VLOOKUP where the final value you want returned is in column N. Your lookup value is in column A. You need to highlight the entire A to N range and then provide your index number to be 14. If you happen to delete any of the in-between columns, you would have to update that index number. You don’t need to worry about this when you use INDEX & MATCH.
Note: Index match is not case sensitive
➡️ Join this channel to get access to perks: youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA/join
🎓 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: www.instagram.com/lgharani
LinkedIn: www.linkedin.com/company/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
All Comments (21)
-
:_leilaLightbulb: Grab the file I used in the video from here 👉 pages.xelplus.com/index-match-basics
-
$1300 for a required college course. $250 for a required book on Excel (& Access). Hours of lecture. I've learned more in minutes with Leila's videos than the entire semester so far.
-
I wish more people would showcase and explain individual functions on their own when making tutorials on combined functions. Very informative.
-
I have been stuck on my homework for hours rereading my chapter and redoing the exercises. Your video was the first to pop up when i googled Index and Match and I am so happy I clicked on it. You explained it so well. It helped me immediately make progress! thank you so much for helping me :)
-
To be honest, this is the only video that was able to explain to me these nested fonctions (Index and Match). Incredible Leila, I don't regret anything about the lessons I'm taking with you. Great thanks to simplifying easily complicate things.
-
What a wonderfully easy to follow lesson! This really helped me finally learn this function combination. Thank you!
-
Leila, you are a brilliant teacher! Thank you so much for this video. In 11 minutes I understood and am able to apply this information in a real life situation that I have been working on for quite a while.
-
This is one of those videos that I just keep coming back to every once in a while just to refresh myself on the function. Love this! Your pace is great, you aren't condescending but don't assume we don't know ANYTHING about Excel, either. Perfect balance -- What a great teacher you are. Wish I had you as a Professor in Uni.
-
I really like her presentation style. After she completes something, she always goes back to show why it works !!!! Not all excel lectureres have figured this out.
-
Been watching Leila's Excel videos for the last 3+ years and there is simply nobody better to teach the technical concepts of Excel in such an easy way. I used to marvel at upper management's ability to do complex spreadsheets and I learned to beat them out in just a year of watching her vids.
-
Hello Leila, You teach me the functions in very smart way: simple and easy to understand. thanks a lot.
-
Thank you for your simple, clear, step by step teaching. Your videos are perfectly balance: the information transmitted by the image and the voice, the tips insertion. It is a pleasure to learn with you and also a way to learn how to teach.
-
Love you teacher and thank you very much for your generous contributions and I have learned a lot from your lessons even within 2 days of exploring your tutorials. I am sure that I will definitely benefit from your lessons therefore, I hope receiving your new lessons in the future as well. I will stay tuned to your new lessons:)
-
This is amazing. I just started a new job this week and I never used Index and match before. This helped me out a ton. Thanks
-
Love the map and coordinates analogy. That helps very much. Now perhaps when I'm in a hurry to do a vlookup real quick, I'll be more comfortable with the better index/match solution. Totally agree, you just need to force yourself to practice using it until it's comfortable.
-
I've searched and read so many help articles on Index and Match. I finally get it with this clear, concise explanation and demonstration. Thank you!
-
Thanks, Leila for simplifying it and giving logical explanation at each step. Actually applied the Index-Match formula and saved myself hours of useless work. Never gonna forget it.
-
Excellent. Describing it as GPS coordinates and the range as the map really helped. Good way to think about it.
-
Beauty with BRAINS!! Thank you so much for explaining this!
-
Thanks Leila for such a great explanation without any penny charge. If I look for this in the market it will cost me more than that. You are such a great tutor for excel on YouTube. Continue to keep up the good works 👍🏼👍🏼