Topics: 1. (00:30) Introduction to what INDEX and MATCH can do 2. (01:42) All about MATCH Function 3. (02:04) MATCH for Exact Match Lookup, [match_type] = 0 4. (02:34) What is Approximate Match? 5. (03:16) MATCH for Approximate Match Lookup with values Sorted Ascending, [match_type] = 1 or omitted 6. (04:02) MATCH for Approximate Match Lookup with values Sorted Descending, [match_type] = - 1 7. (04:17) INDEX and MATCH together to do any type of lookup 8. (04:26) INDEX & MATCH to do One-way lookup to retrieve a single value in a column to the left of the lookup value. Example of Exact Match Lookup. 9. (05:34) INDEX & MATCH to do One-way lookup to retrieve a single value in a column to the left of the lookup value when lookup values are sorted biggest to smallest. Example of one type of Approximate Match Lookup. 10. (06:17) INDEX & MATCH to do Two-way lookup to retrieve a single value. See second type of Approximate Match Lookup and an Exact Match Lookup. 11. (07:24) How to Copy Two-way lookup formula. 12. (09:01) INDEX & MATCH to lookup a list of values 13. (09:23) INDEX & MATCH to do Two-way lookup to retrieve a full row 14. (10:15) INDEX & MATCH to do Two-way lookup to retrieve a full column 15. (10:41) INDEX & MATCH to do Lookup an item from multiple tables 16. (10:41) MATCH to get Table Number 17. (11:11) INDEX & MATCH to do Lookup Columns Headers 18. (11:56) Summary
Hi Mike, great video as usual! Just one thing, at minute 5:54 you are saying that the values are from smallest to biggest when in fact it's the other way around..though people can see that;)
WOW! Your Storytelling is like perfect! Truly “audiovisual”: your visuals speak every word you say, and perfectly synchronized, too! BTW, coincidentally: this was the perfect synopsis video on INDEX & MATCH! The best one I’ve ever seen! Thanks a lot!
Hi Mike.. as usual.. even your quickie videos are overflowing with EXCEL goodness and value. Whether by way of review or a fresh topic, I always learn something new by watching you work. Even with more functions on the way from Microsoft, understanding the fundamentals of INDEX and MATCH is key to EXCEL mastery. Thanks for the quick lesson and for the links to the Full Story videos which I look forward to studying in depth. Double Thumbs up for always outdoing yourself to the benefit of the community!!
Thank you, Thank you and Thank you. I have memorized this video and thanks to the info and the excercices I approved my Technical test and got a new job. Thank you very much for your help support and effort doing this channel.
I went through your complete Excel Basics course during the last few weeks. I am prepping to take the MO-201 test and this was a skill that I found I understood the concept of, but did not know how to actually write the formula out. What a great refresher. I definitely won't be missing any Index and Match questions on the exam. Thank you!
I was researching how to do Index & Match functions today and I can't believe I just applied it on a file I was working on thanks to this video. You are amazing. Thank you so much!
Omgggg I missed this video and you dedicated to me !!! Thank you so much for your explanation and your dedication! Your viewers appreciate you very much for sharing your knowledge!
Awaking Beauty Of Excel, what can I say, but: Go Team!!!! Even if you did not see it right after it was posted, you inspired the video and many Teammates have since have had fun and benefited : ) Go Team!
Great idea for a series. Love your comprehensive videos, don't get me wrong, I've learned so much from those videos and workbooks. But for a refresher, or to point a colleague to summary on an Excel topic, these will be my go-to. Thanks Mike!
Brilliantly explained Mike witting all the details about =INDEX(MATCH() lookup function necessary to understand from your Excel Knowledge-Box. Your last example in this tutorial I remember you expounded on in a long video tutorial of yours where the whole platoon of pertinent details came down on me charging with attempt to incite ones interest in exercising it. This tutorial however it’s much shorter but comprises succinctly all one needs to know and refreshes ones learned skills. Great job as always. Thanks.
That has been my seciality for 12 yeasr here at UA-cam. I am glad you found the excelisfun channel and that I could help with INDEX and MATCH, Dannie! Thanks for your support on each video that you learn from with your comment, thumbs up, and of course your SUB : )
Excellent. Truth is your presentation and teaching skills are also very good (in addition to being the Excel magician); one day you should have a video on producing an education video because you are way better than anyone I have seen in online education. Thanks for all the hard work!
Thanks for the video, love how versatile Index Match is; as always a great overview. I find your short videos area great resource when trying to explain to colleagues why they should think hard before they mess with the formulae and designs (unfortunately it is not always practicle to protect every important cell :()
Hi. Just another perfect learning lesson. U R fantastic in u explanation. I just have learned so much I never knew before finding u youtube channel. Thanks you very much. :) I am a happy follower :)
Thank you a lot for this excellent video! I would be interested to understand why in the "6-7 (an)" spreadsheet, when I transform the A9:E14 list in a Table, the B5 cell doesn't give anymore the result and we obtain a N/A message instead of the price 35.55. If I scrutinize a little bit more, I notice that the approximate Match function applied to the headers (horizontal) is the one which displays N/A. It would be great to understand how it can be fixed since the use of tables with Excel is so interesting. Thanks in advance for your help.
Another wonderful video; what I really regret Index & Match, Lookup are not that popular or say not many people have kind of a situation where they can do 10 times better than Vlookup :(
Yes, I made this video because most Excel users will still be using these two for years to come. It took about 10 years for PivotTable to take hold. It is about 5 years into Power Query and it has not taken hold yet. Dynamic Arrays and XLOOKUP will be a few years off. I can;t wait for it all to switch. Thanks for your support, Richard!
@@excelisfun Which is all really sad. Microsoft's marketing people think that people are idiots. Last month they posted a video on how to do subtraction in Excel! ua-cam.com/video/nXrtlxhUbTY/v-deo.html If Power Query was exposed properly and if they would quit changing the terminology they use I think it would be a huge hit.
@@houstonsam6163 Yes it is that good. It can do almost every thing that any of the other lookup functions can do, but all in one function. I have a comprehensive video on it, but since MS changed the arguments, my video doesn't show that yet. When they release it to all of Office 365 officially, I will re-make my comprehensive video.
@@excelisfun yes its helping me a lot ...in your advance excel series ..lect 13 I ve seen complex formula of index and aggregate combination and then I'm trying to understand each formula separately....now I've understand it completely, many thanks to you for that🙏❤️
Topics:
1. (00:30) Introduction to what INDEX and MATCH can do
2. (01:42) All about MATCH Function
3. (02:04) MATCH for Exact Match Lookup, [match_type] = 0
4. (02:34) What is Approximate Match?
5. (03:16) MATCH for Approximate Match Lookup with values Sorted Ascending, [match_type] = 1 or omitted
6. (04:02) MATCH for Approximate Match Lookup with values Sorted Descending, [match_type] = - 1
7. (04:17) INDEX and MATCH together to do any type of lookup
8. (04:26) INDEX & MATCH to do One-way lookup to retrieve a single value in a column to the left of the lookup value. Example of Exact Match Lookup.
9. (05:34) INDEX & MATCH to do One-way lookup to retrieve a single value in a column to the left of the lookup value when lookup values are sorted biggest to smallest. Example of one type of Approximate Match Lookup.
10. (06:17) INDEX & MATCH to do Two-way lookup to retrieve a single value. See second type of Approximate Match Lookup and an Exact Match Lookup.
11. (07:24) How to Copy Two-way lookup formula.
12. (09:01) INDEX & MATCH to lookup a list of values
13. (09:23) INDEX & MATCH to do Two-way lookup to retrieve a full row
14. (10:15) INDEX & MATCH to do Two-way lookup to retrieve a full column
15. (10:41) INDEX & MATCH to do Lookup an item from multiple tables
16. (10:41) MATCH to get Table Number
17. (11:11) INDEX & MATCH to do Lookup Columns Headers
18. (11:56) Summary
Hi Mike, great video as usual! Just one thing, at minute 5:54 you are saying that the values are from smallest to biggest when in fact it's the other way around..though people can see that;)
I love it that you have pinned a topics summary on the comments. Thank you.
My two friends at work.... Index and match!....Thank you
That is funny, NoShodowOfDoubt : ) : )
WOW! Your Storytelling is like perfect! Truly “audiovisual”: your visuals speak every word you say, and perfectly synchronized, too!
BTW, coincidentally: this was the perfect synopsis video on INDEX & MATCH! The best one I’ve ever seen!
Thanks a lot!
Wow to you too, Geert : ) : ) Thanks for the kind words. This one did take a long time - one whole week to get the visuals and all just right !
These videos on “simple” topics are packed with info. Thx👍
That is for sure: packed : )
Love the condensed version!
Thanks for the love, Teammate : )
Hi Mike.. as usual.. even your quickie videos are overflowing with EXCEL goodness and value. Whether by way of review or a fresh topic, I always learn something new by watching you work. Even with more functions on the way from Microsoft, understanding the fundamentals of INDEX and MATCH is key to EXCEL mastery. Thanks for the quick lesson and for the links to the Full Story videos which I look forward to studying in depth. Double Thumbs up for always outdoing yourself to the benefit of the community!!
Thanks for the double thumbs up and well worded, thoughtful and kind comment, Wayne : )
Short and sweet set of examples using the INDEX/MATCH functions...thanks Mike!
You are welcome, Teammate!!!!!
index and match, great combination, thanks
You are welcome, Most Awesome Stats Guy : )
Thank you, Thank you and Thank you. I have memorized this video and thanks to the info and the excercices I approved my Technical test and got a new job. Thank you very much for your help support and effort doing this channel.
Nice refresher video Mike!
Thanks, Chris : )
I went through your complete Excel Basics course during the last few weeks. I am prepping to take the MO-201 test and this was a skill that I found I understood the concept of, but did not know how to actually write the formula out. What a great refresher. I definitely won't be missing any Index and Match questions on the exam. Thank you!
Best of luck on your exam : )
I was researching how to do Index & Match functions today and I can't believe I just applied it on a file I was working on thanks to this video. You are amazing. Thank you so much!
Crystal clear and spot on for relevance, as usual. You are the rock star of Excel.
Glad it was crystal clear, Sam!!!!
Hi Mike,
Another Great video, you've covered all in one.
Love to learn from you.
Thanks for the love, Khalid!!!!
Thanks for THIS excellent video
You are welcome for the EXCELlent video, Simoiya !!!!
Omgggg I missed this video and you dedicated to me !!! Thank you so much for your explanation and your dedication! Your viewers appreciate you very much for sharing your knowledge!
Awaking Beauty Of Excel, what can I say, but: Go Team!!!! Even if you did not see it right after it was posted, you inspired the video and many Teammates have since have had fun and benefited : ) Go Team!
@@excelisfun yes awesome! Go team !
Excellent review, great video that refreshes and reinforces previous learning for people who don't use this formulas everyday.
EXCELlent comment, Steven Nye!!!!!!
Great idea for a series. Love your comprehensive videos, don't get me wrong, I've learned so much from those videos and workbooks. But for a refresher, or to point a colleague to summary on an Excel topic, these will be my go-to. Thanks Mike!
Yes, I agree, the long comprehensive have there place, but so do the short and condensed : ) You are welcome, Andre!
Brilliantly explained Mike witting all the details about =INDEX(MATCH() lookup function necessary to understand from your Excel Knowledge-Box. Your last example in this tutorial I remember you expounded on in a long video tutorial of yours where the whole platoon of pertinent details came down on me charging with attempt to incite ones interest in exercising it. This tutorial however it’s much shorter but comprises succinctly all one needs to know and refreshes ones learned skills. Great job as always. Thanks.
Glad you like it, Denin!!! You are welcome : )
You have not idea, how I appreciate all your help. Thank you so much
Another great tutorial Thanks so much this was very helpful 👍🏻
You are most welcome, D-Day!!!!
Thank you again! I was confuse on multiple index match conditions. I understand it better after watching your video . Great teacher!
I am glad that I can help bring clarity, Jina : )
Perfect mike mastering index and match is great thing in excel
Yes, to become good in Excel you must master INDEX and MATCH!!!
I like these vids. Good way to practice fundementals and see if I have any holes in my knowledge.
Glad you like the hole-finding videos, Johnnydizzlefoshizle ; )
Absolutely beautiful as always Mike, thank you so much 🤗
You are welcome for the beauty, Katerina!!!
bro you are a BEAST !
Great job Mike. You got it condensed as promised.
You are welcome, WRH!!!!
Very clear explanation and informative, I think it's the best so far compared with other UA-camrs trying to explain. Great job Mike !!!
That has been my seciality for 12 yeasr here at UA-cam. I am glad you found the excelisfun channel and that I could help with INDEX and MATCH, Dannie! Thanks for your support on each video that you learn from with your comment, thumbs up, and of course your SUB : )
You are greatest Excel Master I Ever Seen. GO Ahead. I enjoy and learn all of your videos. Please, Use some big big data with formulas.
Excellent. Truth is your presentation and teaching skills are also very good (in addition to being the Excel magician); one day you should have a video on producing an education video because you are way better than anyone I have seen in online education. Thanks for all the hard work!
I have learned a lot a lot from your tutorial . Thanks more teacher God bless you!!
Loved this short video 👍 I’m fan of ExcellsFun🥰
i want a teacher like you .
awesome may you get the rewards that you deserve. i like your teaching style
You got a teacher like me! Right here at excelisfun here at UA-cam : )
Another comprehensive refresher, thanks Mike 👍.
You are welcome for the refresher, Paul : )
What an great video, a lot of stuff in just 15 minutes
I enjoy the Index and match functions. Thanks for revisiting that Mike.
You are welcome for the revisit!!!!
XLnt explanation on INDEX + MATCH !!!
Like this: XLnt : ) : )
Thanks for the video, love how versatile Index Match is; as always a great overview. I find your short videos area great resource when trying to explain to colleagues why they should think hard before they mess with the formulae and designs (unfortunately it is not always practicle to protect every important cell :()
Yes, I have been trying to learn how to make shorter videos in this 2019 year! Thanks for your support, Roy!
@@excelisfun you are welcome, Mike. I hope you continue to make the longer videos in more depth too as they are always very useful too.
@@roywilson9580 Will do. Why? Because I am a teacher at heart who loves teach the hows and whys in great detail. More of both to come: Short and Long!
Thanks a lot for this video Mike.
You are welcome a lot, Maria : )
Really Nice, covered everything about INDEX and MATCH. Thank you.
You are welcome, Anbarasu!!!!
Nice brush-up on INDEX and MATCH to me. Thank you.
You are welcome, kiwiliow!!!!
Amazing 10-minutes-EXCELopedia :)
Cool, Teammate!!!!! EXCELopedia is just too fun : ) : ) : ) : )
Nice recap Mike. Thank You :)
Glad you like the quick re-cap, nimrodzik1!!!!
Great video Mike, thanks
You are welcome, Graham : ) : )
I have no words to describe your knowledge n expertise 🙏🙏🙏
Glad I can help and thanks for the great "no words" comment : ) : )
You are great Mike.
Glad I can help, hien!!!!!!!
@@excelisfun Thanks.
This was very helpful right now!!!
Than you Soo much for this amazing video. 😍
You are welcome so much, vishwa!!!!
Excellent primer, Mike!
Thanks, DRSteele! Primer for you because you are already so smart with Excel ; )
Another great video. Index/Match is one of my favorites. Hello.to Excelisfunner.
I will tell Excelisfunner that you say hi, Robert : ) : )
Thank you Mike for sharing. I am a fan of index match.
You are welcome for the share, claire : )
Hi. Just another perfect learning lesson. U R fantastic in u explanation. I just have learned so much I never knew before finding u youtube channel. Thanks you very much. :) I am a happy follower :)
I am happy that you are happy, Henrik : ) : )
شكرا على مجهودك الرائع.
Thank you .
Awesome 👍
You are welcome, Faisal : ) : )
Thank you for the great video.
You are welcome, Mohamed!!!
Thank you a lot for this excellent video!
I would be interested to understand why in the "6-7 (an)" spreadsheet, when I transform the A9:E14 list in a Table, the B5 cell doesn't give anymore the result and we obtain a N/A message instead of the price 35.55. If I scrutinize a little bit more, I notice that the approximate Match function applied to the headers (horizontal) is the one which displays N/A.
It would be great to understand how it can be fixed since the use of tables with Excel is so interesting.
Thanks in advance for your help.
Great job , thanks alot Mike .
You are welcome a lot, tha2ir!!!!!!
Wow, you're such a natural, thanks so much for the help
You are welcome!
Great examples!
Thanks, pmsocho!!!!!
Another wonderful video; what I really regret Index & Match, Lookup are not that popular or say not many people have kind of a situation where they can do 10 times better than Vlookup :(
Yes, the can definitely do more than VLOOKUP : )
Great boss of Excel
Thanks for boss comment, Michael : )
Thanks for another great video
Glad you like it, Patrick!!!
Thanks Mike for this Amazing video...
You are welcome, kishore!!!!!
Nice tip! Kudos to you sir
Glad you like the INDEX & MATCH tip, Jayrod : )
Excelent exposition! It's clear
Glad it is clear for you, Ruben : )
The best trainer thanks michael
You are welcome, Atiq!!!!
Super useful video.
Crystal clear explanation
Glad it is crystal : ) : )
Best teacher of my life ❤️ ... God bless you sir 🙏
You are welcome, Robin!!!
Thank you. Very helpful.
You are welcome, Henry!!!!
love it, thanks!
You are welcome, Tingting : )
among lookup functions, index match is superior :D
Yes, superior, until XLOOKUP becomes prominent in a few years ; )
Always Great Mr. Girvin
Glad you like it! At this point you have studied a lot of my videos. I bet you are a real Excel Master now : ) : )
@@excelisfun Your words are a testimony that I cherish Mr.Girvin.
Fantastic content, you're amazing!
Glad this video helps you, Matthew!!!!
ExcelIsFun You’re such a blessing Sir.
@@mattwizy , Glad I can help : )
Sir I am very happy today as I upgraded from MS OFFICE 2010 to MS OFFICE 2013. and I loved FLASH FILL in excel
Yes, Flash Fill to so helpful!
Thanks again Mike....
You are welcome, gary timms : )
Thanks mike. Love it :) :)
I always think of you when I make a formula video...
@@excelisfun pls think of me more and more :) :) :)
I love your work
Glad you love it! Love and Excel go together well : ) Thanks for your support, Jules!!!
Great video 👍
Glad it is great for you, RD 12!!!
Thanks a lot!
You are welcome a lot, Karina!!!
Really awesome video, I like it.
Glad you like it, Ebrahim!!!!
thanks a lot , very useful
You are welcome!
Excellent video
Glad it was EXCELlent for you Amit ; )
Thank you 👍
You are welcome, Bader : )
Perfect great video
Thanks, mohamed!!!!!
Thanks!!!
You are welcome, Josef!!!!
thank you !
You are welcome, Enes : )
Thank you.
You are welcome, S M : )
great stuff.
Glad it is great for you, Dineth : )
Thanks Mike
You are welcome, Dwight!!!
Cool!!! 🙌
Glad it is cool for ya, FRANKWHITE1996 : )
you are Rock star 👍🏻👍🏻👍🏻👍🏻💯💯💯💯
Hi, can u explain index array function? What if both the data are available as col instead of row and col?
Great Video Sir😊😊
Thanks, Vikas : ) : ) : ) : )
Great video. Reinforced why I have replaced I/M wIth XLOOKUP!!
Yes, I made this video because most Excel users will still be using these two for years to come. It took about 10 years for PivotTable to take hold. It is about 5 years into Power Query and it has not taken hold yet. Dynamic Arrays and XLOOKUP will be a few years off. I can;t wait for it all to switch. Thanks for your support, Richard!
@@excelisfun Which is all really sad. Microsoft's marketing people think that people are idiots. Last month they posted a video on how to do subtraction in Excel!
ua-cam.com/video/nXrtlxhUbTY/v-deo.html
If Power Query was exposed properly and if they would quit changing the terminology they use I think it would be a huge hit.
@@excelisfun I'm an I/M purist. Honestly have not given XLOOKUP a fair look. Is it really good enough fully to replace the robust power of I/M?
@@brianxyz That is funny...
@@houstonsam6163 Yes it is that good. It can do almost every thing that any of the other lookup functions can do, but all in one function. I have a comprehensive video on it, but since MS changed the arguments, my video doesn't show that yet. When they release it to all of Office 365 officially, I will re-make my comprehensive video.
That's a great idea! Thanks for it. #TechnicalPortal
What idea is great, Technical Portal?
You are true guru
Thanks, anjan : )
Great 🙌
Glad it is great for you, Augusto : )
Always love your videoes😍
Is this helping you to learn more about INDEX?
@@excelisfun yes its helping me a lot ...in your advance excel series ..lect 13 I ve seen complex formula of index and aggregate combination and then I'm trying to understand each formula separately....now I've understand it completely, many thanks to you for that🙏❤️
@@shubhampawar8506 Your dedication and hard work is making you an Excel Expert!!!! : ) : )
@@excelisfun thanks for your kind words 😊