Vlookup

• Please download this worksheet.
  • This is based on data from The Bureau of Labor Statistics
  • I have modified this data quite a bit for our purposes.
  • I find these reports to be fascinating
  • In addition, looking at the associated pages gives real insight into exploring data.
• This data is typical of data stored in a software system called a Database
  • Data stored in a database is normally stored in tables.
  • Tables tend to cross reference each other.
    • Look at the table on the Occupations Table tab.
      • The occupation code is in all other tables.
      • But the text is not.
    • Database people like to reduce "redundancy"
      • Or they don't like to have the same thing typed multiple times.
      • On this table, for example, they probably would have built a sub table of Occupation Type which would only hold two values (Summary and Line Item)
      • This tends to reduce mistakes and space the tables take up.
      • The data custodian could merge this for you if they know how
      • This is based on a computer language called SQL or Structured Query Language.
  • What is on the Occupations Table tab?
  • The Education Worksheet is much more like databases.
    • The main data is in A11:D830
    • There are three sub tables.
    • I would normally put these on their own worksheet, but I wanted to make our first stage task easier.
  • The Employment Trends worksheet contains predictions of job growth.
  • We will eventually make this table quite a bit larger.
• The VLOOKUP command
  • As we have seen in the past allows us to look up a value in a table and return an associated field.
  • The first argument is the thing we are going to look up.
    • For example, on the Education sheet, we might want to lookup the education level, the experience level, or the training level and replace it with the description.
    • On any sheet, we might want to look up the Code and replace it with the title.
  • The second argument is the table in which we are looking up the data.
    • For Vlookup the table needs to be organized in rows (each row represents a record).
    • Hlookup needs the table in columns (each column is a record)
    • The first column needs to hold the "key" or thing we are going to look up.
    • Depending on the type of lookup, the table might need to be sorted, in ascending order, by the key.
    • Do not include the column headings in the table in this case.
  • The third argument is the number of the column to be returned.
    • The key is in column 1.
  • The fourth argument is optional
    • It depends on the type of lookup that will be performed.
      • When we looked up grades based on percent score in the gradesheet, we used an approximate match.

        Score	Grade
        0%	F
        60%	D
        70%	C
        80%	B
        90%	A

      • In this case, the function looks for the first argument between the starting value and less than the next value up.
      • If there is no next value up, the last value is selected.
      • The order of the key is important here.
    • In this case, it is called an approximate lookup.
    • And the fourth parameter is not needed.
    • When we are looking for an exact match it is called an exact lookup.
    • And the fourth parameter needs to be set to false
• Let's do a quick example.
  • Go to the Sandbox tab, it is clear at the end.
  • Enter a score of 80% in cell A1
  • Use a vlookup to find the associated grade
    • The first argument is A2,
    • The second is F2:G6
    • The third argument is 2
    • Since this is an approximate lookup, there does not need to be a fourth argument.
    • 
    • 
  • Find the appropriate note based on the score
    • Again, this is an approximate lookup
    • The only real difference is we want the third column back
    • 
    • 
  • Fill in some more scores so you have each type of grade (A3:A6)
  • 
  • Now sort F2:H6 in descending order.
    • What happened to B2:C6 and why?
  • What would we need to do to look up the note based on the grade?
    • Insert a new column between C and D.
    • Call this Note2
    • Insert the formula
    • =vlookup(b2, $G$2:$I$6, 2)
    • 
    • What happened and why?
    • Change this to be
    • =vlookup(b2, $G$2:$I$6, 2, false)
    • What happened and why?
    • 
  • Finally, look up the Comment in the table G9:H13
    • What type of a lookup with this be?