1 / 15

VLOOKUP()

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. A source table A reference table A lookup will, given a value from the source, look it up in the reference table.

natalied
Télécharger la présentation

VLOOKUP()

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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, $B$10:$C$12, 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, $B$10:$C$12, 2) • Or in words - • Look at the value in B2. • Find it in the table range $B$10:$C$12 • Once found, look at the second column. • Display what ever is in there!

  15. So the finished table is!

More Related