Using a VLOOKUP and what it does

1
Using a VLOOKUP and what it does

• By B. C

2
What is a VLOOKUP?

• VLOOKUP is a function in Microsoft Excel you can
  use to find data in a table on one worksheet, and
  display it on the screen based on a value you
  enter in a set cell on another worksheet.

3
Step One

• Open Microsoft Excel by double left clicking the
  mouse on the Excel Icon it should look like this

4
Step Two

• Once in Excel you should see a screen that looks
  like this

5
Quick Guide to the Excel Screen
These are the basic bits of the screen we will be
using

• Worksheet tabs,
• - Click these to view other worksheets in your
  project

• Cell Address box,
• This is the address of your cell e.g. A1

Formula bar, or fx bar, -This is where you enter
your formula, like VLOOKUP
6
Step Three

• Enter your data into the cell fields by clicking
  in them and entering your data. Like so

7
Step Four

• Once you have entered your data highlight it

• Then name it Data in the cell address box

• This will name the whole table as Data. We will
  need this later to use the VLOOKUP function

8
Step Five

• Next highlight each column and name it, in the
  address box, according to the title you gave it
  when entering your data

• Do this for all the columns in your data table

9
Step Six

• Next go to sheet 2 by clicking on the sheet tabs.
  Youll be presented with another blank sheet. In
  Cell A1 type Customer number

• At present your screen will look like this

• Double left click the line between A and B to
  resize the columns width. It should now look like
  this

10
Step Seven

• Next in cell A4 type Name and cell A5 Type
  Address. Click in cell B4 and copy the
  following formula VLOOKUP(A2,Data,2).
• This tells the software to look at the value in
  A2, which will be the customer number you type
  in, and match it with column 2 of the data table.
  This will make your screen look like this

11
Step Eight

• At present we have N/A in cell B4. This is
  because we have not yet put a number in cell A2.
  See what happens when you type 1256 in cell A2

• You SHOULD have this happen

• Resize column B as we did column A

12
Step Nine

• In cell B5 type the VLOOKUP formula we used, but
  change the 2 to 3. This will tell the software to
  look in column 3, which is the Address. Do this
  also for B6 and B7 changing the column numbers to
  4 and 5. Your screen should look like this

13
Step Ten

• If you fancy it you add a units used section.
  Enter a formula again to bring this up

14
Test it!

• Congratulations! You have successfully made a
  working VLOOKUP program. But first you should
  test it. Type 1251 and then 1253 into the
  customer number part. This should happen

• If your screen matches the screen shots then it
  works! Well done!