+ Reply to Thread
Results 1 to 8 of 8

Excel 2007 : Variable Table Array in VLOOKUP

  1. #1
    Registered User
    Join Date
    08-25-2011
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    4

    Variable Table Array in VLOOKUP

    Hello all,

    Using VLOOKUP in my base workbook, I am referencing several different sheets in an external workbook that must be a closed file (therefore, I don't think the INDIRECT function will help me); what I would like to do is vary the sheet that is referenced from the external workbook via an input in my base workbook. This is my basic formula:

    =VLOOKUP($B$19, 'R:[Parts Reference v1.0.xlsx]CompanyName'!A1:J1505, 3, FALSE)

    I would like to vary my "CompanyName" via a drop down list in cell B1 in my base workbook so that at first glance one would think the new formula would look something like this:

    =VLOOKUP($B$19, 'R:[Parts Reference v1.0.xlsx]B1'!A1:J1505, 3, FALSE)

    However, the problem is the above formula simply looks for a sheet B1 in the external workbook (which does not exist).

    Now I am trying a bit different approach using the OFFSET function nested inside of my VLOOKUP to try and reference the data in cell B1, but I am beginning to think this may be a dead end as well.

    Any suggestions would be greatly appreciated as this could save me from a staggering number of IF statements.

    Thanks,

    Andrew

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Variable Table Array in VLOOKUP

    To reference the sheetname the way you want to do it, unfortunately, INDIRECT is the only Excel function that would do that.

    There is an Add-In free from here: Morefunc that let's use use INDIRECT.EXT instead to reference a closed workbook.

    e.g.


    =VLOOKUP($B$19,INDIRECT.EXT("'R:[Parts Reference v1.0.xlsx]"&B1&"'!A1:J1505"), 3, FALSE)
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    08-25-2011
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Variable Table Array in VLOOKUP

    Could I use nested VLOOKUP functions? Something like this:

    =VLOOKUP($B$19, VLOOKUP(B1, 'R:[Parts Reference v1.0.xlsx]CompanyNameList'!A1:J1505, 2, FALSE), 3, FALSE)

    So the inner VLOOKUP function would pull a company name from cell B1 and look for it in an external workbook sheet that listed all company names. In the 2nd column of the CompanyNameList sheet we would list the data for our table array...so the CompanyNameList sheet would look something like this:

    Company Name 1_______________________________'R:[Parts Reference v1.0.xlsx]Company Name 1'!A1:J1505
    Company Name 2_______________________________'R:[Parts Reference v1.0.xlsx]Company Name 2'!A1:J1505
    ._____________________________________________.
    ._____________________________________________.
    ._____________________________________________.

    It seems like this idea should work, but I am having some trouble working out the kinks...any ideas?

    Thanks,

    Andrew
    Last edited by andrewryan; 08-26-2011 at 11:56 AM. Reason: Ignore underscores, supposed to represent space separating cells in worksheet.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Variable Table Array in VLOOKUP

    You can't pull a range via VLOOKUP and use it in another vlookup.. that too requires INDIRECT.

  5. #5
    Registered User
    Join Date
    08-25-2011
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Variable Table Array in VLOOKUP

    Hmmm ok. Well, the formula I am working on is in a template that almost my entire company uses and I don't find it very likely that I will be able to get everyone to download the add-in for INDIRECT.EXT, so if anything else comes to mind please let me know.

    Thanks

    Andrew

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Variable Table Array in VLOOKUP

    Here is an article that explains the situation: INDIRECT in closed workbooks

  7. #7
    Registered User
    Join Date
    08-25-2011
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Variable Table Array in VLOOKUP

    Should I explore the option of a nested OFFSET function further? Do you know of a reason that this would fail?

    Basically, I would contain the entire external workbook reference in a single cell (call it B2) based on CompanyName and then use the OFFSET function inside of VLOOKUP to reference this data for my variable table array. Thoughts on this idea?

    B2='R:[Parts Reference v1.0.xlsx]CompanyName'!A1:J1505

    =VLOOKUP($B$19, OFFSET(A1,1,1,1,1), 3, FALSE)

    Sorry, I just think there has to be another workaround other than the INDIRECT.EXT or a million IF statements...I currently have it working with the IF statements, but I am trying to streamline the file and hoped that removing all of the IF statements would reduce the file size and increase its calculation speed.

    Thanks,

    Andrew

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Variable Table Array in VLOOKUP

    I don't think referencing the workbook path using offset works... still needs the INDIRECT.... unless the tables are hard-coded like if you used the multiple IF's you're talking about, you will need to reference the table indirectly.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1