1. Welcome to Chandoo.org Forums. Short message for you

Hi Guest,

Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

Yours,
Chandoo
2. Hi All

Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

Post Spam and you Will Be Deleted as a User

Hui...

3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup indirect

Discussion in 'Ask an Excel Question' started by mithil1, May 18, 2017.

1. mithil1Member

Messages:
61
So I am using the below syntax to pull certain information. I am able to get the correct answer however, when I drag the formula to another cell, indirect ref doesnt move from A2 to A3, A4 so on and so forth. Would anyone have an idea?

VLOOKUP(\$B\$16,INDIRECT(A2&"!A\$1:C\$50"),3,FALSE)
2. HuiExcel NinjaStaff Member

Messages:
10,685
Mithil

Firstly, Welcome to the Chandoo.org Forums

Does you cell have an equal sign at the start of it?
=VLOOKUP(\$B\$16,INDIRECT(A2&"!A\$1:C\$50"),3,FALSE)

If it does can you please post the values in A1, A2, C50 and B16
3. mithil1Member

Messages:
61
Thanks Hui for the reply. Yes, there is an equal sign at the start of Vlookup. So by this vlookup I am trying to pull some information from a cell which is located in a different tab/sheet. Let me explain you what these cells contains :

Summary Tab : Cell B16 as mentioned in the look up value
Tab 2: Let's say the name of the tab 2 is Newyork and the cell A2 in indirect is Newyork in tab1 or summary sheet, so what this indirect is doing is combining Newyork with ! so looking at Newyork tab then it looks at table array A1 to C50.

B16 in Newyork/tab1 is the word total so basically its looking at word total in sheet 2 and giving me the amount.

Now when I try to pull it from Newyork/tab 2 to tab3(different city) then value gets pulled is the same as the value of the previous cell and it doesnt move on its own from tab 1 to tab 2 to tab 3 etc. Got it ?
4. HuiExcel NinjaStaff Member

Messages:
10,685
This works for me
=VLOOKUP(\$B\$16,INDIRECT("'"&A1&"'!A\$1:C\$50"),3,FALSE)
note changes in red
5. mithil1Member

Messages:
61
Hello Hui,

Thanks for the reply. Its not working in my spreadsheet becasue it says it has circular reference. I think (""&A1) is not solving the problem. Could you please tell me why have you added the letters and symbols which you marked in Red ?

If you notice A1&"!A\$1:C\$50 is looking at information in sheet 2. Here A1 is Newyork in Summary sheet and its looking at information at tab named Newyork.

To simply the formula

Vlookup("Total",Indirect(Newyork,200),3,False) so basially I am trying to pull 200 from the second sheet. The problem I am facing is I have several sheets with different cities. When I drag the above formula, it doesn't change cities which are tab names.
6. HuiExcel NinjaStaff Member

Messages:
10,685
INDIRECT("'"&A1&"'!A\$1:C\$50")
evaluates to:

'Newyork'!A\$1:C\$50

which is then used by VLookup and returns different values for me from different sheets depending on what is in Summary!A1

Please post your file or a sample file
There's too many assumptions being made here
7. mithil1Member

Messages:
61
Hello Hui,

It worked out for me!! It was error from my end. The look up value is not \$B\$16 but rather its \$A\$16 and thats the reason I was getting an error of Circular reference. So it works fine but I have two more questions.

1) Why did you you add ""& in indirect. what is ""
2) I get #REF for the tabs that have two names for example Newyork - East or Newyork E, it works fine for Single word tabs.

I really appreciate your help. Thanks!!!
8. HuiExcel NinjaStaff Member

Messages:
10,685
You are getting an error because it is not ""
It is "'"

Also don't forget the other '

That allows sheetnames with spaces
Last edited: May 21, 2017
9. mithil1Member

Messages:
61
Thanks Hui. Its working!! I am not sure how many questions I can ask but suppose if I wnat to take this forumala a step further and would like to add Concatenate within indirect formula is it possible ? because some of the sheets are having more columns so if I concatenate then I can pull the correct info.
10. HuiExcel NinjaStaff Member

Messages:
10,685
You can do whatever you like inside an Indirect() function as long as it evaluates to a legitimate range

ie: you can have variables for the Workbook, Worksheet, Rows and Columns according to your needs
11. mithil1Member

Messages:
61
Thanks Hui. I tried to but can't get the concatenate correct. The problem I am facing is some sheets have the total in column C and some have it in D. So if we continue with above example lets say I want to pull the total amount of cars in a city. In some sheets it is column C and in another its column D. So my idea was to use concatenate "total" and # cars. That way I can pull the data from the correct column irrespective of column number.

The above formula works fine but it pulls the data only from one column and there are chances of errors as it may pull the wrong "total amount"

I really apprecite your patience. Also, if you could give me some ideas on how do I improve on such formulas and you would have got an idea of my profiency in excel so what do I do to improve and become better ?
12. HuiExcel NinjaStaff Member

Messages:
10,685
Still waiting for that sample file ?

13. HuiExcel NinjaStaff Member

Messages:
10,685
The 3 in
=VLOOKUP(\$B\$16,INDIRECT("'"&A1&"'!A\$1:C\$50"),3,FALSE)
is the column

So you will need to use a Match() function to find which column has Total
like: Match("Total",INDIRECT("'"&A1&"'!A\$1:A\$50"),0)

Hence the total formula becomes:
=VLOOKUP(\$B\$16,INDIRECT("'"&A1&"'!A\$1:C\$50"),Match("Total",INDIRECT("'"&A1&"'!A\$1:A\$50"),0),FALSE)
14. mithil1Member

Messages:
61
I have attached

File size:
33.7 KB
Views:
8
15. bosco_yipWell-Known Member

Messages:
1,247
Try...........

In "Summary Sheet" B2, formula copy down :

=VLOOKUP(A\$5,INDIRECT("'"&A2&"'!A1:D50"),MATCH(B\$1,INDIRECT("'"&A2&"'!A1:D1"),0),0)

Regards
16. mithil1Member

Messages:
61
Cool. This is working!!
17. mithil1Member

Messages:
61
Thanks!!!
Thanks alot..
Chirag R Raval likes this.
18. mithil1Member

Messages:
61
Thanks I tried. In most cases it worked but when I dragged the formula down to copy it to other cells. In most of them it pulled the correct number but in some its pulling the wrong #
Chirag R Raval likes this.
19. mithil1Member

Messages:
61
Nevermind. It worked. I didnt enter ,0) for vlookup. Thanks Hui for your help. THis is great!!!! I am so happy and thanks to bosco_yip (other commentator)