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? 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 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 okay and here too I see that it’s in row six. Now because I simply referenced 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 okay 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 okay. Once again click on the first option and click on okay 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 okay 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 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 can 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 okay. 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 okay. 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 okay. 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 expected 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, and then I’ll click on okay. 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 okay. 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 seven million, and to verify that, here we have the Philippines and snickerdoodle and it’s a little over seven 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 I’ll 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 okay, 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 okay. 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. All right, 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.