INDEX MATCH Excel Tutorial

hi everyone kevin here today i want to show you how you can use index match in microsoft excel so what is index match and why would you use it you can use index match to look things up let's say for example at the kevin cookie company we have revenue data in excel 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 like i've always said there's a lot of money in the cookie business if you want to follow along today i've included sample data in the description down below all right let's check this out here i am in microsoft excel and index match should work in just about any version of microsoft excel it doesn't require a microsoft 365 subscription now i mentioned that we're going to use index match and it's actually two separate functions you have the index function and then you also have the match function and when you bring them together you can pull off some pretty powerful lookups in excel and to make sure we understand how it works we're going to start with the index function if you've ever played the game battleship before the index function is pretty similar to that in the game of battleship you try to take out your opponent's ships by calling out different coordinates on the board so you'll call out a row and a column and hopefully you hit someone's ship the index function is very similar to that if we scroll down just a little bit on the first sheet here you'll see our very own cookie company board here i have rows and i also have columns and here within the board i have a cookie now i want to use the index function to tell me what's in this specific cell and it's a cookie so let's go down just a little bit and we'll start entering in the formula i'll select this cell right here and then let's go up to the function helper by clicking on insert function this opens up a prompt where i can insert a function and let's start with index here i'll type in index click on go and let's select this option right here this opens up another prompt and let's go with the first one here that says array row num column num and then click on ok this opens up a prompt where we can specify the arguments that we want to pass into this function and first it asks us for the array so what is the array well here i'll move this over and let's scroll up again and here's that board of the game battleship the array is all of the different possible areas that you can fire at so here if i come down to my board down here i'll select all of these different cells these are all the different possible cells that could contain a cookie and right here it asks me to specify a row number and a column number now once again i want to know what's in this specific cell so this is row number three so i'll type in a three and then it's also column number three right here so for column i'll type in a three and look at that when i click on ok it gives me a cookie back so it tells me that there is a cookie in this specific cell and that's all the index function does you give it an array or basically your board area then you specify a row and a column that you want back and look at that we got a cookie how delicious now that we've looked at how index works let's take a look at how match works down below click into the next worksheet called match next up we have the match function and this is the second part of index match with the match function you can find out what row or column something is in so here for example i have a table with all the different cookie types that we carry here at the kevin cookie company and let's say i want to know what row is sugar in now here you can see that i have six rows in this table and sugar is in the sixth row now that's pretty easy to see but maybe you have a lot more data and that's where a match can help you so right up here let's type in the match formula and once again to make this as easy as possible let's click on the insert function icon this opens up the insert function prompt let's type in match and then click on go next click on the match function this opens up a prompt where i can specify the arguments for this function and first it wants to know what is the lookup value now once again i want to find out what row is sugar in so the lookup value is sugar and i'll simply select this cell now i could also simply type sugar in but i'll reference the cell instead down below i need to specify the lookup array so i want to find sugar in this set of cookie types i want to find out what row is it so i'll select this as the array down below i could also select a match type if i want it to be exact or not exact but i'm just going to leave it set to the default of exact now right over here i can already see the outcome of this function and it tells me it's number six so it's in row number six i'll click on ok and here too i see that it's in row six now because i simply reference this cell i could also type in fortune and when i hit enter here tells me that fortune's on row number four so that's how the match function works like index it's also a pretty simple function 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 down below let's click into the index and match simple worksheet on this next sheet we're going to bring together index and match and we're going to pull off our first lookup on this sheet i have all of our different cookie types at the kevin cookie company and the associated revenue now i want to write in a formula that'll tell me the revenue for a specific type of cookie and index match will help us here first i want to find the position of sugar in this table and just like we did in the previous example we can use match to do this and it'll be very similar to what we just did i'll click into this cell and let's click on insert function this opens up the function arguments prompt and once again i want to find out what row is sugar on for the lookup value i'll select sugar and for the lookup array i'll select all of my different cookie types and here it shows me that it's in row number six i'll click on ok and here it shows me a six now that we know that sugar is on row number six i want to know the revenue for row number six and this is where we can use the index function here i'll click on the insert function icon and here let's select index and then click on ok once again click on the first option and click on ok again this opens up the function helper and for index this is the array so i want to know a specific revenue amount in this selection next it asks me for a row number and we found that sugar is on row six so here i can enter in a six and then click on ok and here we see that the revenue for sugar is just over 17 million now of course when i entered in the index formula up here i hard coded in row number six but i want to use match within the index formula so here i'm going to click up where i entered in the match formula to find out the row that sugar is on and here i'll copy the formula next i'll click down into my index formula and instead of hard coding in the six let me paste in my match formula and so this will give me the row that sugar is on within the index formula i'll hit enter and i still get the same result but now the neat thing is here maybe instead of looking at sugar cookie revenue let me change it to fortune and hit enter and look at that it automatically updates to show me that fortune cookies are actually our second highest revenue driving cookie with almost 38 million dollars if i go back above i'll click into the index match formula and if we look at the formula here here it's basically saying b6 through b11 this is the game board just like in the game battleship and then match is telling me what specific row we want to look at and here fortune cookies are in row four so here it returns this value back hopefully you're starting to understand how index match works and you can pull off some pretty fancy lookups next let's click into index and match advanced in the intro i mentioned that i want to find out how much revenue do we pull in for chocolate chip cookies in india and here i have a table with all of our different cookie types and all of our different markets and right here i could see chocolate chip cookies in india drive in about 32 million dollars but how can we use index match to get this same result well let's start out by entering in the match formulas and then we'll figure out what the revenue is using index first let's find out what row chocolate chip is on here i'll click on the function helper and once again let's use match here i'll click on match and then click on ok for the lookup value i'll select chocolate chip and for the lookup array i'll select all of the different cookie types then i'll click on ok so here we see that chocolate chip is on row number three next let's figure out what column india is in also by using match here i'll click on insert function and once again let's select match this opens up the function prompt here for the lookup value let's select india for the lookup array i'll select all the different markets that we operate in then i'll click on ok so here i can see that india is in column number one now that we know both the row number and the column number we are ready to play the game battleship and we can now insert the index function right up here under revenue let's type in equals index open the parentheses and we need to specify the game board or the array here i'll select this entire area right here i'll insert a comma and now i need to specify the row number and the row number is three so for now i'll hard code it to three i'll insert another comma and then i need to specify the column number and this is in column one so i'll press a one and then close the parentheses so here i see that in india we made almost 33 million dollars on chocolate chip cookies now just like before i don't want to hard code values in the index function instead i want to use match to tell me what these values are so here i'll click into chocolate chip and here i'll copy this formula this will tell me what the row is here i'll click into index and instead of entering in three i'll paste in the match formula and here it continues to work just like it did before next let's click into india and here i'll copy the match formula here i'll click back into this one and instead of hard coding the one here i'll paste in the match formula for the column and when i hit enter this works just like we expect it to now if i click into here and we look at the formula it looks pretty complex 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 the really nice thing here now is let's say i want to change any of these different values so instead of chocolate chip let's say i want to look for fortune cookies there you'll see that in india it automatically updates the revenue for that now if i want to get really fancy i could even insert drop down lists here 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 right here i'll select list instead of any value and for my source let me select all of the different cookie types then i'll click on ok now i could do the same for the market as well here i'll click on data validation i'll set it to list and for the source i'll select all of the different markets that we're in and then i'll click on ok so now i can use a drop down list to choose any of my cookie types so let's look at snickerdoodle in the philippines and here it looks like that's a little over 7 million and to verify that here we have the philippines and snickerdoodle and it's a little over 7 million with index match and drop down lists you can start building some very nice interactive dashboards by now you should have the hang of how index match works but i want to leave you with one last example let's click into the worksheet called index and match advanced 2. on this sheet you'll notice that the table is structured a little bit differently here i have the cookie type in one column the market in another column and then the revenue i want to find out how much revenue did we earn on birthday cake cookies in the philippines so how do we figure this out well just like we've been doing all along let's start with the match function once again let's go up to insert function and here let's select match for the lookup value i want to look up birthday cake and all insert an ampersand and then i'll also select the philippines so i want to look up both of these different values for the lookup array here i'll select this entire column right here and just like we did with the lookup value i'll insert an ampersand and then i'll select this entire column with all of the markets then i'll click on ok and it gives me a three back so it finds birthday cake and the philippines or this combination on row number three and now we can create an index function just like we did before to insert the index function let's click on the insert function icon above select index select the first option and now we can start filling out the arguments once again for the array let's select all of the revenue information right here and for the row number here i can reference this cell with number three then i'll click on ok so here it looks like birthday cake in the philippines has earned about 26 million revenue now just like we did before here i can take the match function i'll copy this and i can now paste it in place of this reference within my index function when i hit enter now here you'll see that i can get the revenue back for multiple different criteria here again just like i did before i can change any one of these values so maybe i type in india and this will automatically update the revenue here too i can also insert drop down lists if i want to make it more dynamic alright well let me know down below in the comments will you be using index match in your next spreadsheet if you enjoyed this video please consider subscribing and i'll see you next time [Music] you

Channel: Kevin Stratvert

Views: 37,134

Rating: 4.9793415 out of 5

Keywords: kevin stratvert, index match, index, match, index match in excel, excel index match, excel, microsoft excel, excel index, excel match, index & match, tutorial, index match tutorial, index match easy, simple, how to, index and match, vlookup, lookup, xlookup, hlookup, function, functions, beginners, beginner, excel index match tutorial, formula, how to use, kevin, stratvert, lookups, how to use index match, using index match, how to do lookup, index match function, index match functions, 365

Id: 6JhbY8Mku1A

Length: 15min 29sec (929 seconds)

Published: Wed Jul 14 2021