INDIRECT Excel Function: how to apply it and combine it with VLOOKUP
Feel free to download our Exercise file and practice along with the video. And don't worry, there is also a Solution file with all the formulas.
We have more explainers on advanced Excel functions you might be interested in - VLOOKUP and MATCH; VLOOKUP COLUMN and ROW; INDEX and MATCH.
In this explainer, we’ll learn how to use another interesting Excel function – INDIRECT.
In addition, we’ll see how it can be applied in combination with VLOOKUP.
Previously, we’ve said it is a common practice to use cell references in formulas. And INDIRECT, if implemented properly, can be helpful when working with cell references. Please follow this explainer carefully to understand how to indirect Excel functions. It can be tricky, but don’t worry, we are here for you.
INDIRECT returns the reference indicated by a text string. So, the input we should have in the brackets must be a text string. What do I mean by that?
Suppose I was interested in the precise number of staff employees working in company A, and I wanted to obtain that number by using Indirect.
In other words, I am interested in the number written in cell C6, right?
Let’s see what happens if I select cell C6 as the input of the function. Excel displays an error message. Why? Because C6 is not a text string and hence the function can’t provide a result.
Look at how the result changes if I place C6 within inverted commas.
See? And why did it work? Because putting C6 in inverted commas makes INDIRECT read the cell as a text string. INDIRECT simply returns the information contained in the reference we’ve specified as text. Here, it gives us the number contained in C6.
An alternative way of including C6 as a text string is to have the letter C in inverted commas, and then concatenate it with the number 6. This way Excel will read it as a text string, and you can obtain an answer.
And the trick here is if I change the number within the function, its result will change as well. If I modify the letter, the outcome will be different. And this is very important. You’ll see why in a second.
So, this is the first thing to remember about INDIRECT Excel functions: as long as the content between parentheses is text forming a cell reference, the function will get you there.
All right! Excellent!
Let’s move on to the next sheet. Now I will show you how to combine VLOOKUP and INDIRECT Excel functions.
Here we have a different table, which provides information about the annual salary of employees.
What I would like is a small table showing the number of employees and salaries of Management and the Staff of Company D.
So, I’ll start by typing VLOOKUP. The lookup value will be “Management”. This reference must not change when I drag the formula to the right, so I’ll fix the column. Ok.
Earlier, we learned how to name cell ranges, remember? And we created range names for Personnel and Salary. Here, we can use these ranges as an input for indirect. The best part is these ranges are also the source table for our VLOOKUP function.
Therefore, the input for Indirect can be the cell where we’ve written “Personnel”. It points to the entire source table above. And to copy the function downwards, I must fix the row number of this reference. Ok.
Finally, in these ranges, the information about company D is in the fifth column. So, I’ll type 5 here.
We are looking for an “Exact Match”… and the function is complete. Let’s press ok and see the result.
Correct! The number of managers in Company D is 3.
Let’s paste the formula in the other three cells and see what happens. The numbers look ok.
Technically, you just witnessed an example of dynamic lookup tables. Dynamic because the VLOOKUP function we have here can work with two source tables. You see that the lookup table is responsive and collects the data correctly. The same would happen if I had more ranges and I referred to them in these cells here.
Why did we write 5 to indicate the column we are looking in? This way, if I changed the company I am interested in, I would still get the results for Company D here, and this number must be changed manually.
We hope this explainer was useful to you. Next, you can watch our video explainer about VLOOKUP COLUMN and ROW: Handle large data tables with ease and take our Introduction to Excel course to enhance your skills.
Next Video: Types of Data
