title
INDEX MATCH Excel Tutorial

description
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: https://1drv.ms/x/s!AmxrofZZlZ-whMguWNsN6AkHM4pGkw?e=O3CjoS - Learn the fundamentals of Excel in just 2 hours: https://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: https://youtube.com/playlist?list=PLlKpQrBME6xLYoubjOqowzcCCd0ivQVLY 🚩 Connect with me on social - LinkedIn: https://www.linkedin.com/in/kevinstratvert/ - Twitter: https://twitter.com/kevstrat - Facebook: https://www.facebook.com/Kevin-Stratvert-101912218227818 - TikTok: https://www.tiktok.com/@kevinstratvert 🔔 Subscribe to my YouTube channel https://www.youtube.com/user/kevlers?sub_confirmation=1 🎬 Want to watch again? Navigate back to my YouTube channel quickly http://www.kevinstratvert.com 🛍 Support me with your Amazon purchases: https://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

detail
{'title': 'INDEX MATCH Excel Tutorial', 'heatmap': [{'end': 846.517, 'start': 809.332, 'weight': 0.846}], 'summary': 'Learn to use index match in excel to look up data, including finding revenue for chocolate chip cookies in india, and understand the powerful lookups and practical examples resulting in $38 million for fortune cookies and $33 million for chocolate chip cookies.', 'chapters': [{'end': 43.054, 'segs': [{'end': 43.054, 'src': 'embed', 'start': 0.149, 'weight': 0, 'content': [{'end': 1.55, 'text': 'Hi everyone, Kevin here.', 'start': 0.149, 'duration': 1.401}, {'end': 8.314, 'text': 'Today I want to show you how you can use index match in Microsoft Excel.', 'start': 1.79, 'duration': 6.524}, {'end': 15.298, 'text': 'So what is index match and why would you use it? You can use index match to look things up.', 'start': 8.894, 'duration': 6.404}, {'end': 21.142, 'text': "Let's say, for example, at the Kevin Cookie Company, we have revenue data in Excel.", 'start': 16.138, 'duration': 5.004}, {'end': 31.491, 'text': 'And I want to answer the question, how much revenue did we earn for chocolate chip cookies in India? I can use index match to answer that question.', 'start': 21.883, 'duration': 9.608}, {'end': 35.055, 'text': "Like I've always said, there's a lot of money in the cookie business.", 'start': 32.192, 'duration': 2.863}, {'end': 41.214, 'text': "If you want to follow along today, I've included sample data in the description down below.", 'start': 36.152, 'duration': 5.062}, {'end': 43.054, 'text': "Alright, let's check this out.", 'start': 41.234, 'duration': 1.82}], 'summary': 'Kevin demonstrates using index match in excel to look up revenue data, providing an example of analyzing sales for chocolate chip cookies in india.', 'duration': 42.905, 'max_score': 0.149, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/6JhbY8Mku1A/pics/6JhbY8Mku1A149.jpg'}], 'start': 0.149, 'title': 'Using index match in excel', 'summary': 'Demonstrates how to use index match in microsoft excel to look up data, specifically to find the revenue earned for chocolate chip cookies in india, with sample data provided for practice.', 'chapters': [{'end': 43.054, 'start': 0.149, 'title': 'Using index match in excel', 'summary': 'Demonstrates how to use index match in microsoft excel to look up data, specifically to find the revenue earned for chocolate chip cookies in india, with sample data provided for practice.', 'duration': 42.905, 'highlights': ['Index match in Microsoft Excel is used to look up data, such as finding the revenue earned for specific products in certain locations, like chocolate chip cookies in India.', 'Sample data is included in the description for practice and demonstration purposes.']}], 'duration': 42.905, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/6JhbY8Mku1A/pics/6JhbY8Mku1A149.jpg', 'highlights': ['Index match in Microsoft Excel is used to look up data, such as finding the revenue earned for specific products in certain locations, like chocolate chip cookies in India.', 'Sample data is included in the description for practice and demonstration purposes.']}, {'end': 915.18, 'segs': [{'end': 90.068, 'src': 'embed', 'start': 43.234, 'weight': 0, 'content': [{'end': 50.637, 'text': 'Here I am in Microsoft Excel and Index Match should work in just about any version of Microsoft Excel.', 'start': 43.234, 'duration': 7.403}, {'end': 53.498, 'text': "It doesn't require a Microsoft 365 subscription.", 'start': 51.117, 'duration': 2.381}, {'end': 60.879, 'text': "Now, I mentioned that we're going to use index match, and it's actually two separate functions.", 'start': 54.978, 'duration': 5.901}, {'end': 67.46, 'text': 'You have the index function and then you also have the match function, and when you bring them together,', 'start': 61.359, 'duration': 6.101}, {'end': 70.741, 'text': 'you can pull off some pretty powerful lookups in Excel.', 'start': 67.46, 'duration': 3.281}, {'end': 76.162, 'text': "And to make sure we understand how it works, we're going to start with the index function.", 'start': 71.521, 'duration': 4.641}, {'end': 82.323, 'text': "If you've ever played the game Battleship before, the index function is pretty similar to that.", 'start': 76.862, 'duration': 5.461}, {'end': 90.068, 'text': "In the game of Battleship, you try to take out your opponent's ships by calling out different coordinates on the board.", 'start': 83.243, 'duration': 6.825}], 'summary': 'Index match in microsoft excel is a powerful lookup tool, not requiring a microsoft 365 subscription, bringing together the index and match functions for powerful lookups.', 'duration': 46.834, 'max_score': 43.234, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/6JhbY8Mku1A/pics/6JhbY8Mku1A43234.jpg'}, {'end': 361.738, 'src': 'embed', 'start': 333.89, 'weight': 1, 'content': [{'end': 336.491, 'text': "Like index, it's also a pretty simple function.", 'start': 333.89, 'duration': 2.601}, {'end': 344.113, 'text': "Next, we're going to combine the index and the match function, and we're going to be able to pull off some pretty powerful lookups.", 'start': 337.151, 'duration': 6.962}, {'end': 348.734, 'text': "Down below, let's click into the index and match simple worksheet.", 'start': 344.693, 'duration': 4.041}, {'end': 355.436, 'text': "On this next sheet, we're going to bring together index and match, and we're going to pull off our first lookup.", 'start': 349.214, 'duration': 6.222}, {'end': 361.738, 'text': 'On this sheet, I have all of our different cookie types at the Kevin Cookie Company and the associated revenue.', 'start': 356.336, 'duration': 5.402}], 'summary': 'Combining index and match functions for powerful lookups and first cookie revenue lookup.', 'duration': 27.848, 'max_score': 333.89, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/6JhbY8Mku1A/pics/6JhbY8Mku1A333890.jpg'}, {'end': 558.994, 'src': 'embed', 'start': 531.319, 'weight': 5, 'content': [{'end': 537.888, 'text': 'In the intro, I mentioned that I want to find out how much revenue do we pull in for chocolate chip cookies in India.', 'start': 531.319, 'duration': 6.569}, {'end': 543.69, 'text': 'and here I have a table with all of our different cookie types and all of our different markets.', 'start': 538.468, 'duration': 5.222}, {'end': 549.551, 'text': 'And right here I could see chocolate chip cookies in India drive in about 32 million dollars.', 'start': 544.23, 'duration': 5.321}, {'end': 553.172, 'text': 'But how can we use index match to get this same result?', 'start': 549.971, 'duration': 3.201}, {'end': 558.994, 'text': "Well, let's start out by entering in the match formulas and then we'll figure out what the revenue is using index.", 'start': 553.572, 'duration': 5.422}], 'summary': 'Chocolate chip cookies in india generate $32 million in revenue, utilizing index match for calculation.', 'duration': 27.675, 'max_score': 531.319, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/6JhbY8Mku1A/pics/6JhbY8Mku1A531319.jpg'}, {'end': 737.433, 'src': 'embed', 'start': 693.524, 'weight': 4, 'content': [{'end': 701.629, 'text': "but when you break it down and you start with the matches and you work your way out to the index, it's actually very easy to use index match.", 'start': 693.524, 'duration': 8.105}, {'end': 706.771, 'text': "The really nice thing here now is let's say I want to change any of these different values.", 'start': 702.369, 'duration': 4.402}, {'end': 710.093, 'text': "So instead of chocolate chip, let's say I want to look for fortune cookies.", 'start': 706.872, 'duration': 3.221}, {'end': 714.756, 'text': "There you'll see that in India it automatically updates the revenue for that.", 'start': 710.654, 'duration': 4.102}, {'end': 719.903, 'text': 'Now, if I want to get really fancy, I could even insert dropdown lists here.', 'start': 715.58, 'duration': 4.323}, {'end': 728.067, 'text': "So instead of just typing in a value, let me go up to data up on the top tabs and I'll go to the option that says data validation.", 'start': 720.363, 'duration': 7.704}, {'end': 732.13, 'text': "Right here, I'll select list instead of any value.", 'start': 728.928, 'duration': 3.202}, {'end': 735.692, 'text': 'And for my source, let me select all of the different cookie types.', 'start': 732.45, 'duration': 3.242}, {'end': 737.433, 'text': "then I'll click on OK.", 'start': 736.272, 'duration': 1.161}], 'summary': 'Using index match allows for easy data manipulation and updating, with the added feature of inserting dropdown lists for data validation.', 'duration': 43.909, 'max_score': 693.524, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/6JhbY8Mku1A/pics/6JhbY8Mku1A693524.jpg'}, {'end': 846.517, 'src': 'heatmap', 'start': 809.332, 'weight': 0.846, 'content': [{'end': 810.693, 'text': 'For the lookup value.', 'start': 809.332, 'duration': 1.361}, {'end': 817.156, 'text': "I want to look up birthday cake and I'll insert an ampersand and then I'll also select the Philippines.", 'start': 810.693, 'duration': 6.463}, {'end': 819.697, 'text': 'so I want to look up both of these different values.', 'start': 817.156, 'duration': 2.541}, {'end': 827.862, 'text': "For the lookup array here I'll select this entire column right here and, just like we did with the lookup value,", 'start': 820.478, 'duration': 7.384}, {'end': 833.245, 'text': "I'll insert an ampersand and then I'll select this entire column with all of the markets.", 'start': 827.862, 'duration': 5.383}, {'end': 837.789, 'text': "then I'll click on OK and it gives me a three back.", 'start': 834.165, 'duration': 3.624}, {'end': 846.517, 'text': 'so it finds birthday cake and the Philippines or this combination on row number three, and now we can create an index function,', 'start': 837.789, 'duration': 8.728}], 'summary': 'Using the lookup and array to find a combination, yielding a result of three.', 'duration': 37.185, 'max_score': 809.332, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/6JhbY8Mku1A/pics/6JhbY8Mku1A809332.jpg'}], 'start': 43.234, 'title': 'Using index match and functions in excel', 'summary': 'Explains the usage of index match in microsoft excel for powerful lookups like the game of battleship, and demonstrates the index and match functions with practical examples, resulting in $38 million for fortune cookies and $33 million for chocolate chip cookies in india.', 'chapters': [{'end': 175.209, 'start': 43.234, 'title': 'Using index match in microsoft excel', 'summary': 'Explains the usage of index match in microsoft excel, highlighting its capability to perform powerful lookups and the similarity of the index function to the game of battleship. it demonstrates the step-by-step process of using the index function with practical examples and prompts in excel.', 'duration': 131.975, 'highlights': ['The index match in Microsoft Excel does not require a Microsoft 365 subscription and can be used in any version of Excel. This emphasizes the accessibility and versatility of the index match function, making it available to a wide range of Excel users.', 'The index function, when combined with the match function, enables powerful lookups in Excel. It showcases the capability of the index match combination to perform advanced and effective data lookups in Excel.', 'The index function is explained using the analogy of the game Battleship, making it easier to understand. The analogy simplifies the concept of the index function, aiding in better comprehension for the audience.']}, {'end': 915.18, 'start': 176.07, 'title': 'Index and match functions in excel', 'summary': 'Demonstrates how to use the index and match functions in excel to perform specific cell lookups, with examples showing the retrieval of revenue data for specific cookie types and markets, resulting in $38 million for fortune cookies and $33 million for chocolate chip cookies in india.', 'duration': 739.11, 'highlights': ['The chapter demonstrates how to use the index and match functions in Excel to perform specific cell lookups. The chapter introduces the usage of the index and match functions in Excel to perform specific cell lookups, enabling users to retrieve specific data from arrays or tables.', 'The retrieval of revenue data for specific cookie types and markets, resulting in $38 million for fortune cookies and $33 million for chocolate chip cookies in India. The example showcases the usage of index and match functions to retrieve revenue data, demonstrating that fortune cookies generated almost $38 million and chocolate chip cookies in India resulted in revenue of almost $33 million.', 'The ability to insert dropdown lists for more dynamic data selection and interactive dashboards. The demonstration includes the use of dropdown lists for dynamic data selection, allowing users to build interactive dashboards for enhanced data visualization and analysis.']}], 'duration': 871.946, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/6JhbY8Mku1A/pics/6JhbY8Mku1A43234.jpg', 'highlights': ['The index match in Microsoft Excel is accessible to a wide range of users.', 'The index match combination performs advanced and effective data lookups in Excel.', 'The analogy of the game Battleship simplifies the concept of the index function.', 'The chapter introduces the usage of the index and match functions in Excel for specific cell lookups.', 'The example showcases the usage of index and match functions to retrieve revenue data.', 'Fortune cookies generated almost $38 million and chocolate chip cookies in India resulted in revenue of almost $33 million.', 'The demonstration includes the use of dropdown lists for dynamic data selection.']}], 'highlights': ['The index match combination performs advanced and effective data lookups in Excel.', 'The example showcases the usage of index and match functions to retrieve revenue data.', 'The demonstration includes the use of dropdown lists for dynamic data selection.', 'Index match in Microsoft Excel is used to look up data, such as finding the revenue earned for specific products in certain locations, like chocolate chip cookies in India.', 'Sample data is included in the description for practice and demonstration purposes.', 'The index match in Microsoft Excel is accessible to a wide range of users.', 'The analogy of the game Battleship simplifies the concept of the index function.', 'The chapter introduces the usage of the index and match functions in Excel for specific cell lookups.', 'Fortune cookies generated almost $38 million and chocolate chip cookies in India resulted in revenue of almost $33 million.']}