In this video we're going to go over the VLOOKUP formula in Excel. Now, VLOOKUP stands for vertical lookup. Meaning that
whatever it is you're looking up, all the references will be up and down, um, they won't be horizontal. We'll go over horizontal in a
different video. But, the VLOOKUP, you're going to lookup up and down what you're looking for
and
when Excel looks, one thing to remember, before we get started, is whatever you're looking up needs
to be on the further left column of the data set that you're referencing.
So,
for example, so we want to look up what product was sent to Omaha.
So we want to say =VLOOKUP,
and
Our lookup value is going to be Omaha. Now, we're going to want to put that in quotations because it's text, it's not numerical
like Excel is used to doing. The table array will be
our range of columns that we want Excel to look through.
So we're just going to highlight all of these columns. You can also just highlight a section, but,
whenever possible, to avoid errors, it's just better to highlight all the columns.
The next question is the column index number. So Excel is not going to reference your column headers. It's actually just going
to assign a number to each column. So, for example, the first column is always 1.
in this case "Product #". Column B would be our second column, or 2, and then 3, 4,
5 and 6 - all the way over to column H. So we need to know which product was sent to Omaha.
So we're going to just choose number 3 for column E.
And then it asks us if we want to choose approximate or exact match. So I prefer to use exact match. I've actually never used
approximate match, I've never needed to. Approximate match will bring back the first value that it sees
that is close to what you want. And that, a lot of times, can just give you errors
so I like to do exact match.
So, we close our parentheses and we can see that a hat was sent to Omaha. So, if you look up Omaha here...where is Omaha?
And you can see that, yes, indeed, a hat was sent to Omaha. Now to illustrate, if you forget to do the
parentheses, excuse me, if you forget to do the quotation marks, you're going to see
here what happens - you get an error because it's not sure what to do.
So I'm just going to undo that.
So the next thing is, we want to look up the product price of Product #32480.
Now, that we can just type as a number. So we're going to do VLOOKUP,
32480
and we want to choose the range. Now
we're going to actually look up...we're going to have a slightly different range because, again, we need for the product,
or for the item that we're looking up, to be on the further left column of our data range.
So we're going to choose...we're going to start the range with column D. So D through H. And
we need the Price, which in this case will be 1, 2, 3..will be the fourth column.
And choose False.
And you see now that 32480
was $30.00.
Now, we want to look up where Product #32479 was delivered to. Well, that puts us in a pickle because 32479 is to the right of
the location. So the easiest thing to do in this circumstance, you can either move this or...
I'm just going to copy it because if I drag the entire column then that'll mess up our other 2
formulas. So we just copied and pasted that column over
to anywhere on the right of
the Product #.
So we just simply type our formula again. So, =VLOOKUP and we're going to do Product # to the new Store Location and that is 6 columns.
Whoops! We don't want that.
32479,
choose our range again,
column 6
and false. And we see that
Product #32479 was delivered to the Mobile location.
So, hope this helps you with your VLOOKUP formula. And if you enjoyed this video and would like to know right away when the next
ones come out, please make sure to subscribe to this channel. Thanks!