INDEX MATCH Excel Tutorial
ฝัง
- เผยแพร่เมื่อ 4 ก.ค. 2024
- 🔥 Learn Excel in just 2 hours: kevinstratvert.thinkific.com
In this step-by-step tutorial, learn how to use Index Match in Microsoft Excel to lookup values. We start with how to use the index function. We use the game Battleship as an example to make it easy to understand. The index function returns a value or the reference to a value from within a table or range. Next, we look at how to use the match function. The match function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. We then bring both index and match together to find specific values within an array. We start with a simple example and then we finish with two more advanced examples. We also look at how to set up a simple drop down list to start building an interactive dashboard.
👋 Additional resources
- Sample workbook to follow-along: 1drv.ms/x/s!AmxrofZZlZ-whMguW...
- Learn the fundamentals of Excel in just 2 hours: kevinstratvert.thinkific.com
⌚ Timestamps
00:00 Introduction
01:16 Index function
03:31 Match function
05:36 Index & match simple example
08:50 Index & match advanced example 1
11:54 Drop down lists with index match
13:01 Index & match advanced example 2
15:02 Wrap up
📃 Watch related playlists
- Playlist with all my videos on Excel: • ❎ How to use Excel
🚩 Connect with me on social
- LinkedIn: / kevinstratvert
- Twitter: / kevstrat
- Facebook: / kevin-stratvert-101912...
- TikTok: / kevinstratvert
🔔 Subscribe to my TH-cam channel
th-cam.com/users/kevlers?...
🎬 Want to watch again? Navigate back to my TH-cam channel quickly
www.kevinstratvert.com
🛍 Support me with your Amazon purchases: amzn.to/3kCP2yz
⚖ As full disclosure, I use affiliate links above. Purchasing through these links gives me a small commission to support videos on this channel -- the price to you is the same.
#stratvert - วิทยาศาสตร์และเทคโนโลยี
![[ LIVE ] Rank เล่นแต่แครี่ ยาวๆถึง 100ดาว “วันนี้มีกิจกรรมลุ้นรับสกินมาบอกต่อ โก๋แก่ช็อกโกบอล x ROV”](http://i.ytimg.com/vi/5NNgkhWj57Y/mqdefault.jpg)
🏫 Learn the fundamentals of Excel in just 2 hours: kevinstratvert.thinkific.com
⏭ Watch next - playlist with all my free tutorial videos on how to use Excel: th-cam.com/play/PLlKpQrBME6xLYoubjOqowzcCCd0ivQVLY.html
Kevin, I am already well nuanced with excel, I would like to have some time learning more advanced excel formulas, do you have a contact number to reach you Thanks for prompt response. Thank you, Miguel Gaitan
As a trainer myself, it's your ability to cut through so succinctly to the essence of what's happening that impresses me the most about your tutorials. No unnecessary talk, focused only on key concepts and steps and then trusting your audience to either work the rest out for themselves or come back for more when they're ready.
Very very good.
So true
totally agree! Absolutely crisp tutorial!!
Can we all appreciate Kevin's hardwork and dedication?
I really like the introducuton of Kevin Hi Everyone Kevin Here Today..
By far the best Index/Match tutorial on TH-cam. Thank you so much. I've been stuck on a complex index match formula for hours. I had to go back to the basics to get it right. Thanks again 😉
This is a super powerful lookup function. My 1st time learning was from a software engineer at our company, and the correct way to do this formula slips my mind now and again. I really appreciate you taking the time to make this video because it helps so much when using large datasets for research and analytics. This combined with a drop-down menu is unstoppable. Makes processes so easy and impresses folks as well.
Kevin thank you for this! You explained a very complex function in such a way that was easy to understand. I've learned so much about Excel from your channel!
Thanks Kevin! This is one tutorial that makes INDEX and MATCH so clear and easy to use! Keep up the great work! 😊👍
Hi Kevin! I use INDEX MATCH all the time but am a long time subscriber and wanted to see your unique take/style on it. I wouldn't have believed you could pack so much useful stuff into 15 mins but you did! Appreciate the early mention about not needing Office 365 as I "complained" about wasting time watching videos only to find late on that my 2019 version wouldn't be any use. I'm an old guy and have struggled to make a living, this being exacerbated by chronic health problems. Excel is one of two skills (the other being languages), that are my stand outs and I really enjoy your videos, so thanks.
Excel has so many features. It's an amazing powerful program. You explain things with ease. Thank you!
The advanced example 2 is EXACTLY what I've been looking for, thanks so much, I've used so many of your tutorials
He saved my life with that second example. I saw the video ending and I still couldn't apply it to my situation...I was getting sad... But that 2nd advanced example really helped!!
Hi Kevin,
Yours is the first video I have come across for explaining Index and Match in the most simple manner.
Good job and keep making good videos.
The bit from 13:16 is exactly what I will be using right now for my reporting, many thanks. I've been doing a convoluted way with multiple VLOOKUPS up until now.
I have been trying for a while to figure out the Index Match function, and this tutorial gave me my Eureka moment. Thanks so much! I really appreciated the clear detail and the Battleship reference was very helpful. 😃
Hey Kevin, the way you just broke down index and match was brilliant! I have been trying to figure out how to use it for the longest time but just couldn’t get it until I saw your video. Love your tips and tricks on Excel. Please keep them coming. Thank You!!!!!
Thanks for this Kevin. The Battleship analogy works well. And I love your method of working out the MATCH functions to replace the numbers.... that concept makes creating INDEX MATCH formulas that work SO much easier to understand! Thanks again.
Great to hear!
Best index & match tutorial on TH-cam. Period. Thanks a mill.
I use Index & Match all the time. But the last tip was new for me. Thanks.
Amazing Sir!!!!! the best part is that you upload every day!!!!! You are the best!!
Very Useful While Working
Man you´ve got a gift for explaining - very clear and concise
Hi Kevin, will definitely use this INDEX and MATCH functions in my spreadsheets. Proud also to see MALAYSIA been part of this tutorial. :-) Thanks so much!
Love Malaysia! ❤️
@@KevinStratvert have you been to Malaysia before?😅
Thank you Kevin for your informative tutorial. I will definitely be using this in my business for looking up prices of my items I sell.
Wow! Excel is crazy! I have been watching lots of your Excel videos lately. Thanks so much Kevin.
excellent explanation. the greatest part is the simplicity with which you explained it. Thank you Kevin and i will be using this in my next spreadsheet
Great video, Kevin! I share your videos with my students as part of learning material. Thanks!!
"YOU SUNK MY BATTLESHIP!"
Battleship was a childhood game i enjoyed alot! referencing this with indexmatch was a great way to explain! love it Kevin!
I thoroughly enjoyed this one. I've had trouble understating indexing and matching. You broke it down nicely
Yes, I often use Index Match function. Thank you so much for the presentation.
You are indeed the best Excel teacher Kev! Wow! Thank you!
I agree with Martin of Aus - best comprehensive instruction I've found yet!!! Thank you!
when I saw some Index/ match formula written by others, I was so afraid they are very confusion. you made these complex formula made simple. thanks much!
Your pedagogical approach is outstanding!!!!!
Great opening to set the scene on the basics... makes it really easy to follow!
0:42 There is always something special I would like to hear. This time the way you said "Alright" was nice.
Great video the easiest and most comprehensive I've seen on index match yet, Yay I can finally easily do INDEX MATCH
Your intros always become better!!!! Hi everyone, Kevin here 🔥🔥🔥😁😁😁
Thank you so much for your videos, they are way better and easy to understand than my teacher's explanations.
This is by far the best way to explain! Thank you 🙏🏻
I went through many youtube videos regarding index & match formula but I didn't get the correct assistance but you did it in a smart way thank you Kevin ♡ you really made it very simple ☆
Thank you Kevin. You have a gift for teaching.
Wonderful!! And so clearly explained, thank you!!!
I like the way you explain things so easily, Thanks Kevin
one of the best explanations and super best breakdown of the formula, otherwise it was hard and confusing to understand.
I always resisted Index Match. This breakdown was excellent!
The best index match explanation on TH-cam
I will be using INDEX MATCH now. Thanks!
Kelvin you are the best tutor for learning this, your explanation is amazing and easy to follow, learn and master. I am so happy i stumbled on your channel. Great work. Please do a series on Power BI as well
Wow 😳 why didn't I know this before.. such great help with ease ✅.. thanks Kelvin 👏
Thank you so much for the Battleship analogy for Index and Match! This finally makes sense to me!
I've seen this explained in so many ways that it can sound like voodoo. But this was broken down in such simple terms it's impossible not to understand. Great job.
Thanks for the note!
Hey Kevin, thank you for the informative video and this will empower many of us in our daily lives.
Suggestion : If we working with multiple sheets and need to share the final sheet with other people, they are not able to view the data unless they have the other reference sheets, can you make a video on how to fix this ?
Kevin keep up the great work my friend keep up the great work thank you for these videos man God bless you
Best “explanation” of index / match so far.
You are a good teacher. Perfect.
Thank you for helping me impress my boss and coworkers on my first day!!!! This was ridiculously helpful
Excellent tutorial! Easy explanation. Thank you.
Thanks for presented complex function in a simple way
Thankyou for breaking it down and simplifying it.
This was extremely easy to understand. Thanks, Kevin!
Hi Kevin, i can't find enough words to say thank you for this amazing video, you are the best bro ... Love you
thank you so much Kevin, i am always watching your videos and your long time subscriber. I am using this nested functions for long time, but now i learned how simple to do it, because in my way most of the times i forgot the syntax. thank you so much for showing this very simple way... I also touched that you used my country, Philippines in your example. Thanks that you know Philippines. Waiting for more tips and techniques.
Thank you for a detailed explanation of INDEX/MATCH 😊
Great example of battleship game! now I finally understand what index is for.👍
Great video Kevin, the advanced tip using ampersands is a game changer and is another reason to use Index Match instead of vlookup
You're simply the best Kevin ! 🙌🏼💯🙏🙂
Great stuff Kevin - I always look out for your trainings - keep it up
That's how the cookie crumbles! Good example of how to build up a complex formula! I use the same method.
thank you very much for this video. i was trying so hard to figure out how to do this and tried other functions but this one did it.
This is handholding. I am glad I found it on the day I needed it so much. Thank you.
Love this example, brought it all together with the Battleaship concept. There are only a few of you teachers whom explain it and I get it right away. I'm having "ah haa!" moments 😂
Very informative! Thank you!
Thank you Kevin, I am following your channel as a subscriber and getting to know lot many things about MS Office, I have learnt almost all the formulas from basic till INDEX, MATCH Funtion, and learning is helping me do better in my work. I have been using your tips and tricks for MS WORD, EXCEL, and Power Point as well as ZOOM and would be looking forward to use MS ACCESS. I would look forward to learn MS PROJECT as well. A BIG BIG THANK YOU AND GRATITUDE FROM VINAY NAGAONKAR FROM INDIA
Hi Kevin, your tutorial on this topic is so helpful...Thank you...keep up...
Thanks for the advanced tips
I like how you explain things. It would be helpful if you went over common errors more often.
hi.. thank you Kevin..been long time I want to learn this index match as an additional technique for me on my office lookup works in excel but I got stuck in vlookup as there is no chance for me to use such function. Now in the new company that I am in, I saw beforehand that in their excel report they are using index match (kinda anxiously overwhelmed upon first look at it) which prompt me to learn it by heart this time as I will really need it 100% for sure.
Thank you so much for that easy step by step explanation. It was indeed easy to learn. More power to your channel and hope you can reach out far to those working individuals like me who are struggling on some excel functions and techniques on how to work on it.
Thank you Kevin, excellent explanation of what appears to be complex function. Keep up the good work. (From: Ireland 🇮🇪)
The colors of our Flag is same as Yours india🇮🇳
The guy is out of this world as a teacher!
I had never used this combination of functions, but I need it for the MO-201 exam. This is a great explanation and demonstration, and all based on something that truly deeply matters in life - cookes! Me likey !!
Great to hear!
Best tutorial I’ve watched today
Thank you for this piece. Very helpful
Truly phenomenal. I am building Stock Analysis spreadsheets. My source data is coming from scraping websites using PowerBi and PowerQuery. The export from PowerBI is as individual csv files. Basically, I have 10 websites that I scrape data. That means 10 separate csv files per stock ticker. I use PowerShell to combine all of those csv’s into a single spreadsheet.
The problem is that each stock has slightly different metrics. Some have goodwill or dividends or some other metric specific to that industry. This means the tables range from say 20 rows to 27 rows depending on the stock.
I hard coded my analysis spreadsheet to look for a certain value or average function based on rows and columns. Well Revenue for Microsoft is on row 8 but for Apple it is on row 10. My average function on my data sheet is referencing row 8. It works for Microsoft but when I run it on Apple I get Liabilities instead.
With your help I can now tell it to find AAPL, Revenue, and then give me the next cell over or multiple cells in that row. Time to redesign my analysis sheet.
A sincere thank you.
Edit: Sub'd
2nd Edit: Doesn't work. I tried the index&matched advanced 2. Looks like you are referencing a table instead of the array. Why does it show Revenue [Cookie type] instead of A3:A30
Very helpful topic and well explained. Thanks Kevin
Thank you! That was extremely useful. I use this everyday
Great to hear!
Kevin thank you for this good presentation.
I have wanted to see about Index the second option of choosing areas. It is so useful to work with arrays. Can you help?
Thank you
u r the best, Kevin! Please continue your good work
🙂
Excellent. Thanks Kevin 👍
Great job explaining!!! i like what you did having the excel file to practice with it, as we learn way to teach!
Thanks a million Kevin, You just got a new Subscriber & admirer.
Love from Tanzania.
Xlookup is better. I hope you look it up.
Thank you. Your explanation is awesome 😊
I love your videos! I'm learning a lot from you.
wow. as always awesome video Kevin. good job
Great Video! Thank you so much! I think this the best tutorial out there!
Thank you Kevin..
for very useful topic 😊
It's really useful.thank you
Looking different basic & formula using in excel
You are an Excel champ Kevin! And hope I will be an excel champ too after learning from you 😀😁. Keep educating us mate!!
Best of luck!
Thank you man, this was very helpful.
Thank you for the great videos. They are the best on TH-cam so far. Just one little thing, if there are less zoom-ins and zoom-outs it would be even better so we can see the whole data set and the connections among cells.
Noted!
This is amazing Kevin!
Thanks this was quit easy to follow, was hoping that on the last example you would have shown how to come up with the drop-down list. Would you simply do it like the earlier example or would you have to copy, remove duplicates then utilize that as the drop-down list.
All the same will be surely using index and match on my next spreadsheet.
Thank you so much, it's helpful to me.