Hi. In this Excel tutorial, we’re going to go over how to use the INDEX function in Excel and end
with a couple of examples where we’ll use the INDEX – MATCH function combination.
The INDEX function returns a value from a specific location within a data set. So, let’s take a look at what this means. On my screen is
data containing a list of 5 associates and their Score. Let’s create an INDEX function that returns the score for the 4th associate.
Think of this as returning the score displayed in the cell at the intersection of the row where associate 4 meets the column with
the scores. These are also the 3 key pieces of information that the INDEX formula will need -
A set of data, a row, and a column where the result we want will intersect. We'll write =INDEX,
and the first thing we'll do is select our dataset. Next, we need to specify the row number within that dataset. So, in this case it'll be row
four because that's where the 4th associate's data is located.
And, next, we specify the column number - which, in this case, will be 2 because Score is located in the 2nd column of our dataset. And
press Enter. And we get back a result of 3. Now, this was pretty straightforward, but you likely won’t always know the number of where
the row or column you need is located in your data set.
So, if you recall from our tutorial on MATCH, the MATCH function, returns a value that represents
the location of a specific item within a selection – which is exactly what we need.
So, let’s look at how you can combine the MATCH function with the INDEX function to help you quickly lookup the information you
need. And, by the way, if you’re finding this tutorial helpful, don’t forget to Like the video and subscribe to the channel for even more
Excel tips. So, here, we have performance data by associate. We can also see which region each
associate works in. We’ll use the INDEX and MATCH functions to,
first, return the performance by time period for the associate and, then, the associate’s region. These 2 cells that are shaded are where the
variables will need to be entered - the associate name and the time period.
Now, because this will use what we type here to look through the data, it’s important that there are no misspellings. So, to help avoid
inputting errors, we’ll create a drop down list. This will both make it easier for users to see what they can
select and will limit the options that can be entered in the cell.
So, let's start with making a drop down for the associate names. So from the Data tab,
select Data Validation,
and then Lists, and, in Source, we'll select all the associate names. Also, FYI, my data is formatted as a Table. There are several
reasons why I like my data in Table format, but a key thing here is that, even though the Source data
is surrounded by $, by using a Table, any new names added to the Table, will automatically be added to the dropdown. And
this can be a huge help as associates get added to the team.
And, when done, click OK.
And now we have a drop down for our associate names.
And we'll do the same thing for Time Period.
And we'll select these time periods at the top
and click OK
So, we now have all the pieces of information we need to create our lookup using INDEX and
MATCH. So, let's select here an Associate and a Time Period
so that we can get a result right away after we input our functions. Here, we'll type =INDEX,
and the first thing we need to do is select our data, so I'm going to select
just the entire data part of our Table. And, notice how Excel updates this to read Table1. So, Excel assigns a reference to the various
parts of Tables and that's where this is coming from.
So, comma, and the next thing we need to specify is the row where the data for our associate will be found.
So, here's where we'll insert our first MATCH formula. So, we'll type MATCH
and we'll reference cell B1 because we need to match it to the store associate name.
Then we'll highlight our associate names. And, again, notice how this updated here to reference the
Associate column. So, just like with our drop down, this Table reference will help any new
names that may be added later auto populate into our lookup.
So, comma, and then we'll type 0 for an Exact Match,
close the parentheses. And, next, we need to note which column to reference for the Time Period. So, we'll insert another MATCH
function here, open parentheses, and, this time, we'll reference cell B2 with the Time Period.
And this will look up across here. Now, I could just select the Quarters and the Full Year, but I'm actually going to select the entire header
row - so, like before, this MATCH function
now references the Table headers which will also help if new time periods are added - comma, and, we'll again type 0 for an Exact
Match, we'll close the parentheses. Press Enter. And this gives us the performance results for our associate!
Now, if we'd wanted to, we could have used VLOOKUP for this since the data was to the right of what we were looking up. But you can
use INDEX - MATCH to lookup data to the left. So, let's lookup the Region so you can see.
So, we'll write =INDEX
and we can type Table1.
Then, for the row, we need to find which associate. So, we'll again use a MATCH function.
We'll reference the associate name, and for the lookup array, we'll select the associate names again, and 0 for an Exact Match. We close the
parentheses. Now, for the column, we need the column to bring back the Region. So, we can't use a MATCH function here because we
don't know which Region - that's what we're trying to get back. So, we'll the type 1, since we know that Region is
the first column in our dataset, and, when done, press Enter.
And, this let's us know that associate Emerson is in the South region. I hope you enjoyed this tutorial on how to use the INDEX function
in Microsoft Excel. If you did, please make sure to Like the video and don’t forget to subscribe to this channel. Thanks!