How To Use Vlookup

Hi, my name is Ghamza Jacobs, I'm an IT Trainer with New Horizons in London. I'm here today just to show you a couple of cool little tips and tricks on Windows 7 and Office 2010. How to create a Vlookup function in Microsoft Excel 2010.

Vlook up stands for vertical lookup and it's extremely useful to look up values against a data set. The example I have here, I've got a number of IDs listed about here. Now, these are my employee IDs, these may quite often mean nothing to me, all I'm interested in is the person's name.

On the employee sheet, you'll notice I have a list of all the employee IDs, person's last name, first name, date of hire and department, and office location and extension. So that's it. All I want to do here is I want to know what the person's first name is relative to the ID listed on the left hand side.

Firstly, vlookup comes in effectively. As I mentioned, it's vertical lookup so all this vlookup is going to do, I'm going to say find this value in this column, and it's going to vertically run down the column, find the value, and it will return the first name for me back into the cell. That's vlook up, so vertically looks up a value, when it matches that value, it returns another value in the same row next to that.

So think, find the NI number and give me the person's phone number, think find the client name and give me the revenue, and so find something that loves the lift in those columns. Let's have a quick look at it. I'm going to type the equals, obviously we start with the equals sign for our formula.

Now, one of the nice features in Excel 2010 and 2007 is as you start typing, look, as you start typing V, it gives me every formula or every function that starts with V. Type in VL, it leaves me with vlookup. So I can now use my mouse, double my mouse to double mix, use the tab key on the keyboard, it gives me vlookup.

Now, and also let's look at the requirements here, look up value, table, column and as such. You know, starting out with the formulas, sometimes it can be difficult. So what I advise a lot of people to do is you can hit the fx button, the insert function button, add this point up to the bracket, this now gives you a very nice breakdown that thoroughly explains vlookup.

Firstly, we have our lookup value. Lookup value is explained at the bottom here, the value to be found in the first column of the table. So, I'm looking for this value, and then an important point there is it looks up in the first column of the table, so it will always look in the left most column of your table, okay.

So just remember that the first column on the left's the vertical column. The table array, this is where you would like it to look up. So I simply go into the table array, and I would need to select the data that contains all of my data entries, all of my values.

I'm going to go to the employee sheet at the bottom and select all of my data. Now, remember what I said earlier, it always looks up in the left most column here, so it always looks in the first column, so that's perfect. It's going to look for the employee ID over there.

I'm going to fix those cells now using the dollar sign of course, I'm going to note the difference, now the keyboard's shortcut for that is shift 4. The next step is to enter the column you'd like to return, so what have I got so far? I've got what would you like to find, that's A2, that's my lookup value. Where would you like to find that value, that's my table array, that's my employee sheet data.

The next one, the final one there that is required is the column index number. This is the column that you would like to return. Now, this is a bit confusing to some people because you will look at this data and say okay, you're looking at the first column that's A, fine.

I want the name, the first name back, that's column C, but the index number is actually the numerical value of that column. So in my table, all I have to do is I go count it, 1