Using a VLOOKUP and what it does - PowerPoint PPT Presentation

1 / 14
About This Presentation

Using a VLOOKUP and what it does


Next in cell A4 type 'Name' and cell A5 Type 'Address' ... At present we have #N/A in cell B4. This is because we have not yet put a number in cell A2. ... – PowerPoint PPT presentation

Number of Views:330
Avg rating:3.0/5.0
Slides: 15
Provided by: AAl6
Tags: vlookup | and | can | cell | get | in | name | number | put | using | where


Transcript and Presenter's Notes

Title: Using a VLOOKUP and what it does

Using a VLOOKUP and what it does
  • By B. C

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.

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

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

Quick Guide to the Excel Screen
These are the basic bits of the screen we will be
  • Worksheet tabs,
  • - Click these to view other worksheets in your
  • 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
Step Three
  • Enter your data into the cell fields by clicking
    in them and entering your data. Like so

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

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

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

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

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

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

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

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!
Write a Comment
User Comments (0)