Sometimes we want to pull out data on one sheet from different worksheets, all of them having the same structure.We can do that using the vlookup or hlookup function as the case may be. However, while selecting the appropriate range we will have to go to each tab and select the range manually. This can be very time consuming especially if there are lots of tabs. Excel’s indirect function can help us get around this.

The following example elaborates on “Indirect funtion’s” power.
For simplicity, let us suppose, there are two tabs - A and B. Both of them have names of students along with their marks and in “consolidated” sheet we want to bring in everything on the same page. If we do it using vlookup function, we cannot drag the formula down to the names that are in tab “B” and would need to change the range in the formula as shown below.

This can be easily dealt with by using indirect function along with vlookup.

In order to use indirect function just follow the steps given below:
1) Go to each tab and select the range and give it a same name as the tab using define name feature. But this is just a one time exercise. For e.g. we have named range A1:B15 of tab A as “A” and likewise we have defined B.

2) Now in the vlookup function, we can replace the range with indirect function i.e. instead of entering the range as “A!$A$1:$B$5″ in cell C3, we can use indirect(A3) and simply drag the formula to the last cell without changing the range as shown below.

If we simply enter A3 without indirect then it would not work as it will treat “A” as text.Indirect function gives the value of the cell. Like if cell A1 has value 100, then indirect(”A1″) will give us 100.
However, if we enter “A” manually instead of a range then it will work fine. But it will pose the same problem as changing the range meaning we would need to change the formula from cell C6 and replace A with B.

Popularity: 30%