Articles Blog

Microsoft Excel 2013 Training — Using the VLOOKUP Function

Microsoft Excel 2013 Training — Using the VLOOKUP Function


Hello again and welcome back to our course
on Excel 2013. By now we’ve covered quite a lot of the
basics of Microsoft Excel. You hopefully have done some of the exercises
that I provided and you’re starting to feel more confident in your use of Excel. One rather unfortunate thing about Microsoft
Excel is that it is a big, very powerful, and quite complex product. And if you’re still quite new to it, there
are many, many more things to learn. And I think one of the dangers is trying to
take in too much at once and there’s always a good case for getting a bit of practice
with what we’ve covered already. So what I’m starting in this section is
a small case study to show you how we can use some of the things you’ve already learned
together with some new or perhaps more detailed things related to Functions to assist in the
production of a business document. Now the business document we’re going to
produce is an invoice for our plumbing supplies company. You’ve seen it already earlier on in the
course, but now we’re going to start to look at making it not only more flexible and
more powerful but also something that could be more likely to be used in a business environment. So first of all, let me explain a little bit
about this invoice. We have the word Invoice at the top. There’s our name, Ocean Plumbing. Our address is 2324 Main St. South Park. And this particular invoice is going to Zak
Stephens of West Beach Bathrooms. There’s his address. It’s going by courier. The order date is that date. The order number is that. The purchase order number from the customer
is that. Contact department there is purchasing and
the account number for this customer, 2973, terms are 30 days. Now if I were regularly invoicing West Beach
Bathrooms, I’d have all of this information in a database. And for the purposes of this exercise, my
database is one of the sheets in this workbook. Now normally I wouldn’t set things up like
that but in order to demonstrate how to do this, let’s just go along with my approach
for the moment. So let’s assume that the worksheet called
Customers here is actually my database of customers. Now I’ve only put four on there at the moment. I’d hopefully have more than four customers,
but there you are, account number 2973, West Beach Bathrooms, Zak Stephens is the contact. Their terms are 30 days. They do get a discount and there is their
address. Now if I were preparing an invoice for West
Beach Bathrooms in reality, I wouldn’t type in all of these individual lines and pieces
of information for West Beach every time I do an invoice. I might copy an old invoice but it will be
much better if I could automatically fill in these cells in Excel. And what I’m going to show you how to do
now is how to use one of the cells to help to fill in many of the other cells. Now first of all, what is it about this customer
that helps us to identify their information? And in the case of a customer of a company
it will usually be an account number. So if I have the account number, in this case
2973, then I can use that account number to find the other information. Now let me just flip back to the Customer
Sheet again. On the Customer Sheet the account number is
in the first column. What I’d like to be able to do, go back
to the invoice, is if I instead of putting 2973 here put say 2972, it would bring up
the other information for the other customer. So there was a customer 2972, that’s a company
called Bathtime. There’s my contact there. I’d like to automatically bring up that
customer’s details and create an invoice for them if I put their account number on
here. Let me just put that back to 2073 and then
we’ll see how we do that. Now the first field, the first cell that I’m
going to apply this principle to is that one, D5. It’s currently the name of my contact at
the customer company. It’s currently got typed into it Zak Stephens,
but instead of Zak Stephens I’m going to put in there a formula. And the formula will actually be a function. Click on equals, go to Formulas, and the sort
of function it’s going to be is a Lookup Function. Now Lookup and Reference Functions are ones
that get information basically from elsewhere. And the Lookup Function we’re going to use
here is one called VLOOKUP. There are two direct functions. There’s a VLOOKUP function and an HLOOKUP
function. We’re going to go for VLOOKUP. Just read the screen tip there. Looks for a value in the left most column
of a table and then returns a value in the same row from the column you specify. By default, the table must be sorted in ascending
order. Now we are going to be using account number,
therefore one of the requirements is that this table of account numbers must be in ascending
order. That’s pretty straightforward because we
can always sort a table if we need to. So it’s the VLOOKUP function that we want. Now when you choose VLOOKUP in Excel 2013,
it brings up this very convenient dialog and we can use the dialog to fill in the rest
of this. Now, first of all, what’s the value we’re
going to look up? Now the value we’re going to look up is
the account number which is in C17. If I click in there, where is it going to
find it? Now where it’s going to find it is in the
Customers Sheet. So if I click on the Customer Sheet and select
from the beginning of the table of customer information down to the end, it’s a very
small amount of information at the moment. But if I select that, it gives me the table
array. What it’s saying is I want you to look up
what’s in C17 in the table. It doesn’t actually have to be setup as
a table. It can just be a range. The table, customers, that’s on the Customers
Sheet, A2 to G5. And as you can see from the marching ants,
A2 to G5 covers all of my customers. Now when VLOOKUP does the look up, it always
looks up in Column 1. It will look for whatever’s in C17 on the
Invoice Sheet in Column 1, so it will only look in the account numbers. But the next question is which column will
the answer be in? Here I want in this particular cell the customer
contact name and the contact name is in Column C. So which index number that’s 1, 2, 3. That is Column 3, click on OK, and see what
I get. I get Zak Stephens. Zak Stephens is, that’s the value but the
formula is equals VLOOKUP. C17, that is what do you want to look up? I want to look up the account number. There it is, 2973. Where do I look it up? You look it up in customers bang or exclamation
mark, A2:G5; so it’s on the Customer Sheet and it’s in the range A2 to G5. Which column number? Column 3. So far, so good. Let’s see if that works. Let me click in the C17 field and let me now
change that from 2973 to 2972, tick, and what I get now is Amit Nehraj in the contact name
field. So it’s automatically and let’s do another
one, 2971, tick. It’s automatically changing the name here
when I change the account number there. Now you can obviously see what the problem
here is and that is that we’re sorting out the contact names automatically based on the
account number but we’re not doing everything else. And that’s where there’s a little job
for you to do on some of the rest of this invoice. So what I want to do now is to just take another
look at that Customers Sheet again. Note the columns there: account numbers are
in Column 1, company name in Column 2, contact Column 3, terms of business in 4, discount
in 5. The first two lines of the address in Column
6 and 7 and there could, of course, be many other columns with other information about
this particular customer. But I’m only going to use these at the moment. And we’re going to now look at the terms
of business column. Now let’s suppose that the next thing I’m
going to do is to put a similar Lookup in here. One option is to literally copy that cell. You might look at it and think, well, I’m
copying Dorothy Walinski. I don’t want it to say Dorothy Walinski
but don’t worry about that at the moment. Let’s do a Control-C to copy it and let’s
do a Control-V to paste it and, of course, we get an error. Now, hopefully if you look at what’s in
the formula bar, you can see what the error is. Because the error is that with this Lookup,
when I’ve moved the Lookup to a different cell, it’s basically used its referencing
to change not only the cell reference for the item to look up, the account number in
this case, but also the range on the Customer Sheet for the information about customers. So let me undo that and let me go back to
the original formula for Dorothy and you should know that what we need to do there is to put
in absolute references. Now we can get over it to some extent in terms
of the Customer Sheet by referencing it as a table, but let’s put in the absolute references. Just tick that to make sure that Dorothy still
works. Dorothy still works. Let’s do a Copy. Let’s Paste that in here. Well that’s better because we now get Dorothy
in there. But you should know why we get Dorothy in
there and that is because if I tick in this particular cell, so I’m now in E18, the
terms of reference are not in Column 3, they’re in Column 4. So if I change that 3 to a 4 and tick and
I get 7 days for Dorothy Walinski. Now bear in mind this is 7 days for account
number 2971. If I go back to customers 2971 terms are 7
days. So what we have here is Example 13. We’ve just effectively automated the terms
and the contact name. What I’d like you to do is to do the same
for the name of the client company and for the first two lines of their address. Now obviously this will still be a rather
strange invoice because part of it, such as the phone numbers, won’t change. But if you were feeling really, really keen
and enthusiastic and if you’ve got the time, you could maybe extend that table out to include
telephone numbers or anything else indeed that should be specific to individual customers. You’d, of course, have to change your VLOOKUPs
to use the bigger table. But all I really need you to do is to have
a go at the name of the company and the two lines of the address, address line 1 and address
line 2. And then you can test it as here just by changing
the client number. If I put this back to 2973 watch what happens
in D5 and E18. When I change that, I get the contact name
and the terms change back again. So my answer to that question is Example 14
and I’ll see you in the next section.

