Create Powerful Numbers Spreadsheets With the INDIRECT Function

Hi, this is Gary with MacMost.com. Let's look at the incredibly, useful INDIRECT function in Numbers. 

MacMost is brought to you thanks to a great group of supporters. Go to Macmost.com/patreon. There you can read more about it, join us and get exclusive content.

So you can accomplish a lot in a Numbers spreadsheet using something like the Lookup function. But the INDIRECT function can actually be more powerful. You can do some really clever things with it. Here's the basic way that INDIRECT works. Say I want to get the value of apples at the Central store which is sixty cents. I could go into a cell here, type the equals key to enter a formula mode, and I could type C2 and hit Return. It's going to covert that to Central Apples because that's the name of the row and column and it will give me that value. I could also just have typed equals and then clicked here and I get that.

I can use INDIRECT to get that in an indirect way. So I'll enter a formula and do Indirect as the function and parenthesis. Then I can do in quotes something like C2. Then I also get that same value. So it's basically saying okay do an Indirect lookup of this string, this piece of text here C2, what's the value of C2. Okay. Now that may not seem like it's an improvement over this. This seems a lot easier. But now you can do things like this. You can say Indirect and say give me the Indirect value of whatever the value in this cell is. B10. I close it and I get an error because B10 is empty. But if I enter the text C2 into B10 I get $0.60 because it's saying INDIRECT lookup of the value of B10 which is C2 and it looks up that value. If I change this to say C3 you can see it goes and says $0.65 because now it's looking up Central Oranges.

So you can use INDIRECT to grab a value from one place and then find the value based on the first value, there. You can do that in another table as well. You just need to add the table name. So I could right here say INDIRECT and then in quotes you could say prices, since that's the name of this table here, two colons, and then I could say C2. It will get me that value.

Now things get really interesting when you start to use row and column names instead of a labels like C2. So, for instance, I can do INDIRECT and instead of C2 I can simply say, okay Central Apples and it will give me the value. I can do the same thing here. I can say Prices colon colon Central Apples and it will look it up. As a matter of fact it's even smarter than that because I don't need to include the name of the table. I can just say Central Apples. Why does that work? Numbers says, well Central and Apples only applies to this table. So it must be from this table where that value is needed. So I don't have to do anything special, like provide the table name, to reference that value in another place.

Now I can go even further than that and do something like this. Let me add another column here. Let's say Central and Apples are the values in these cells and I can do INDIRECT and say okay Central and then use an ampersand and then a quote, a space, another quote to insert a space between that and the value for this and now it's going to put together Central and then a space and then Apples and get the INDIRECT lookup for that and I get $0.60. The cool thing about that is I can change this. I can say West and you see that value updates. I can say Grapes and the value updates again. So it's going to lookup whatever values are here inside of this INDIRECT.

One cool thing about the INDIRECT function is you can so easily change the table reference in addition to changing the row or column reference. So, for instance, here we have two different tables representing possible costs and prices for products. Then we want to calculate, basically, the profit based on one of those two tables. So we have a cell here for Apples, fifty of them. What's the cost of that going to be using the current pricing. Well we can use INDIRECT to look that up. So, we can start with quote and then Current for the name of the table with two colons, and then we'll add to that the name of the product here, so Apples, and then we'll add to that a space and Cost. From that we get sixty cents. 

Now we want to multiply that by the amount. So we'll add that here at the end. Times and then we'll do that amount. So that's the total there. We can do the same thing for the Sale price except use Price instead of Cost. We can do INDIRECT and then Current, colon colon, ampersand, and then the value there. Then ampersand, quote, space, price. Close that off and we don't need the extra parameter there. Multiple that by the amount and we get the Sale. Profit is then the Sale price minus the Cost.

So what if we wanted to do that but instead of Current do Proposed. So I can go into here and I can change Current to Proposed. Now it's the Proposed Cost and I can change this one to Proposed as well. But we don't want to have to change each one those each time and then if we have a whole bunch of rows here we have to change for everything. So instead let's create another table here with just one cell in it. So this will be a really simple table. I like using one cell tables a lot for things just like this. 

So we'll call this one, Which Type. Whatever you want to call it. The value here will be something like Current. Okay. Now we can take that value here and substitute it in here. So I'm going to take away everything but leave the two colons there and before it I'm going to put the value of this and than an ampersand. So the Which Type A1 and then it's going to add two colons and then continue as before. 

Make sure that this is set to be an Absolute Value so where I paste this it's always going to refer to that one cell.

I'll do the same thing for here. I'll take away Proposed and add a reference to this cell and make it Absolute. Now the cool thing is if I go and change the value of this from Current to Proposed it changes that. I can have fifty rows here of different sales and it would change for all of them. I could switch between the Current prices and the Proposed prices really easily. As a matter of fact I can make this a Pop Up menu with Proposed and Current as the two values and now I can switch between them very easily to see the changes here. Add a little Total there at the bottom and I can see how, say, a month's worth of sales would be effected by changing from the Current pricing to the Proposed pricing.

So one last tip I want to show you is you don't even need to have a column name if you only have one column in the table. So, for instance, I will go and delete all of this here and shrink this table down. I will even get rid of this Header row here. Basically what I have here is a quick lookup table of prices. Then I could go in here and do INDIRECT's to lookup a price. So INDIRECT and then just say apples. It will find it because it knows that the only table that has apples in it as a header is here and there's only one value it could possibly be. So I could in fact go and say something like Apples and then INDIRECT and then take the value from this and it will look it up. So then I could change this to Oranges and it will look it up. So you can do it even simpler if you have a table, a quick lookup table like this, with INDIRECT.

You can also refer to different sheets as well. So here's Sheet 1 as before and I've got a Prices table and a price for Apples. I've got Sheet 3 and there's also a Prices table and a different price for Apples. So in Sheet 4 you see if I just were to type equals and then go over to Sheet 1 and say click on the cell here it gives me what it should look like. So Sheet 1 and then two colons, Prices two colons and then East Apples. So I could do the same thing with INDIRECT.  Do INDIRECT and then in here do Sheet 1, colon colon, Prices, colon colon, East Apples and get a value. But if I changed it to Sheet 3 then I get a different value. Of course you can change these Sheet's names, I could change this to Future for instance. So in here now I would have to change from Sheet 3 to Future. 

So you can do all three. You can refer to Sheets, Tables, and individual cells inside of a table. You can see there's a ton of power inside of the INDIRECT function. If you want to take your spreadsheets to the next level practice using the INDIRECT function, see what you can do, and how you can apply it to your projects.