Instructor: Okay, I wanna start by talking about two
of the most common yet powerful LOOKUP functions in Excel,
VLOOKUP and HLOOKUP.
These are generally used
when you need to combine two different data arrays
with a common field.
So for instance, if I have product level data
in one array where I have quantity and product ID,
and then I have another array of data
with the same products and the price information,
and I wanna merge those data sources
so that I have quantity ID and price all in one spreadsheet,
the LOOKUP is a great tool to use to do that.
So there are a few components of the LOOKUP function.
It starts with the lookup_value.
So this is the value that you're trying to match.
It's typically the common field that exists
between the two arrays, the source and the LOOKUP array.
The second component is the LOOKUP array.
So this is where you're trying to find that lookup_value.
And the third piece is the column index number.
So in your table array
where you're looking for your LOOKUP value,
which column of data would you like to return?
So that's typically where your variable is
that you're trying to pull into your first source array.
And then last but not least, you have a range_lookup,
which basically just says you're trying to match
the exact LOOKUP value or something similar.
If you're trying to look up the exact value
which you'll use for text strings and words,
you'll put a zero or the word false there.
Otherwise you can put a one.
For instance, if you're looking up a number
with a bunch of decimal places
and you don't need it to match exactly, you could use that.
But just so you know,
99% of the time you're gonna use a zero
or exact as your range_lookup.
So in this example here,
again, I have this product level data that I just described.
And in one array, I've got product name,
quantity, and product ID,
and in my second array, I've got product and price.
So as you can see, product is the common field between them.
And what I wanna do is find the price in my LOOKUP array
and pull it into my source array.
So in cell D2, I've created a price column.
I'm gonna say VLOOKUP, whatever's in A2,
which happens to be the product T-shirt,
within my table array G1 through H5.
So it's gonna go row by row until it finds the LOOKUP value,
which in this case it's finding it in row five.
And then it wants to return the data
from the second column over,
since that's the column index number.
So it finds T-shirt, moves to the second column over,
finds the value of 14.99, and returns it in cell D2.
And then as you apply this LOOKUP table down,
it's gonna be looking up sweater, then shorts, then socks,
all within this LOOKUP array.
So that's VLOOKUP.
HLOOKUP works almost exactly the same way.
It's really the same syntax.
The only difference is that you need to use an HLOOKUP
if your LOOKUP array is transposed,
which is kind of an odd way of showing the data,
but sometimes it'll run across data sets
that are built in this way.
So it works the same way.
The only difference is that it will move column by column
until it finds its LOOKUP value
and then move down to a specified row index number,
as opposed to moving row by row
and moving to a column index number for the VLOOKUP.
So again, same exact concept.
You just use it when your data is transposed.
So two very, very important rules
that you need to remember
when you use VLOOKUP or HLOOKUP functions.
The first is that your LOOKUP value
has to be in the first column if you're using a VLOOKUP
or the first row if you're using an HLOOKUP
of your table array.
So you can't have a table array
that goes from column A to D, where your LOOKUP values
or your matching column is column B.
Excel just doesn't like that.
The second rule is that excel will always return the value
from the topmost row if you're doing a VLOOKUP
or the leftmost column if you're doing an HLOOKUP
of a table array
if multiple instances of your LOOKUP value are present.
So this is a very, very important distinction to make here,
which is that, if I go back
to my VLOOKUP example, for instance,
and I'm looking up the value T-shirt in this LOOKUP array,
let's say two lines in a row
had T-shirt as the product with different prices,
the VLOOKUP is going to stop
the first time it finds the value it's looking for,
in this case T-shirt,
and return the price in the second column over
for that first instance.
It's not even gonna know that there were multiple instances
because it stops at the first one.
So the way to get around that
is to identify a key that's common to both data sets
and unique for every row.
That's the key piece here.
So it typically takes the form
of a concatenation of multiple fields.
We'll talk more about those specifics
in the TEXT function section,
but basically concatenation which can be accomplished
just using the ampersand sign in Excel
just mashes together two text fields into one.
So we will see in our baseball example
that we've got players and years,
and we're gonna need to create a common key
based on both of those to properly use the VLOOKUP function.
So that's VLOOKUP and HLOOKUP.
In the next lecture, we're gonna talk
about actually putting this into practice
by merging our data.