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.