INDIRECT Excel Function: How it works and when to use it [Advanced Excel]

Key Takeaways

in this video 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 common practice to use cell references in formulas and indirect if implemented properly can be helpful when working with cell references please follow this lecture carefully to understand how indirect 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 C 6 within inverted commas see and why did it work because putting c6 in inverted commas makes interact 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 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 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 okay earlier we learned how to name cell ranges remember you 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 okay finally in these ranges the information about Company D is in the fifth column so I'll type five 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 three let's paste the formula in the other three cells and see what happens the numbers look okay technically you just witnessed an example of dynamic look-up 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 refer to them in these cells here why did I 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 a function can automate the process we'll learn more about it in our next lesson thanks for watching