Transcripts
1. Introduction - The Index & Match Formulas: Hi everyone. I'm Jeremy, or as some call me Big dog. Welcome to my
Skillshare course on the index and match formulas. Before we jump into the content, let me take a second
to introduce myself. I have been in the
working world for almost seven years and
all of that time has been spent in Excel for my time in college to my
current role today, Excel has been a part
of my daily toolkit. I've developed a passion for analytics and
interests in figuring out the most efficient solutions and a love for automation. My goal for this video
series is to make you an expert on the index
and match formulas. The index and match
formulas, when combined, provide you with a Lookup
function that outperforms the VLookup and H
look-ups and ensures your calculations
will not change based on additional
rows or columns, the index match can boost
the effectiveness of ad hoc spreadsheets and
complex cost models. Join me in this series as we unlock the next
level in your cell potential and leave the other lookup
functions in the dust. Let's get after it.
2. Index & Match Basics - Part 1: Exact Match: Hi everyone, Welcome to the
first video in the series. In the upcoming videos, we will be covering the
index and match formulas. The index formula has us define a range of cells and
allows us to return the value of a cell from within that range given the
cells coordinates. The match formula asks for a lookup value and
the defined range, or the lookup value appears, the match formula will
then return the column or row number in the defined range or the lookup value is found. While these formulas can
be used on their own, the most common use case
is when they are combined. Over the next few videos, we will work through
some examples of the different ways these
two formulas can be used. Let's dive in. In this example,
we have a table of invoices for March that is
missing the customer city. We would like to pull
the customer city from the lookup table into
the working table. We can use the combined index and match formulas to help us. The index formula
starts by asking for the IRB ray that has
our desired data, which is B3 through B6
in the lookup table. Note that this will be set as an absolute reference so that the range doesn't change as we copy the formula to other cells. Next, the index formula wants
the row number in the array that we just defined of the cell where our
desired data resides. Because the specific
row number for the city that we want to return will
change based on the customer. We will need the row
number to be dynamic. For this, we will use
the match formula. The match formula first asks
for the lookup value a ten. Next, it asks for us to
enter the lookup array, which will be A3 through a six. Just like the index array above, this will also be set to
an absolute reference. Finally, it asks
for the match type, which in this case will
be 0 because we want an exact match less
than and greater than matches will be
explained in later videos. But know that those
are typically just used for matching numbers. The index formula has a final optional
input column number that we will not be
using in this example, but we'll take a
look at later on. Okay, we've finished
the formula. Let's copy it down and see if we're getting the
correct cities. Fantastic. Now, since
this formula combination can be a bit confusing, let's quickly review
what happened. First, we use the index
formula to get the range of cells where the customer
city information that we wanted was located. Since we didn't want to manually
input the row number of the customer city for
each invoice line, we use the match formula to dynamically return
the row number. The match formula return the row number of
the customer in the lookup table based on the lookup value that we
provided from the working table, we were able to use
the row number that the match formula provided as an input into the index formula to then return the
customer city. Keep in mind that the
index formulas or ray and the match formulas lookup array have to be the same row values. In our case, rows
three through six, in order for the formulas
to properly work together. Alright, let's move on to another example to solidify
our understanding.
3. Index & Match Basics - Part 2: Less Than Match: Hi everyone, Welcome to the
second video in the series. In this video, we're
going to build on the combination of
the index and match formulas and dive
deeper into what the match formula can
do. Let's get into it. In this example,
we have a list of customers and the annual
sales achieved by each. For each customer, we want to determine what their
rebates should be based on the rebate
tears associated with the annual sales targets
in the lookup table. Like in the previous video, the index formula begins
with the array of the desired data, B3 through B8. Next, in order to make the index formula is row
number Input Dynamic, we have to use the
match formula. In the previous video example, we use the customer as
the lookup value and 0 an exact match
as the match type. In this example, we're
going to use a number, annual sales as the
lookup value in one less than as the match type. Let's walk through this. The match type will
be annual sales between the lookup array will
be a three through eight, and the match type will be one. Let's quickly go through what's happening in the match formula. This time, the
mattress first taking the annual sales
lookup value and matching it to the annual
sales in the lookup array. Since we entered less
than as the match type, the match formula
is going to find the closest value in the lookup array that is
less than our lookup value. It will then return
the row number of the closest less than match, and it will be fed
into the index formula as the dynamic row number. We had already given
the index formula, the array of data. And now with a dynamic row
number from the match formula, it will return the
contents of the cell in the dynamic row numbers
position within the array. Let's copy the
formula down to see if we got the
results we expected. Awesome, everything looks good. Let's move forward
to the next example.
4. Index & Match Basics - Part 3: Index Match Match: Hi everyone, Welcome to the
third video in the series. In this video,
we're going to add an additional layer
to the combination of the index and match formulas and additional
match formula. In the last two examples, we looked at returning
just the row number two, the index formula to
get the desired data. In this example, we're
going to use to match formulas to return both the
row and column numbers. This will allow us to return
a wider range of data with just a bit more work
upfront. Here we go. In this example,
we have a table of invoices for March that
is missing the customers, city, state, and zip code. We'll use the information from the lookup table to
populate our working table. Unlike the other
two example videos, we will start with an
index array that includes rows and columns, B3 through D6. The index array will have
both rows and columns locked. One important note here,
throughout this formula, it will be very important
to pay attention to the reference
types of the cells. Some cells will have the
rows and columns locked in some we'll only have either
rows or columns locked. If the wrong cell
reference type is used, the formula will not
return what we intended. Next, we will use the first match formula to dynamically return
the row number. The first match
formulas lookup value will be the customer a ten. This lookup value will
have the column a locked, but the row ten open, the lookup array
will be A3 through A6 with both rows
and columns locked. Finally, the match type will be 0 because we want
an exact match. For the second match formula
to return the column number. The lookup value will be
the column header city. In cell C9. This cell will have
the row nine locked, but the columns C open. The lookup array will
be B2 through D2, with both the rows
and columns locked. Lastly, the match type will again be 0 because we
want an exact match. Let's copy this formula to
the rest of the cells in the city column and to the state and zip
code columns as well. Sweet, everything is
working as expected. Let's back up a bit and talk through how this
formula is working. We began the index match
formula by stating the range of cells containing the data that we want to
eventually return. A quick note, we want to be sure that this range of
cells only includes data that we want to be
returned and does not include lookup values that
we will use to get the data. Okay? Now that we have
the range of cells containing our
return data to find, the index formula now needs both the row number and the column number of
where the data is stored. Instead of giving static
numbers like row one and column two to return Ohio, we're going to use
match formulas to dynamically
return these values. We don't have to enter
the coordinates manually. The first match
formula is going to dynamically return
the row number. We give the formula,
the lookup value, the customer in
the range of cells where the customer lookup
value is contained. And it returns the row of
the customer lookup value. Since each customer in the lookup table has
corresponding city, state, and zip code information. The data that we want, returning the row
number of where the match formula found. The customer also gives us the row number of the data
that we want to return. The second match
formula is going to dynamically return
the column number. We give the formula,
the lookup value, the column header, and the range of cells where the column
header corresponds to. And it will return the location
of the column header as a number given both coordinates from the two match values, the index formula is now able to return the value of the
cell that we were after. That was quite a bit
of information to absorb, but you
powered through it. Since this is the last
video in the series, I'd like to thank
you for watching. If you like this course, please take a minute to
check out my other courses. And as always, if
you're feeling stuck, feel free to stop back
and ask a question. Thank you.