Excel INDIRECT Function: Lookup Values in Different Sheets / Excel Tabs

(funny cartoon music) Let's take a look at Excel's INDIRECT

function

. Now, INDIRECT is a bit of a strange feature and takes a bit of time to master. But it is very useful because it can easily handle cases where you have

different

ranges that you need to feed other formulas. Let me give you an example. Here I want to get the total income based on the year I select. You only have two years in this example of the divisions I've listed here. Now the tricky part is that my data is not set up in a single data table and a single tab.

It is actually divided into two

different

tabs

. So this tab is only for 2016 data and this tab is only for 2017 data. You can see that I formatted my table as an official Excel table and this one is called data_py from previous year and the other one is called data_current. So what I want to do is get all the revenue depending on this. It's a condition, right? The formula I would use is the SUMIF or SUMIF

function

, but I need to search in different

tabs

depending on the year chosen here. INDIRECT is great for this because you can easily refer to different ranges.

So if you were just doing this and it was just these two options, you could use separate IF statements, right? So you could say IF this is 2016 THEN you can write a complete SUMIFS that references only this tab and IF this is THEN 2017 you can write a complete SUMIFS that references this tab only. What INDIRECT does is write SUMIFS once and then use INDIRECT to reference these things. So it becomes very simple if you have many tabs, you don't need to manage different formulas. Before we get here, let me show you what INDIRECT does. As for the arguments you need, it is very simple.

You only need one required argument and that is to reference a cell or enter text. So I'm going to reference this cell. What do you think I'm going to get? Hello, why? Why doesn't it give me I6? What really is I6? What is in cell I6? It says Hello... So what you are doing INDIRECT, is actually going

indirect

ly to cell I6. It basically uses the cell you give it in the formula as a messenger to know where to go. So cell A6 is the messenger and says "Go to I6." That was the second optional argument you see here.

It says A1. It is true if it is A1 style. So like the style I set up or R1C1 where you reference two row numbers and column numbers. And by default, it is true. What happens if I don't put anything here? Mistake. The messenger has nothing to say. He doesn't know where to go. What if I put something like hello? Mistake. You are trying to search for an address called hello. There is no address, it gives an error. So what happens in this case? Do you think I'm going to get an error here? I get hello...

Why? Why does it give me an error for hello, but returns a value for say hello? Because the greeting is actually an address. And is the address of this cell. Notice that here I have given the greeting name to this cell phone. So you see it as text, but first you translate it to an address. And since it exists, and you can also see it here in the name manager, I gave this cell the greeting name I6 because it exists, and it goes and finds the value that is in the final destination. Okay, just to see that this is dynamic, I'm just going to put an exclamation point there and you can see it here.

If I called A1, I can write it directly here, hello. Now this returns the INDIRECT function. Before we move on to our example, let me show you one more thing because we saw the cell that references part of this. Let me show you what happens if I put this in quotes. So basically I'm converting A6 to text. I receive greetings. Now it's returning what's in this cell. Because what INDIRECT does is translate text to a cell reference and then it gives me what's in that cell. Notice the difference, I remove the quotes, I go to the address which is hello, it acts as the messenger, I put the quotes, it translates this text to a cell reference, it goes here and returns the value.

So the more advanced you are in Excel, the more uses you will find for the different ways to use INDIRECT. Now let's move on to our example. The way I do this is I start very simply. I act like I don't have the division here. I create my formula as if everything were in a single tab. And then I try to figure out how I can use the INDIRECT function in that main formula because doing it all at once can be very confusing. Since I want to get the total revenue, let's assume that only for 2016 I will use the SUMIFS function.

The first thing is the SUM range. Is this. And well, it refers to the table headers. That's exactly what I want. Criteria_range1 is my split, and the actual criteria is in the report and it is this one. Okay, I'm referencing the table headers. I don't need to fix anything. I can just turn it down. So that seems about right. Let's double check these numbers. That's 187. 187. This is my dynamic so far, right? So our goal is to make this part dynamic. So when we're in 2017, it's actually seen in the other tab. What I've done here is put the years I have in the drop down tab and put the tables I should search in.

This is what I don't have in common between these two tabs, right? The rest, the header of both tabs are identical, right? This is also income and division. So, this is part of my cell phone reference. It is not all. I also need to combine it with income. What I can do is do a virtual search here, depending on the search value here. I will search this table and return the table name. and I want an exact match. And I still need the income headers. So I'm going to combine this with my Revenue header. This is exactly how it should look.

Now I'm just going to copy this, press Escape, go here, paste and replace Income with Division. When I change this to 2017, this changes. Okay, so the tables are turning, so my direction is changing. Now what I have to do is replace this reference or this direct reference with an

indirect

reference of this, because I'm adding the revenue first. That's the first part. And then for Division, I need to reference this cell. I want to tear this down. I just have to be careful because I need to fix this. Now this should refer to 2017. So let's check that Utility 2017 is 189. 189.

And in 2016 it is 187. That looks good. So my range references change based on the selection. That's one way you can use the INDIRECT function. Another way to write this, if you want to avoid these helper cells, you can do the virtual

lookup

directly inside the indirect formula. So we're looking for this here. We want the second column to be false because we want an exact match and we're going to combine this with Revenue, but I actually need the quotes because I'm combining text there and closing brackets for INDIRECT. And now I'm going to use the same piece and replace this one and combine the Division.

Okay, before we copy this, let's make sure we've fixed everything. This also needs to be fixed. This needs to be fixed. And this must be fixed. Let's check. For 2016, that was 187. And that's 189. So, this is another way you can use to write the INDIRECT function. Now, one thing you need to keep in mind is that INDIRECT is a volatile formula. And it can really slow things down if you have larger spread

sheets

and use the INDIRECT function a lot. So you are feeding it a lot of formulas. You will see a performance hit. So keep this in mind when using it on larger spread

sheets

. (funny cartoon music)