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!