INDEX MATCH Excel Tutorial
Vložit
- čas přidán 22. 07. 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 CZcams channel
czcams.com/users/kevlers?...
🎬 Want to watch again? Navigate back to my CZcams 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 - Věda a technologie
🏫 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: czcams.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!!
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.
By far the best Index/Match tutorial on CZcams. 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 😉
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.
Thank you, every time I can't work out how to do something, I find your excellent tutorial and you explain it in a way I can understand.
Can we all appreciate Kevin's hardwork and dedication?
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! 😊👍
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.
Excel has so many features. It's an amazing powerful program. You explain things with ease. Thank you!
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.
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!!!!!
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. 😃
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!
"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 really like the introducuton of Kevin Hi Everyone Kevin Here Today..
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!!
Best index & match tutorial on CZcams. Period. Thanks a mill.
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!
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
Man you´ve got a gift for explaining - very clear and concise
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 ☆
0:42 There is always something special I would like to hear. This time the way you said "Alright" was nice.
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 😂
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.
Amazing Sir!!!!! the best part is that you upload every day!!!!! You are the best!!
Very Useful While Working
I thoroughly enjoyed this one. I've had trouble understating indexing and matching. You broke it down nicely
Wow! Excel is crazy! I have been watching lots of your Excel videos lately. Thanks so much Kevin.
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!
You are indeed the best Excel teacher Kev! Wow! Thank you!
The best index match explanation on CZcams
I use Index & Match all the time. But the last tip was new for me. 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
Yes, I often use Index Match function. Thank you so much for the presentation.
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.
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?😅
Great video the easiest and most comprehensive I've seen on index match yet, Yay I can finally easily do INDEX MATCH
Thank you for helping me impress my boss and coworkers on my first day!!!! This was ridiculously helpful
I agree with Martin of Aus - best comprehensive instruction I've found yet!!! Thank you!
Your pedagogical approach is outstanding!!!!!
Best “explanation” of index / match so far.
Great video, Kevin! I share your videos with my students as part of learning material. Thanks!!
Thank you so much for your videos, they are way better and easy to understand than my teacher's explanations.
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
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.
I always resisted Index Match. This breakdown was excellent!
This is by far the best way to explain! Thank you 🙏🏻
one of the best explanations and super best breakdown of the formula, otherwise it was hard and confusing to understand.
Great opening to set the scene on the basics... makes it really easy to follow!
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 ?
Thank you Kevin. You have a gift for teaching.
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
Thank you so much for the Battleship analogy for Index and Match! This finally makes sense to me!
Hi Kevin, i can't find enough words to say thank you for this amazing video, you are the best bro ... Love you
I like how you explain things. It would be helpful if you went over common errors more often.
I was stuck the whole day, didn't know the ampersand and how to check 2 values at once. Thank man!
That's how the cookie crumbles! Good example of how to build up a complex formula! I use the same method.
I like the way you explain things so easily, Thanks Kevin
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!
I will be using INDEX MATCH now. Thanks!
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 are a good teacher. Perfect.
Your intros always become better!!!! Hi everyone, Kevin here 🔥🔥🔥😁😁😁
Wow! Special mention our country, the Philippines. 😅 I really enjoyed watching all your videos. I even copied your intro, the "hi everyone! Dexter here!", In my channel about legal fees in the Philippine Judiciary. Im just a beginner. Just want to help my colleagues in the judiciary courts and the public to have a uniform assessement and computation of fees for all kinds of cases. I used OBS and Davinci Resolve 17. All because of you idol! Stay safe! Thank you!🙏
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.
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.
Awesome Explanation !!
Thank you for a detailed explanation of INDEX/MATCH 😊
This is handholding. I am glad I found it on the day I needed it so much. Thank you.
Wonderful!! And so clearly explained, thank you!!!
Thanks for presented complex function in a simple way
This was extremely easy to understand. Thanks, Kevin!
Great stuff Kevin - I always look out for your trainings - keep it up
Wow 😳 why didn't I know this before.. such great help with ease ✅.. thanks Kelvin 👏
You're simply the best Kevin ! 🙌🏼💯🙏🙂
Thankyou for breaking it down and simplifying it.
Excellent tutorial! Easy explanation. Thank you.
Thank you so much for all your videos. Super helpful and insightful.
I have an issue ,and I’m no pro at Excel. Hoping this is an easy fix. For a pro like yourself.
At work I have a sign out sheet. I have a worksheet with drop-downs for items/ qty / description.
On a separate sheet I have prices I manually enter.
Is there away to combine these on one spreadsheet to automatically add the price to a specific item when I choose it in the drop down?
Again I know this is probably basic. Just hoping to find an answer. Thank you, and have an awesome day.
Best tutorial I’ve watched today
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!
Love the tutorial. That merged cell in the Battleship sheet is funny though. Don’t think I didn’t notice :).
Hey Kevin, great training videos. Thank you! I am wondering if you have a video that shows how to connect Excel to Google Sheets and not Google Sheets to Excel. I work with an agency that likes to save all their files in Google Drive. However, our laptops are equip with Excel and we preferred to use Excel to create our workbooks but we would like the ability to update Excel and then Google Sheets will self update. Your help is greatly appreciated. Thank you! JC
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!
Great tutorial. On your last example/tab, how could you display the total revenue for a particular cookie type (without soeting and adding subtotal sums). Thanks much!
Kevin keep up the great work my friend keep up the great work thank you for these videos man God bless you
Thank you so much for sharing so much information yes I need to start using Index Match for my job 👍👍🤩🤩🤩
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
Thank you for the great videos. They are the best on CZcams 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!
Hi Kevin, your tutorial on this topic is so helpful...Thank you...keep up...
Thanks a million Kevin, You just got a new Subscriber & admirer.
Love from Tanzania.
Xlookup is better. I hope you look it up.
best explanation on youtube by far
Using index match as I write this comment. Advanced example 2 is exactly what I was looking for because "and" function did not help. Thank you.
Good morning Kevin, I find your channel immensely helpful, please advice if you provide one on one training on V, H, X Look up, Index, Match and If formulas, I am very interested. Thank You, Miguel M. Gaitan
Very informative! Thank you!
Thanks for the advanced tips
So easy to understand, ma shaa ALLAH.
Thank you..
Thanks Kevin for the video
Excellent. Thanks Kevin 👍