VLOOKUP() - PowerPoint PPT Presentation

About This Presentation
Title:

VLOOKUP()

Description:

VLOOKUP() Mr Hamflett What does it mean lookup?? Both V and H lookups have the job of referencing data from another table. This means we have two tables. – PowerPoint PPT presentation

Number of Views:600
Avg rating:3.0/5.0
Slides: 16
Provided by: vbc1
Category:

less

Transcript and Presenter's Notes

Title: VLOOKUP()


1
VLOOKUP()
  • Mr Hamflett

2
What does it mean lookup??
  • Both V and H lookups have the job of referencing
    data from another table.
  • This means we have two tables.
  • A source table
  • A reference table
  • A lookup will, given a value from the source,
    look it up in the reference table

3
Looking at a example
  • Below is a list of year 7 pupils
  • Each pupils form group is given
  • Want to use a lookup to get the teachers name.

4
Reference Table
  • A reference table could be created.
  • This table would just list out the form groups
    and the form teachers
  • Much smaller than the source table!

5
So the idea is...
  • We use the source table to look up data in the
    reference table.

Look up value
Into this table!
6
The result off a look up will be
7
So a VLOOKUP...
  • Simply takes a cell.
  • Looks at its value (say 7.6)
  • Looks at a reference table.
  • Finds where it the source cell (looks for 7.6)
  • Then displays the result!

8
Understanding the function
  • We now know what roughly it does, lets look at
    how to write the function!
  • The format is
  • VLOOKUP(SourceCell, ReferenceTable,ColumnToLookup)

9
Source cell
  • VLOOKUP(SourceCell
  • This is the cell which you are using as the basis
    of your lookup

B2
Source Cell
10
Reference Table
  • VLOOKUP(B2, ReferenceTable
  • This is the whole table which you are looking up.
  • Use a cell range OR a table name.
  • IMPORTANT use absolute cell reference!

Whole table is the reference table
11
Column to lookup
  • VLOOKUP(B2, B10C12, ColumnToLookup)
  • This is NOT a cell reference!
  • This is where the V and H lookups differ
  • V stands for vertical
  • H stands for horizontal

12
V and H
  • Or more clearly
  • V looks at columns
  • H looks at rows
  • Expects a whole number (1,2,3 etc)
  • This corresponds to the column or row number the
    answer is in!

13
V column lookup!
  • The answer we want to display lies in the 2nd
    column.
  • Counting starts at 1
  • So we would write 2

14
The complete formula!
  • VLOOKUP(B2, B10C12, 2)
  • Or in words -
  • Look at the value in B2.
  • Find it in the table range B10C12
  • Once found, look at the second column.
  • Display what ever is in there!

15
So the finished table is!
Write a Comment
User Comments (0)
About PowerShow.com