More Lookups

• Grab this
• This is Applebee's Appetizers Nutrition information.
  • But it is a bit of a mess. Let's clean it up.
  • What can we do about the item names
  • And about the column headings.
  • I eventually got this
  • 
• What should we do about the <1 and <5?
• Name the columns, but DO NOT USE THE ROWS
  • 
  • Name the entire table Nutrition
  • Name the titles as Titles
  • Rename the sheet to be Raw Data
• Make a new sheet called Sandbox
• Just a reminder, we will review match and indirect
  • In A1 put Max Category
  • In A2 put Sodium
  • In B1 put Value
  • In B2 put a formula that finds the maximum of the category in A1
  • =max(indirect(a2))
  • Switch A2 to calories, did this work?
  • In C1 put Row of Max
  • In C3 find the row number of the item with the maximum value for the category
  • In d1 put Item with max
  • In d2 put =index(appetizer,c2)
• The index function
  • Takes a range as the first argument.
  • With only a second argument, it will return the item at this position in the range
  • If given two additional arguments, it will look items up in a table.
• The nice part is, it is row/column agnostic
  • In A4 put Title Number
  • IN B4 put Title
  • In A5 put a number
  • In B5 put =index(titles,a5)
• And if we wish we can do a 2D lookup.
  • In A7 put Item
  • In A8 put Chips*
  • In B7 put Row
  • In B8 find the row number for the item listed in A8
  • in C7 put Category
  • In C8 put Sat*
  • In D8 put Column
  • In D8 find the column for the item in D7
  • In E7 put Value
  • In E8 put = index(Nutrition, B8, D8)
  • Looks like there is an error, why?
  • Fix this, smile
• Xlookup is here
  • xlookup
  • I don't have it on my machine yet.
  • But it is just a simple version of index/match.
  • It does have a default of exact, so this is nice.
  • I like index/match better
  • There appears to be an xmatch as well (carefully googling this)
• Indirect
  • Just a word of caution, it causes an entire recalculate.
  • Excel apparently maintains a tree of computations
  • in C4: =f(c3,b4)
  • In C3: =f(c2)
  • In C2: constant
  • In b4 constant

  •                b4
                   |
                   v
           c2->c3->c4 
            

  • As long as none of these cells change, the chain does not need to be recalculated.
• He gives many diffent scenarios and use cases for lookup functions in this chapter, worth a read, and frequent review.