Hi this is Gary with MacMost.com. Let’s look

at using Multiple Tables and the Lookup function in Numbers. I’m using Numbers version 3 here. I’m going

to show you how to create a Multi-Table spreadsheet and why you should be using Multiple Tables. We’re going to start off with a blank spreadsheet

and it’s going to put in a default table which we are going to shrink down to just be the

size that we need it. So we’re going to start off with basically a single table here like

this. Now I’ve created a table here that has basically

some products and some cost values. I’m going to use this now in another table, I’m going

to create this one, and this is going to actually be a table of orders for a store. So let’s

shrink this down here and create this one and we will say that we’ve got the order and

the product and then actually let’s do the count, the amount of them, and then we’ll

do one more that is the total cost. So for instance we can do here; Order 1, apples,

and there is 87 apples in this order, and we want to figure out the total cost. Now

to do this, what you might be tempted to do of course, is say this is equal to 87, C2

in this one times, and then we go over to here and we hit cost apples and return. Sure

enough we get the proper answer there. So all we would have to do is every time we add

a new order then we go ahead and redo this formula here (equals this times and then we

have to find it in here and do peaches.) There. But there is a better way. Instead we are going to do a formula that

uses the Lookup function. We’re going to lookup the cost in the table. So we’re go start off by saying we want to

start with the count here. So C2. Then we’re going to multiple that. Then we’re going to

do the Lookup function. You can certainly view the function browser if you want to look

up the details of this. But we’re going to lookup the value of this, B2, in column A

of table 1 and get the result from the cost column in that same table. The result is that we’re going to get the

same thing. It is actually going to look it up and I can copy it and paste it in here

and get that answer. So let’s see what happens now when I add another order. So oranges,

must match exactly this, and we’re going to order 100 oranges. If I copy and paste this

in here we should expect the answer 100 times 30 cents. That is exactly what we get. So

we can simply copy and paste this formula in each order to get the result we want. It gets even better than that. We only have

three orders. We shouldn’t be having all these blank rows here in the table. We should only

have three because we have three orders. Now it is time to add a fourth order. I’m just

going to just hit return here to create a fourth order. You can see right away there

is something in this. There is a formula. Of course the formula is not working because

there is no data there. Let’s enter some data. So this is going to be order four. Let’s order

some kiwis. You can see right away it changes to zero. The count is zero. So we’re going

to order twenty of them. You can see that 20 times $1.20 gives us $24.00. I can continue to add another row there and

the formula will follow us through each of those. We have a very simple way to basically

look something up in a table, make a computation, and carry that forward in each row. Another cool thing is that this is all linked

together. So if I were to change the price of apples here you can see that it automatically

updates there. So there are many reasons why you should be

using multiple tables. You should always be looking for ways to break up your data into

different tables where it makes sense and not repeat data. Like, for instance, I could

have put a cost column here and then continued to add 55 cents every time I did apples as

a product but instead I did it this way and now I’ve got it all in one location. I could

do the same thing with price and other things and compute different things in different

tables using the Lookup function or using all sorts of different other functions that

will then go across tables. So a lot of good reasons to break your data

up into tables besides just looking neater and easier to find things.