Using Multiple Tables And Lookup In Numbers

There are many advantages to using multiple tables in a single spreadsheet in Numbers. Take a look at how you can use two tables with the LOOKUP function to make computations easier. The LOOKUP function will grab data from another table by looking up a value in one column and returning the value of another.

You can also watch this video at YouTube (but with ads).

Video Transcript: 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.

Comments: 6 Responses to “Using Multiple Tables And Lookup In Numbers”

Don Wyman

11 years ago

Another great job; informative and fun use of tables in Numbers. Thanks for the learning opportunity.
dgw

Brian O'Hara

How can you create a drop down/pop up menu of products in that column in the second table using the entries in table 1 ?

Gary Rosenzweig

You can't populate a menu with contents from a table.

Donna Woeckener

I have two tables, one is for Jan -Jun, the other is for July-Dec. I want to take the total sales from table 1, and have a formula add it to the sales for July-Dec to get a grand total. How can I do that?

Start a new formula. Type =. Click on the total in the first table. Type +. Click on the total in the second table.

David Berger

Thank-you Gary, this, I think, is exactly what I need to keep track of behavior percentages of my students!! Awesome job!

Comments Closed.

Using Multiple Tables And Lookup In Numbers

Comments: 6 Responses to “Using Multiple Tables And Lookup In Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information