Articles Blog

Using Multiple Tables And Lookup In Numbers (#958)

Using Multiple Tables And Lookup In Numbers (#958)


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.

Leave a Reply

Your email address will not be published. Required fields are marked *