• 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...

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

Vlookup indirect

M

mithil1

Member
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)
 
Mithil

Firstly, Welcome to the Chandoo.org Forums

Does you cell have an equal sign at the start of it?
It should read
=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
 
Hui said:
Mithil

Firstly, Welcome to the Chandoo.org Forums

Does you cell have an equal sign at the start of it?
It should read
=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
Click to expand...

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 ?
 
This works for me
=VLOOKUP($B$16,INDIRECT("'"&A1&"'!A$1:C$50"),3,FALSE)
note changes in red
 
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.
 
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
 
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!!! :)
 
You are getting an error because it is not ""
It is "'"

Also don't forget the other '
upload_2017-5-21_9-55-17.png
That allows sheetnames with spaces
 
Last edited:
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.
 
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
 
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 ?
 
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)
 
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
 
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 #
 
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)
 
You must log in or register to reply here.
Back
Top