Lookup Functions

Part of Subway's nutrition information

Cleanup

Rename the sheet Nutrition
Name the columns.

The USRDA of sodium is 2300mg.

Let's build a table so we can classify the sodium contents of an item.
Add a worksheet called constants
Add
Name the entire table, including headers, Facts
Name this Sodium Table

Classifying sodium

Return to the Nutrition worksheet.
Add a column after H.
Label this Sodium Class
Someone remind me how vlookup works.
Use vlookup to find the proper value

Add a new worksheet called sandbox

In A1 put Item, in B1 put Sodium
Put Bacon in cell a1
Use a lookup formula to find the sodium from bacon.
Change the sort order of the table, sort on sodium low to high.
Is your lookup value still good?
If not, how can you fix it so it will stay good.

A new command, match

Go back to the nutrition worksheet and name the first row (A1:Q1) titles
Back in the sandbox worksheet
In A5 put Category
In A6 put Sodium
In B5 put Position
In B6 put =match(A6,titles,0)
In C5 put Value
In C6, lookup the value for the item in A2 and the category in A6

Let's play with the third parameter to match.

Go to the constants table.
Add the following table
Add a match to find the item in column D, in A2:A6, using the match type in E:G
Can you explain the entries in columns F and G?
Sort the SodiumTable ascending
Can you explain the entries in column E

Go back to the sandbox.

I'd like to look up Chipotle Southwest but can't spell it.
Put a Chip* in cell A2
I want to know about Carbs, but alas, this escapes my ability to spell as well, so put Carb* in A6
This only works for match type 0.

Just to play

Name A1:Q2 on the nutrition worksheet Htable
On the Sandbox worksheet
Put Item in A8
In a9, put

Can you find the row number with for the item with the most calories?

If we wanted to find the item name with the most calories we would have a problem.

For both vlookup and hlookup we need the item we are looking for to be in column 1 or row 1.
This is a disadvantage for this dataset.
We do not want to resort the dataset to change the first item.

Index function

Index(array, row number, column number)
First let's find the name of the fifth item in facts
=index(item,5)
Lets try to find the item with the most calories now.
=index(item,match(max(calories),calories,0))

Can you build an index-match pair to find any nutrition fact for any item?

Can you build this table?