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.

Thank you. good explanation

just a tip, you could make this a 2 min video. trust me no one wants lengthy explanation cept this guy below

Very clear explanation even a beginner like me can understand.. Thanks !

I was here.

great tutorial but when I enter the index number of the column it gives me an error…

Great! Thank you.

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! 😀

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?

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

such very good learning place i get knowledge pivot table and vlookup from u

thanks u very much

Helped, but frankly its too slow. I waited coz I had no time to waste on searching for better tutorials.

Thank you

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

Good tutorial. It explained otherwise around specially usage in invoicing.

Absolutely Fantastic tutorial!! Thank you very much. So useful!!

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

Hey Simon, these videos are great. Do you think you could do a tutorial on Crystal Ball? (Add on to Excel)

Easy simple to understand tutorial on Vlookup, Pivot Table, Pivot Charts and More

Thank you!!!

>click on equals

Best vlookup video i have seen so far… and trust me, i have looked around. So many Thanks

Best explanation, I was really confused about VLOOKUP. thanks a lot for sharing …

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 🙂

excellent explanation of VLOOKUP

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.

Much better video than another one on youtube that has twice as many view

Simon, thank you for the tutorial!

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

your the best

British English Narration >> American

Thank you Simon. Your simple tutorial has made most of my work easier

thanks y have a good explanation sir

you made me the best person in my computer class. thanks

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!!!!