Indirect.

In addition, we'll see how it could be applied in combination with V.

Look up.

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 C six, Right.

Let's see what happens if I select Cell C six.

As the input of the function Excel displays an error message.

Why?

Because see, six is not a text string, and hence the function can't provide a result.

Look at how the result changes.

If I play, see six within inverted commas, See, and why did it work?

Because putting C six 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 C six.

An alternative way of including C six as a text string is to have the letter C in inverted commas.

And then Khan captain ate it with the number six.

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 results 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 texts forming a cell reference the function.

We'll get you there.

All right.

Excellent.

Let's move on to the next sheet.

Now I will show you how to combine V look up 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 V.

Look up.

The look of value will be management.

This reference must not change.

When I dragged the formula to the right so I'll fix the column.

Okay.

Earlier, we learned how to name cell range is remember, here we can use these ranges as an input for indirect.

The best part is these ranges are also the source table for our V Look up 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 road number of this reference.

Okay, finally, him these ranges The information about company D is in the fifth column.

So I'll type five here.

We're looking for an exact match and the function is complete.

Let's press OK hands see the result correct.

The number of managers and 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 lookup tables dynamic because the V look up function we have here can work with two source tables.

You see that the look of table is responsive and collects the data correctly?

The same would happen if I had Maur ranges and I referred to them in these cells here.

Why did I write five to indicate the column?

We're 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.

Ah, function can automate the process.

We'll learn more about it in our next lesson.

Thanks for watching.