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.