A Simple Numbers Lookup Example

The Lookup function in Numbers allows you to get a value from one table based on a piece of information. In this example you'll learn how to look up a number from column B based on the text in column A in order to calculate the cost of a project.

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

Video Transcript: It's been awhile since I've talked about Lookup Tables in Numbers. They're extremely useful. So let's just do a simple example. 
Say you're trying to calculate the cost of web development. So you're running a firm here and you've got different types of work that your employees can do and each thing has a different hourly rate. You have a very simple table here with two columns. The first one is type of work like design, coding, testing. The second column has the hourly rate. So design costs $75 a hour while coding costs $125 a hour and you have a bunch of others here. Now in this other table you're trying to calculate the cost for this project. 
So you have some employees and each is putting in a certain number of hours for a type of work. For instance  employee A is a designer going to do eight hours of design work. Employee B is going to do four hours of coding. C is going to do six hour of research. Etc. You have the total here of all of the work being done on this project. 
Now you have to figure out the cost. This is pretty simple to do. All you need to do is multiply the number of hours here by the type of work done, the hourly rate for that type. So for instance for design work you need to go over here and see design work is $75 a hour. It's 8 times 75. I could just go and calculate 8 times 75 and put the number in here. But then I would have to do it for each one of these and I would have to do it every time I calculated a project. It would be great to have a formula that did this automatically. You can do it with the Lookup function. Here's how it works. 
I'm going to start with a formula here. I'm going to hit equals so I'm typing a formula and I'm going to use Lookup. So I'm going to, in the functions over here, search for Lookup, bring it up, and then I can scroll through the notes here and see how to use it so I'm ready. This is how you use it.
I'm going to first multiply the number of hours, so this cell B2, by the cost here. How I do the cost is I'm going to lookup the type in this column and then pull that number there. So I'm going to use the Lookup function. The first parameter is this here, the actual type of work being done. So cell C2 which is design. Comma. The next thing I want to do is where do I look it up. I'm going to look it up here in column A of this table. So I'm going to click on A here. Great. Work type is column A. 
Then once it finds it, it's then going to pull the value from column B so the hourly rate here. That's it. It's going to take the number of hours, multiply it by the type of work looked up in column A of work types returning the value, the hourly rate, for that. I hit Return and get 600 which is 75 times 8. 
Now if I copy this and I then select all of these and paste it's going to paste this formula in. Of course it's going to move so now it's doing B3 and C3 in the formula here. B3 and C3 but it's still looking up C3 in the column A and column B for here. So I get 4 hours of coding which is $125 a hour that's 500. So all of these now Lookup and I have just a simple sum formula in here, sum of the whole column to give me the total. 
The great thing is I can go and change something here. For instance you can see testing 5 times 50 for testing is 250. But if I say oops, testing is supposed to be $60 a hour. I change it there and you can see it automatically updates. Now testing shows 5 hours is a total of $300 and the total is updated. 
That's basically how you do Lookups. It's very handy. It means that I could have a really long table here if this is a huge project and all of this will be looked up from this table and used in the formulas in this table. These tables can be on separate sheets. You don't have to have this on sheet one. So I could put this on sheet one or sheet two and this table here on a separate sheet. That way, maybe if the client goes and sees this or I print this out, they don't see my list here, they only see this one.

Comments: 3 Responses to “A Simple Numbers Lookup Example”

Sean Chandler

7 years ago

That was cool - how about a follow-up, showing how one could build a quote from that, maybe showing only column C and D from the right-had table, in a Pages doc?

Gary Rosenzweig

Sean: That's pretty specific. Probably best at that point to just take my Numbers course and work on your own to come up with exactly what you need.

Randy Miller

Thanks so much for this, Gary. I did not realize there was a lookup function, I had been trying to work on a monthly budget and get a running total for each month, in the past I had to manually copy everything, from one spreadsheet to another. this will save me a lot of work and time. Thanks again for doing this.

Comments Closed.

A Simple Numbers Lookup Example

Comments: 3 Responses to “A Simple Numbers Lookup Example”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information