34 thoughts on “Microsoft Excel 2013 Training — Using the VLOOKUP Function”

  1. This video is a life saver!
    Using VLookup is a part of my current assignment in class and I can't do it for the life of me, really helped me out, I'll use functions through the tools way instead of typing them out from now on, makes life so much easier! 🙂

    And now onwards to do Forecasting and then hopefully I'll get an A grade on my assignment! ^^

    Thanks for the video! 😀

  2. The lookup value in the VLOOKUP function only allows for you to reference one field. How do you use a VLOOKUP or another Excel function to give a value based on 3 or more fields without using INDEX?

  3. Trying to apply for jobs where V-Lookup is essential :(…. so thank you for this! I agree that YOU are a life saver!! God bless you

  4. Great instructional video!  Thank you.  I agree with the others –  VLookup and PivotTables are HUGE when applying for certain jobs.

  5. Thanks for the tutorial. I want to create automatic dimensional reports for a range of different parts. So I only have to change the part ref no to have the dimensions and tolerances, drawing version, etc automatically filled. I will also use this to feed the results of the measurments, from an external table… hopefully LOL

  6. I'm in my first year oif A-Levels and we have an exam on edexcel. Not going to lie I'm terrible at edexcel and the questions are incredibly confusing. Example: "Enter suitable formulae into cells G6–G8 of the ‘Values’ worksheet to show a value
    for total cost, total revenue and total profit."

    Do you have qany idea on how I can decipher these questions so that they make more sense to me? I understand you may not be an ICT teacher but you appear to know exactly what your doing!! We have the senario and my school thinks its about planning a tourist boat route in relation to time, date and order of attractions to visit. It's also in relation to tidal height. If you think you could help I'd be very grateful!! If you would happily give me your email, I could send you the previous question paper and spreadsheet from last years exam for you to look at and possibly give me some advice but don't worry if you can't. Thank you for this tutorial though, you're great at explanation 🙂

  7. Hello Simon Sez IT, I wondering if you could help me with an Excel issue that I have not been able to figure out through any of the youtube excel videos. I would be so appreciative. My email is [email protected] I look forward to your response. Thank you RWN Laguna Hills, CA.

  8. Simon, thanks for the nice tutorial. How do I practice the way you teach? Do you have practice files I can download?

  9. I love the way you are teaching Excel. I have been writing code for a bit and recently started out learning from the beginning and I am glad I did. There are some things that I have been doing the hard way thinking that was the way it had to be written. Some teach Excel by first showing "what not to do" but that really throws me off. With that said, thank you for the way you are teaching. Is there a list of videos that I can access? YouTube often takes me away from your videos while trying to move forward with your teaching. Thanks Again, you're awesome!!!!

Leave a Reply

Your email address will not be published. Required fields are marked *