Financial Formulas and Lookup Functions (Combined PC/Mac): INDEX and MATCH, Part 1: Basic Syntax
and Data Analysis Welcome to the next lesson in these PC and
Mac combined Excel lessons. We're going to look at the INDEX and MATCH functions here.
As I say in the notes, these functions are the best way to retrieve data from ranges
of cells in Excel. They are an improvement over the VLOOKUP and HLOOKUP functions that
we learned last time around because they are faster to execute in Excel, they're more flexible,
they're more powerful, and they get around some of the limitations of those functions
like the need to only move in certain directions and the need to know the exact row number
and column number in advance. You will use these functions all the time
in data analysis to join together data from different tables. You'll also use these functions
in financial models to retrieve data from separate calculations or data pages and then
display it in the spots that you want.
So, our plan for this lesson and the next one is to demonstrate the basics of these
functions here using some simple examples, apply them to this data analysis by rewriting
the Commissions function over here, and then in Part 2 in the next lesson, we will show
you how to apply these functions in financial models to set up scenarios and data retrieval.
Let's go to the key functions and their applications in data analysis, and then we'll conclude
this lesson with the exercise. The INDEX function returns a value or the
reference of a cell at the intersection of a row and column in a range. So, when you
enter the INDEX function in Excel, it accepts as inputs an array, a row number, and a column
number. Now, if you notice, the column number here
is in brackets, meaning that it's optional. So, if you don't enter anything there, Excel
will just assume 1 for the column number. So, really, all you need is a range of cells
and a row number for this function to work. If you have to enter the row number and column
number manually, INDEX is not that useful of a function.
For example, let's go over to the Sales_Reps tab right here and let's just enter an INDEX
function. I'll enter INDEX. And then for the array, I'll select the whole range of cells
with data here. And let's say that we want to go down three rows and four columns, so
I'll enter 3 and 4 for the row number and the column number. And we get FL for the state
of Florida. If we want to change this and maybe we want
to get the base salary, so we have to go over one, two, three, four, five, six, seven, eight
columns. Let's change the column number in there to 8, and we get the person's base salary.
Now, this function is not very useful. In fact, it's worse than VLOOKUP and HLOOKUP
because now we need to hard-code the row number and the column number. So, it's not very robust,
and it might have certain limited applications, but as it stands, it's not all that useful
to be able to look up an item by the row number and column number, because you need to determine
the row number and column number first anyway.
One other thing here I'll show you quickly before we move into the next part in this
is that, as I said in the beginning, the column number is optional. So, for example, if I
just enter something like this vertical column right here, and then I enter 5 for the row
number, I could just end the INDEX right here. I don't need to enter a column number. It'll
just assume 1 and we get Sanford Island. Now, if we extend the range slightly and we
make it two columns instead, we could attempt to do the same thing, but then Excel will
give us a #REF! error. And the issue here is that if you actually have multiple rows
and multiple columns, then you do need to specify the column number.
So, this little trick only works if your INDEX area is just a single column. In that case,
you don't need to give the column number. But if it's more than one column, then you
do need to give the column number. So, we can enter 1 here or we can enter 2.
In any case, as I said, the basic INDEX function is not super useful. Where it becomes useful
is when you combine INDEX with MATCH, because MATCH lets you automatically retrieve the
row number and column number and then pass those into the INDEX function.
Now, some people think this is very complicated
because they're not used to using Excel functions together and putting one function within another
function. But if you get used to it, it will be a very, very powerful combination that
most people either do not know about in Excel or most people do not use correctly or to
its full extent in Excel. So, with the MATCH function, if you enter
it in Excel, you have to enter the lookup value, the lookup array, and then the match
type. So, for the lookup value, let's just go over
and demonstrate how this works with the sales reps table here. So, I'll enter MATCH. And
then for the lookup value, let's say I want to enter "state" and figure out which column
this is. For the lookup array, I will enter the row up here at the top. And then for the
match type, I can say less than, exact match, or greater than. We almost always use exact
match. And you have to enter this as 0, because if
you don't, Excel we'll assume 1 here for less than instead. So, we always prefer to enter
this hard-coded as a 0 to specify that it's an exact match. And Excel tells us that this
is in one, two, three, four columns over.
Now, it's important to note that MATCH returns the relative position. What I mean by that
is if you look at this entire spreadsheet, Column A is 1, Column B is 2, Column C is
3, Column D is 4, Column E is 5. So, the absolute position of this is column number 5 in the
spreadsheet. But the MATCH function only is concerned with this area, and within this
specific area, state is in the fourth column. So, that is how the MATCH function works.
You can run this vertically as well. So, if we enter MATCH, and then let's say that we
want to do a MATCH for the base salary, for example, and I'll enter 89,000. For the lookup
array, we will enter this column, and then for match type, I'll say 0. Zero is pretty
important here because with numbers, less than and greater than actually makes a big
difference in terms of what MATCH retrieves.
And we get to position 10 here, as expected, because this is the 10th row in this range.
That is the relative position. The absolute position is the 11th row in the spreadsheet,
but remember, MATCH only works based on relative positions.
So, that's the MATCH function. You can then combine this, as I was saying before, with
the INDEX function. Normally, you pass in a single row or column for the lookup array
part in MATCH. And then when you use the INDEX function, you have to make the area that you're
indexing match up with the ranges given in the MATCH functions.
So, let's take a look at how this works. Let's say I want to index this whole area. So, the
first thing I'll do is type INDEX and then select this whole area. For the row number,
we don't know exactly what we want, but we know that we're looking for information about
a specific sales rep and we have the ID for the sales rep.
For the sales rep, I'll use a MATCH function right here. And then for the lookup value,
let's say that we're concerned with sales rep number six. For the lookup array, we will
select the sales rep id right here, this column, and then for the match type I'll say 0 for
exact match. So, this will give us the correct row that we need to pull information from.
Now, for the correct column, let's enter MATCH again, and let's say that we want to start
by retrieving the sales rep's name. So, I'll enter "name" for the lookup value. For the
lookup array, we want to go to the very top row right here. And then for the match type,
I'll say 0 for exact match. And I forgot parentheses there at the end.
So, we should have a parenthesis after the MATCH and then a parenthesis to close out
the INDEX function. And we get Sylvia Martin for this person's
name. Now, you can already see from looking at this
that this already has some significant advantages over the VLOOKUP or HLOOKUP functions. For
one, we're not doing a lookup based on the leftmost column. We're actually doing a lookup
based on this column in the center and then we're moving to the left. We could move to
the right as well.
For example, if I entered right here the base salary instead, we get the person's base salary
and we'd be moving from the center to the right.
Another big advantage of this INDEX and MATCH combination is that if we ever change this
table . . . let's say I just add some random rows right here and I'll add some random columns.
It doesn't matter. The function still works because it searches for the actual position
in the row and in the columns, and you don't have to tell it exactly where it's going to
be. Even if you use something like ROWS and COLUMNS
to get around that particular limitation of HLOOKUP and VLOOKUP, you're still going to
have a problem if something fundamental about the structure of the data or the shape of
the data in some way changes. INDEX and MATCH get around that limitation because it searches
for the actual data so that even when something changes, the search will update correctly
and everything will be displayed properly.
I'm going to delete these extra rows and columns for now.
Now, if you're confused about exactly what a function like this is doing, I'm going to
rewrite this and show you what Excel is doing internally, and that may help to clarify exactly
how this works. I'm taking a lot of time to go through this because this function is so
important and a lot of people tend to be confused about it.
What this function is doing and how Excel is processing it internally is the following.
The INDEX part is pretty clear. We're just searching across this whole area. The MATCH
part, so when we type something like =MATCH(6, and then G2 to G11 for the range here, and
then 0 for exact match, Excel goes down and keeps looking for the 6 until it finds it.
So, it starts in the first row, goes to the second row, the third, fourth, fifth, sixth,
and seventh, and it finds it in Row 7 right here. So, it rewrites it and it puts in that
7. This MATCH part becomes 7 for the position.
Then for the column part, it starts in our leftmost column right here, name, this is
number one, address is number two, three, four, five, six, seven, eight. It goes all
the way across until it finds base salary in this top row. It's an exact match. It's
position number eight right here, and so Excel replaces this MATCH with an 8, and that's
how we get to the 86,000. So, that is literally what Excel is doing
internally. It's just searching through the data until it finds the first thing that is
an exact match in both cases, and then it gives you the position of that and it substitutes
it into the INDEX function. So, that is how the INDEX and MATCH combination
here works. Now, we've only scratched the surface. You can do a whole lot more with
this. For example, I've hard-coded the inputs to
these functions here for base salary and the sales rep ID. But if you want to make this
more powerful, you could have some type of user input function, Sales Rep ID right here,
and then you could have the field title right here.
And what you could do is instead of hard-coding this manually, you could actually . . . let
me change this back into a function. So, instead of hard-coding this manually, you could actually
go here and you could link to the sales rep ID field. And then for this one, instead of
hard-coding the field name, you could just link down here.
Now, this function gives us an error message right now, but if we enter, say, 2 for the
sales rep ID, and then we enter city for the field title . . . let me actually remove these
double quotes. Okay, so that's correct. So, now we look up the city for sales rep ID 2,
and we can change this to sales rep ID 7, and we can change this to annual salary increase
or commission rate. These are not displaying correctly because of the formatting, but that's
just a formatting issue. You can see if you change it to a percentage, clearly, it shows
up. So, you can do endless numbers of things with
this. You can make your spreadsheets more interactive. You can let users enter data.
You do have to put some restrictions on it if you want to do this, which we'll cover
in an upcoming lesson on data validation, but I just wanted to show you some of the
basics of how INDEX and MATCH work for now.
Really, the only disadvantage of these functions is that they are a little more difficult to
write than VLOOKUP and HLOOKUP. People are less familiar with them and the average person
who just uses Excel casually is not going to understand exactly how these work. However,
you are going to be using Excel quite intensively if you go into finance or use this in any
accounting or finance role, so you should really know these functions pretty well.
With that, let's now go into the exercise. It's pretty simple. Rewrite the Commissions
Formula here, and instead of using VLOOKUP as we're doing right now, I want you to rewrite
this, make it more flexible, and use INDEX and MATCH instead.
Do not hard-code anything. You should link to the appropriate cells in the sales reps
sheet and use anchoring and the MATCH functions to do the work.
I'm not going to give any more instructions than that, but use what we just learned and
rewrite this function using INDEX and MATCH. Pause the video, try it yourself, and when
you're done, come back, unpause it, and we'll walk through it together.
Okay, good. So, let's delete everything we have here first and then let's type in INDEX.
Let's go over to our sales reps right here. We'll index this whole area, Shift + Ctrl
+ Down Arrow Key, Right Arrow Key to select everything. F4, or ⌘ + T on the Mac, to
anchor all this. Then for the row number, we want to use a
MATCH function, and we want to match the sales rep ID right here. We do not want to anchor
that. That should move down as we copy and paste this formula down.
And then for the lookup array, let's go over and select Column G right here. This one we
will anchor it completely with F4 or ⌘ + T. And then for the match type, I'll say 0.
We want an exact match. For the column number, we will use a MATCH
function once again. For the lookup value, we want to take the commission rate right
here and I'm just going to link to this directly because, remember, we don't want to hardcode
anything in this function. So, I'm just going to literally link to the cell that has commission
rate in it right here.
And then for the lookup array, we will select everything here at the top. I will anchor
that. I should also anchor the commission rate right here because we don't want either
of those to shift around as we move down. And then for the match type, I will just say
0 for exact match. I will then close out these parentheses.
Before multiplying by the order dollar amount, as we did before, let's just enter this and
see if it works. So, if I change the formatting, we get 15%,
which matches the commission rate for the first sales rep, so it seems like this works.
Let's change back the formatting and then let's multiply it by the order dollar amount
right there. And then Ctrl + C, Ctrl + Down Arrow Key, Shift + Ctrl + Up Arrow Key, and
Alt, E, S, F, to paste these as formulas. Or we could just use Ctrl + V there. It doesn't
really matter. And so, now we have that. We've rewritten it by using INDEX and MATCH.
It doesn't make a huge difference in this case. The formula does look more complex,
but it's also more flexible because if we ever change this data or rearrange the order
or add rows or delete rows or add columns or delete columns, it'll still work as long
as we have the sales rep ID column and the commission rate column.
That's it for this lesson. Let's do a quick
recap and summary. INDEX and MATCH are the best and most powerful
ways to retrieve data from ranges of cells in Excel. They get around the limitations
of VLOOKUP and HLOOKUP and are more flexible, more powerful, and more efficient.
INDEX retrieves something in an array, a range of cells, at a certain row number and column
number. MATCH looks up something in a lookup array, usually a single row or column, and
then gives you the relative position of it. So, you often use INDEX by replacing the row
number and column number part with MATCH functions, passing in the position of whatever you found
with the MATCH functions, and then INDEX gets the value at the intersection of that row
and column. The column number is an optional input with
INDEX, but note that it only works if you're just indexing a single column as you saw before.
I'll add that in the notes right here.
When using these functions together, you have to be really careful that the areas match
up. So, if you are indexing 20 rows in a range of cells with INDEX, for example, then if
you're using MATCH within that, the MATCH part also has to be 20 rows and has to start
at the same row and end at the same row as the indexed area.
You saw how to use these functions to rewrite the Commissions Formula and how to make it
more powerful and more flexible. That's it for this lesson. Coming up next,
you'll see how to apply these in financial models and how to retrieve data and set up
scenarios there.