Transcripts
1. Introduction - The Vlookup: Hello there. I'm Jeremy, or as some call me Big dog. Welcome to my Skillshare
course on the VLookup. Before we jump into the content, let me take a second
to introduce myself. I've been in the
working world for almost seven years and
all 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
interest 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 VLOOKUP formula. And in record time, the VLookup allows
you to combine multiple data sources
in matter of minutes, being able to analyze
from different systems, multiple users or
across segments speeds up data analytics and allows you to be a
productive Machine. By the end of this course, you'll be combining
data that you never thought could
be partnered up. Ladies and gentlemen,
the VLOOKUP.
2. Vlookup Basics - Part 1: Hi everyone, Welcome to the second video
series where we will learn about the VLOOKUP formula. The VLOOKUP allows
us to aggregate common datasets within Excel sheets and
separate workbooks. And it is a foundational
building block to more complex formulas. The upcoming videos,
I will walk through multiple examples of how to
use the VLookup formula. And by the end of the series, you'll be a VLookup machine
without further ado. Let's jump into
the first example. In this example we have a working table and
a lookup table. In the working table, we have a list of invoice
totals from March, the customer that each
invoice total belongs to the region of the US
where each customer resides. In the lookup table, we have the different
regions within the US and the account manager that is assigned to each
of those regions. We would like to bring in
the account manager from the lookup table into the working table through
the region common ID. Well, there are other formulas that could help us
with this task. The VLOOKUP is the best
suited for the job. The VLOOKUP begins
with the lookup value. The lookup value is the
value that is common between the lookup table
and the working table. One very important note here, the VLOOKUP will only
work if the lookup value is included in both
the lookup table and the working table. And the lookup table or table array has the lookup value in
the first field or column. This is one of the
most common mistakes that I see with this formula. In this case, the lookup
value that is common between both the working table and the lookup table is
the Region column. Lookup value will be B11. Next is the table array. The table array is the range
of cells that includes both the lookup value data
and our desired data. Also, just to reiterate, the first column in the
table array must have the same lookup value data
from the working table in it. If this is not true, the formula will not work. The table array in this example
would be a3 through B6. And since we don't want
the table array changing cell references as we
copied the formula, we will need to make this
range and absolute reference. The next part of the formula
is the column index number. Each column of the table
array that we just defined has a number
associated with it. The first column of the
table array that includes our lookup value or region data in this example is column one. The account manager
information that we want to get is column to the column index number is
asking for the number of the column in the table array
that has our desired data. In this case, our desired
data is in column two. So we would input two
into the formula. The last part of the formula
is the range lookup. The range lookup field allows us to enter either
true or false. True represents a close match and false represents
an exact match. To keep the formulas
easier to understand, we will use false here and go over what true means
in later examples. Let's copy this formula down to the other cells and check if
we got our desired result. Bill will is the West
region account manager. Jake lake is the north
region Account Manager. And forests chorus is the
East region account manager. Awesome. We're getting exactly
what we expected. Let's move on to
the next example.
3. Vlookup Basics - Part 2: Welcome to the second
video in the series. In this video we'll
be going over the second example
for the VLOOKUP. If this is the first video
that you're watching, I recommend that you go
back and watch example one. The example one video
goes into more detail on the basics of the VLOOKUP that will not be included
in this video. Let's get right into it. Just like in the first video, we have a working table
and a lookup table. In the working table we have a list of invoice
totals from March. The customer that each
invoice total belongs to, the region number
that's associated with the region where each
customer resides. In the lookup table, we have different
regions within the US, the region number and the account manager that is assigned to each
of those regions. In the previous video, we brought the
account manager into the working table
through the field common to both the lookup and working table, the region name. In this example, the working table only
provides the region ID, so we'll have to use that
as the common lookup value. The VLookup begins with
the lookup value be ten. Next we define the table
array C3 through C6. After that, we input the
column index number, which unlike the
previous example, is in column three instead of two as our lookup table
has an additional column. Finally, we add in
the range lookup, which will be false. Cool. Let's copy this down and see if we get our desired results. Interesting. The VLookup didn't work. It seems that every other
cell is giving us an error. Now, obviously this was by design as I wanted
to illustrate, a common error that
the VLOOKUP runs into, and that is data-type. If we click into
the BLM one cell, we notice that there's an
error symbol that says that the Region number
is stored as text. Now, like I mentioned
in the previous video, in order for a VLOOKUP to work, the look of value in both
the working table and the lookup table
has to be exactly the same, including data-type. If for the errored out cells, we had Region numbers stored
as text, we would be okay. But all the region numbers in our lookup table are stored
as number datatypes. So we have to make are
working table match. Let's embed a simple formula
within our VLOOKUP formula. The number of value
formula converts any cell data to a
number datatype. If we use this on the
lookup value input in our VLOOKUP formula. It will convert any
numbers stored as text to number datatypes, which allows us to match the lookup tables
region number datatype. Let's copy this
corrected formula down. Awesome, we're no
longer getting errors. Let's move on to
the next example.
4. Vlookup with Concat Formula: Welcome to the third
video in the series. Now that we've mastered
the basics of the VLOOKUP, let's try to combine the
VLOOKUP formula with another common useful formula,
the concatenate function. The concatenate
function is used to merge the data of
two or more cells. Since the VLOOKUP requires
a unique lookup value, which the concatenate
function is very good at creating the VLOOKUP and concatenate functions
and make a great pair. One note before we jump in, the concatenate formula
was replaced by the CONCAT formula in 2019. So keep that in mind
in case you are using an older version of Excel. All right, let's get into it. Just like in the
previous lessons, we have a working table
and a lookup table. In working table, we have a list of invoice
totals from March. The customer that each
invoice total belongs to, the region number and
region butter that's associated with the region
where each customer resides. In the lookup table, we have the concatenated
or merged unique region id and the account manager associated with each
unique region ID. In previous lessons, the unique lookup
value was in one cell. In this lesson, we're
going to have to merge two cells to create
the unique lookup value. The VLookup always starts
with the lookup value. The lookup value field is going to use the CONCAT formula, which accepts
multiple cell inputs. We will be using two inputs, the region number cell B11 and the region
lighter cell C11. Next we define the table
array A3 through B6. After that, we input the column index
number, which is two, because the data that
we want to return is in the second column
of the table array. Finally, we add in
the range lookup, which will be false because
we want an exact match, not a close match. Good, let's copy this
down and see what we get. It looks like we're getting
exactly what we intended. Awesome. Now, I just wanted to take a second look at the
CONCAT formula on its own. Before we wrap up, I wanted to clarify what
this formula does in case there's
confusion in cell F2. Let's enter the CONCAT formula
with B1 and C1 as inputs. We are getting one
a as the output. In our VLOOKUP example, the CONCAT formula is doing
the calculation first and then passing the
value one to the VLOOKUP. The VLOOKUP is then using one as the lookup value to
search the table array. Feel free to leave a question in the discussion board if
this is still confusing. Since this is the last
video in the course, I'd like to thank you
all for your time. You've been great students and I wish you the best on
your Excel journey. If you're ever feeling stuck, feel free to stop back
and ask a question. The big dog will always
be here to help.