Video Transcript
Hi, this is Gary with MacMost Now. On today's episode I'm going to show you how to use the Lookup function in Numbers.
So it seems this comes up for people quite a lot. I get a lot of questions about this. Say you want to have a column in Numbers where you choose an item. For instance a fruit stand. You want to choose what item of fruit was sold; apple, orange, banana, and etc. You want then, based on that, to automatically populate the next cell with the price of the item instead of having to enter the item in every time. Well, you can do this of course. A lot of people know you can use pop-up menu cell so you can choose from a list of items. But they don't know how to use the Lookup function to be able to then grab the price related to that item. Let me show you.
Okay, I set up two tables here in Numbers. The first thing I did was to set up this main table which is going to list items sold. I set it up in a very precise way. I created this table here, let me bring up the Inspector, and you will see that for the table it has one header row here and one footer row. Nothing, no header column here. I put in the header row its product and price and the footer row I have a formula here which is basically the sum of this column.
Now the way this is setup with the header and the footer row and the one thing in the middle here is that I can expand this by simply pulling down. You can see how it duplicates each row. So if I want to add a new item all I need to do is pull this down and I get as many as I want.
In this cell here I created a cell that is a pop-up menu and I populated the pop-up menu with a bunch of different items here that could appear in it. Now I have also added in there a first item called "choose item" which will basically be blank so every time I go create one it is not populated with apple it is just populated with choose item. It is kind of a blank item there. That's it.
The price is blank. So what you would do normally here is to choose something like apple and then you would go here and manually enter the price. What would be nice is to have a way to enter the price in automatically simple by choosing the product here on the left.
The way that some people try to do this is that they try to use "if" statements. So for instance you could say equals if and you would say this row here equals apple then its value is .4 otherwise it is zero. You can see it is zero there and if I change it to apple it would be .4.
Now if I want to add orange what I need to do then is to change this value instead of zero it would be another if, if product equals orange then the value is .25 otherwise zero. Now I have something that actually works here and I can do apple, I can do orange, I can do something else at zero. So I can keep nesting it and it gets very long and complex. And it's probably not the right way to do it.
So you may have already noticed there is this other table I have created here. It is a simple little table and it just has a list of product names and it has a list of prices here on the right. I have included choose item which is the first option here in this pop-up with a blank here. I'm going to use this to basically look up the value for here based on what's here. The way you do that is to use the Lookup function. So I say = Lookup and the first part of Lookup is what to look up. So I am going to do product. Then comma. Then I am going to do the row of this table of where to look up that product so I will click on A there. Then the next part is the price. I'm going to click on B. So Lookup product in this row of this table and put the value of this row in this table. Then return. So you can see right there that it picked up pear at .25 and banana is .45 and if I click choose item you can see it is zero.
So now I can go ahead and add a bunch of different items here and you can see it fills each one in automatically based on this table. The best thing about it is that I can change the price over here and you can see it automatically reflects that over there.
Now this grocery store sales list example is really a very simple one and you might use this in a completely different way. The main point here is that it gets you to look at some of these functions like "Look up" and see what they can do for you and how they can make some of your spreadsheets easier to manage. Go through Help and the Help Menu under functions help. You can read and look at examples for all these different functions. If you use Numbers a lot you can actually browse through it and see what is available.
Hope you found this useful. Until next time this is Gary with MacMost Now.
So it seems this comes up for people quite a lot. I get a lot of questions about this. Say you want to have a column in Numbers where you choose an item. For instance a fruit stand. You want to choose what item of fruit was sold; apple, orange, banana, and etc. You want then, based on that, to automatically populate the next cell with the price of the item instead of having to enter the item in every time. Well, you can do this of course. A lot of people know you can use pop-up menu cell so you can choose from a list of items. But they don't know how to use the Lookup function to be able to then grab the price related to that item. Let me show you.
Okay, I set up two tables here in Numbers. The first thing I did was to set up this main table which is going to list items sold. I set it up in a very precise way. I created this table here, let me bring up the Inspector, and you will see that for the table it has one header row here and one footer row. Nothing, no header column here. I put in the header row its product and price and the footer row I have a formula here which is basically the sum of this column.
Now the way this is setup with the header and the footer row and the one thing in the middle here is that I can expand this by simply pulling down. You can see how it duplicates each row. So if I want to add a new item all I need to do is pull this down and I get as many as I want.
In this cell here I created a cell that is a pop-up menu and I populated the pop-up menu with a bunch of different items here that could appear in it. Now I have also added in there a first item called "choose item" which will basically be blank so every time I go create one it is not populated with apple it is just populated with choose item. It is kind of a blank item there. That's it.
The price is blank. So what you would do normally here is to choose something like apple and then you would go here and manually enter the price. What would be nice is to have a way to enter the price in automatically simple by choosing the product here on the left.
The way that some people try to do this is that they try to use "if" statements. So for instance you could say equals if and you would say this row here equals apple then its value is .4 otherwise it is zero. You can see it is zero there and if I change it to apple it would be .4.
Now if I want to add orange what I need to do then is to change this value instead of zero it would be another if, if product equals orange then the value is .25 otherwise zero. Now I have something that actually works here and I can do apple, I can do orange, I can do something else at zero. So I can keep nesting it and it gets very long and complex. And it's probably not the right way to do it.
So you may have already noticed there is this other table I have created here. It is a simple little table and it just has a list of product names and it has a list of prices here on the right. I have included choose item which is the first option here in this pop-up with a blank here. I'm going to use this to basically look up the value for here based on what's here. The way you do that is to use the Lookup function. So I say = Lookup and the first part of Lookup is what to look up. So I am going to do product. Then comma. Then I am going to do the row of this table of where to look up that product so I will click on A there. Then the next part is the price. I'm going to click on B. So Lookup product in this row of this table and put the value of this row in this table. Then return. So you can see right there that it picked up pear at .25 and banana is .45 and if I click choose item you can see it is zero.
So now I can go ahead and add a bunch of different items here and you can see it fills each one in automatically based on this table. The best thing about it is that I can change the price over here and you can see it automatically reflects that over there.
Now this grocery store sales list example is really a very simple one and you might use this in a completely different way. The main point here is that it gets you to look at some of these functions like "Look up" and see what they can do for you and how they can make some of your spreadsheets easier to manage. Go through Help and the Help Menu under functions help. You can read and look at examples for all these different functions. If you use Numbers a lot you can actually browse through it and see what is available.
Hope you found this useful. Until next time this is Gary with MacMost Now.
Thanks for this video.
Question 1: Can the lookup function look to other documents?
Question 2: Does this also work in Pages?
I am seeking a good document automation solution.
Scott
I don’t think it can look in other document. But try it. Maybe.
Now, I don’t think this would work in Pages — but also, try it. You have the same functions there.
Is there a way to populate the Pop-Up Menu from a List rather than typing in the values?
Sunny.
I don’t think so, no.
Wow great tut. Amazing what numbers can do. Can you do a video on how to transpose in numbers please?
Transpose? Can you be more specific?