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

    mithil1 Member

    Messages:
    57
    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. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,606
    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
  3. mithil1

    mithil1 Member

    Messages:
    57
    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. Hui

    Hui Excel Ninja Staff Member

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

    mithil1 Member

    Messages:
    57
    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. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,606
    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. mithil1

    mithil1 Member

    Messages:
    57
    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. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,606
    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: May 21, 2017
  9. mithil1

    mithil1 Member

    Messages:
    57
    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. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,606
    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. mithil1

    mithil1 Member

    Messages:
    57
    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. Hui

    Hui Excel Ninja Staff Member

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

  13. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,606
    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. mithil1

    mithil1 Member

    Messages:
    57
    I have attached

    Attached Files:

  15. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,223
    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. mithil1

    mithil1 Member

    Messages:
    57
    Cool. This is working!!
  17. mithil1

    mithil1 Member

    Messages:
    57
    Thanks!!!
    Thanks alot..
    Chirag R Raval likes this.
  18. mithil1

    mithil1 Member

    Messages:
    57
    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. mithil1

    mithil1 Member

    Messages:
    57
    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)

Share This Page