Lookup like a PRO with INDEX and MATCH

Data Translates
219 followers
Hello, my little Cellmateys. Welcome back to my channel.
In this tutorial, I'm going to show you how you can use the index and the match function,
combine them together to create a magical lookup formula.
Not only is this formula powerful and flexible, it basically means you can do away with VLOOKUP
for good. Because if you've been using VLOOKUP for a while, then you'll know that there are many
limitations to it. And all of those issues are cast aside when we progress on to using
index and match to perform our lookups. So we're going to take a look at a couple of basic
examples. I'm going to throw in some data validation and some named ranges as a little
bonus. So if this sounds like something that you're going to be interested in, then please
keep watching. So first things first, I'm going to have to put on my glasses for this one because
I've run out of contact lenses. Hopefully the ring light reflecting in them isn't going to
be too distracting for you. So let's take a look at our first example. Now, if you take a look at
this worksheet, I have some basic data on here. It's not a huge data set. It's fairly small.
And this might be something like some HR information. So we have the employee ID in
column A. We then have the employee name, their job title, department, manager, and their salary.
And what I'm aiming to do here with a lookup is I want to be able to type in or select from a
drop-down list the employee ID number. And I want Excel to return to me the name, the job title,
and the salary. Sounds straightforward enough. Now, I could do this using a VLOOKUP, but we're
going to use INDEX and MATCH. Now, you'll often hear INDEX and MATCH used together in the same
sentence. They are a very popular combination of functions. But they are individual functions in
their own right. If you jump up to the formulas tab and go to the lookup and reference group,
this is where you'll find them. We have INDEX in there, and then we have MATCH. So let's first
start out by taking a look at these functions individually, because that's going to help
illustrate to you why they're so powerful when used together. So let's click in cell J7. I'm
going to type in equals, and let's look at INDEX first of all. Now, INDEX has two different ways
of constructing this formula. And I would say that 90% of the time, you're going to use the first one.
Now, the first argument there is the array. Now, the way that I like to remember this is the array
is the range of cells that contains your answer. So what am I looking for here? I'm looking for
the name. So my array is going to be where that answer is going to be found, which is this name
The next argument is row number. So I need to tell Excel the row number of the value I want
to return. So maybe I want to return the name Mike Young. The row number for Mike Young is going to
be three. And remember, when we're talking about row number in this context, we mean the row number
of the range that we've selected. So this is the third entry down in my range, as opposed to
the third row of the spreadsheet. Now, you'll also see that there is an optional third argument here
of column num. I'm not going to go into that at the moment. Let's just stick to row number,
close the back bracket, hit enter, and we get the result of Mike Young returned. Now,
that is all well and good. And because our data set is very small, it's very easy for me to just
count down to find that row number. But imagine if you have a spreadsheet that contains hundreds
or even thousands of rows of data. Are you really going to want to be sitting there counting down
until you find your entry? No, you're not. So we want to find a way of working more efficiently
by automating the finding of this row number. So with that in mind, let's take a look at what
the match function does. Now, before we do match, I'm actually going to type an employee ID in here.
So let's just take this first one. I'm just going to copy and paste that in here for this example.
So let's see what match does. The first argument for match is going to be the lookup value.
So I could say to match, look up this employee ID in J5, look it up in this array, and do an
exact match. I want Excel to exactly match that employee number in the lookup array. So I've got
Let's close the bracket down, hit enter. It gives us a result of one. So it's telling me that this
employee is in the first row. So essentially what we've done there is we've automated the finding
of the row number. So now that we have that, we can basically combine index and match together
to perform a really powerful and flexible lookup. Now, before we do this, I'm going to make my life
a little bit easier by adding the employee IDs into a data validation drop-down list,
simply so I don't have to keep typing them in every single time. And I use data validation
drop-down lists all the time. I think they're so helpful. If you've never created one before,
you'll find the option on the data tab in the data tools group. Click the drop-down,
data validation. We want to create a list. And the source for our list is going to be
Click on OK. I now have my little drop-down arrow and it's very easy for me to select whichever
employee I'm interested in. So now let's go in and pull through the name of this employee.
So we're going to type in equals index, open our bracket. Remember the array is the range
that contains your answer. So I'm looking for the name. So this is going to be my array,
comma. We then need the row number. Remember that match formula automates the finding of that row
number. So we're going to go straight into match. Our lookup value is going to be whatever we have
in J5, comma. Where are we going to look up that employee ID? In the employee ID range.
And we're doing an exact match. I want it to exactly match that number in the table. Let's
close off our match formula. Let's close off our index formula. Hit enter and we get our result.
Let's check to make sure this works. If I select a different employee,
it updates. So this is a very nice dynamic way of working. Let's do it one more time so we can
practice. Equals index, open bracket. This time we're looking for the job title. So our array is
which is going to be in the job title range. We want to automate the finding of the row number
by using match. Our lookup value is going to be what we have in J5. We want to look up the employee
ID number in the employee ID range. And we want to do an exact match. Close off our match. Close
off our index. Hit enter and we can now see that Stephen Martinez is a HR manager. Let's check to
make sure that's working. Let's choose something else. There we go. Now I could make this even
easier on myself. In the two examples I've shown you, each time I've been going in and I've been
selecting the different ranges to include in my formula. And that's absolutely fine. You can carry
on doing that. And with a small data set, it's not too much of a hassle. But again, if you have a
large data set, sometimes it's a bit easier and more efficient to use named ranges in your formula
instead. And if you're not sure what a named range is, basically you can select any range of cells
and give it a meaningful name so that it's easy to identify. A formula that contains named ranges
is a lot easier to understand than one that just has cell references. So let's name all of these
ranges. So I want to name this range just here employee ID, this range called name, this range
job title, so on and so forth. So a quick way of doing this is you can select the ranges that you
want to name. I'm going to do the whole lot. Why not? Up to that formulas tab and in the defined
names group in the middle there, I'm going to say create name from selection. I want to use the top
row as the name for my ranges. Click on OK. And now if I click the drop down in the name box,
you can see there are all of my ranges. If I select manager, that is that range of cells.
So now I can use these names in my formula instead. So this time we're going to use index
and match to return the salary. Our first argument is the array. So instead of selecting salary,
I'm going to use my named range. You can do this in a couple of ways. You can start to type it in
and IntelliSense should pick it up as you can see it has underneath. I can press the tab key
and it selects that range. We now need to use our match to automate the finding of that row.
Our lookup value is going to be whatever is in J5. Our lookup array is going to be the employee ID.
Now again, I can use my named range here. I can start to type it in or I can press the F3 key,
which is going to pull up any named ranges that I have in my workbook.
So let's select employee ID. Click on OK. Comma. I want to do an exact match. Let's close off our
match. Close off our index. And there we get our result. Let's change our employee and make sure
that everything updates nicely, which I can see that it does. So that is how you can perform a
very basic index and match using data validation lists and named ranges. Before we leave this
lesson, I want to show you one more example. This time we're going to do a two way lookup.
So let's take a quick look at our data. I have some travel companies listed in here and then
I have their sales for January, February, March of Q1. What I basically want to do here is I want
to be able to select the company from a dropdown list and the month from another dropdown list
and have it return the corresponding sales value. So let's create our data validation
dropdown list first of all. I'm going to click in cell G6, up to data, and we're going to select
data validation. We're going to create a list and the source for our list is going to be the company
names and click on OK. So now I have all of those listed in there. Let's do exactly the same thing.
We're going to create another list, but this time we're going to do it for the months. So our source
is going to be this cell range. Click on OK and now I can list out those months. Now we're going
to construct a two way index and match formula. So let's type in index, open our bracket. Now
remember our array is where we're going to find our answer. Now our answer is going to be one of
the sales values and depending on what I've selected in these dropdowns, the answer could
be anywhere in these values. So my array is going to be B5 to D17 because my answer could be
anywhere in there. We're going to find the row number using match, open our bracket, lookup value.
Well, our first lookup value is going to be whatever we have in cell G6. I want it to look
up the company. I want it to look it up in this array just here. So where we have all the companies
listed and I want it to do an exact match of that company name. Let's close up our bracket,
add another comma because we need to do another match. We need to match the months as well, right?
So we're going to do the same thing. Our lookup value for this match is going to be whatever we
have in cell H5. Our lookup array, we're looking for that month in this little range just here,
B4 to D4. And we want to do an exact match of that month name. Let's close off our match,
close off our index, hit enter, and there we have our result.
So now I can change both of these. Let's say, let's select March and let's choose a different
company and it's going to produce the correct result. So that is basically some examples of
how you can use index and match to perform lookups. I'm going to be doing more videos on
index and match because there are so many ways that you can use these functions together.
But for now, that is it. If you like this video, don't forget to give that like button a good old
smash, maybe consider subscribing. And if you want to be the first to know when I post a new video,
then make sure you hit that notification bell. That's it for now. I'll see you later, guys. Bye!
