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