More Lookup Functions

• There are two more lookup type functions we need to discuss.
• Start by downloading this document
  • This is the data for homework 3.
  • Look at the about page.
  • Look at the other pages.
• Let's start by making a copy of the raw data.
  • Call it Lookup Work.
• To make life easier, let's name some columns
  • Name all the columns on the Lookup Work worksheet
  • Name all of the columns on the Currency worksheet
  • Name all of the columns on the Country Codes worksheet
• Move to the Lookup Work tab
  • I would like to have column C contain the currency name based on column B and the currency worksheet.
  • The problem is, the Alphabetic Code on the worksheet is in the wrong place.
  • I could move the columns around, but that might be annoying or even impossible on a larger worksheet.
• The lookup function will help here
  • This is on page 333 of the book.
  • It takes two or three arguments.
  • It is row/column agnostic.
  • But requires the data to be sorted by the lookup column/row
    • So go to the Currency tab and sort based on currency
    • Selection may be a problem because of blanks in the dataset
    • But be persistent.
  • Go back to Lookup Work and insert a new columns after B.
    • Call this Currency Name
  • Try the two argument lookup function
    • Look for b2, in Alphabetic_code
    • 
    • Not quite what I was looking for
  • Try adding the currency array
    • A bit of a problem here, bad planning, there are three currency values
    • do the worksheet dereference.
    • 
  • Can you use the alpha 2 country code to add the country name to the table after column J?
    • 
• A problem
  • Based on the name in the new column K, can you look up the UN_Code?
  • Well not without resorting the table, and that would mess up the column K lookup.
• index(match)
  • Remember, index takes a range, row and possibly column number and returns an item.
  • Match will supply that row number
  • match takes
    • A cell to look up
    • A range to look this cell up in
    • A match type
      • -1 means do a greater than match
      • 0 means do an exact match
      • 1 means to a less than match.
    • Match will return an error if not found for any reason
    • For now let's try to find the ALPHA_2 code for the country name.
    • 
  • Notice, this is very useful for the CCodes table.
    • I probably want to be able to look up by all three types of codes.
    • But they don't have the same sort order.
  • Can you modify the currency sheet to provide the UN_CODE in column D?
    • I can with errors
    • 
    • But wrap that in an if
    • 
  • How many Entities are we missing UN codes for?
  • Can you do the opposite?
    • Add the Currency Code to the CCodes table based on the entity name?
• Here is someone who thinks index/match is better than *lookup
  • Reduction of errors
  • Inserting a column / row will not break it.
    • xxx will not change if new rows/columns are inserted
    • =vlookup(value, table, xxx)
  • Flexibility (h/v lookup, left to right, ...)
  • Some articles say speed as well.
• I feel we need to explore match/index a little more.
  • This is following a discussion with Dr. S.
  • Add a new worksheet, Sandbox
  • in a1:c1 put 1 3 5
  • Name this rowa
  • Build the following table
  • 
  • Add the following formula
  • 
  • Can you explain the results?
  • Change rowa to be 5 3 1
  • does this help?
  • Read the help page for match.
  • Does this help?
  • I find our book is wonderful for ideas
    • But I often need to experiment
    • And to read other sources.