+ Reply to Thread
Results 1 to 18 of 18

Problem with VLOOKUP

  1. #1
    Registered User
    Join Date
    01-26-2016
    Location
    Switzerland
    MS-Off Ver
    2016
    Posts
    10

    Question Problem with VLOOKUP

    Hello everyone

    So, i have a problem with a dynamic vlookup formula I want to make. The formula takes the first digit of a number and uses this to navigate to a specific sheet within the excel file. It then searches through a list to find the associated entry.

    So, for example, if you have the number "10135) it takes the "1" from that number via "left(B2;1)".
    It then makes the reference with the help of "indirect" and "concatenate", so "indirect(concatenate("Test";left(B2;1);"!A2:B21");TRUE)".
    It then uses this in a vlookup formula, so "vlookup(B2;indirect(concatenate("Test";left(B2;1);"!A2:B21");TRUE);2;TRUE)".

    So, the formula searches for "10135" on the sheet named "Test1" in the area A2:B21 and returns the value from the second row.

    And the formula works. As long as everything is in the same file. But as soon as I try to split the formula and the data up into two separate files, the formula just throws a reference error at me.

    Could someone help me with this?

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,403

    Re: Problem with VLOOKUP

    Can you post a sample file: do you mean separate files (Workbooks) or separate sheets within a workbook?

    To post a file:

    Click "Go Advanced" and the "Paper Clip" icon. Then "Add files"==>"Browse"==> select file==>"Upload" and "Done"

  3. #3
    Forum Contributor
    Join Date
    06-24-2005
    Location
    excelliot.com
    MS-Off Ver
    2007
    Posts
    325

    Re: Problem with VLOOKUP

    So when you split file, do target sheet is there?

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Problem with VLOOKUP

    The INDIRECT function won't work with references to closed files. The target file must be open.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    01-26-2016
    Location
    Switzerland
    MS-Off Ver
    2016
    Posts
    10

    Re: Problem with VLOOKUP

    Quote Originally Posted by JohnTopley View Post
    Can you post a sample file: do you mean separate files (Workbooks) or separate sheets within a workbook?
    Two seperate files. See attachment for details.

    Test.zip

  6. #6
    Registered User
    Join Date
    01-26-2016
    Location
    Switzerland
    MS-Off Ver
    2016
    Posts
    10

    Re: Problem with VLOOKUP

    Quote Originally Posted by Tony Valko View Post
    The INDIRECT function won't work with references to closed files. The target file must be open.
    Damn. I heard about that before. But...how do i do this then? Do you have an idea?

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Problem with VLOOKUP

    There is an old add-in available that has a function that works like the INDIRECT function except that the target file did not need to be open.

    However, this add-in is no longer supported by the author and I don't know if it works in the newer versions of Excel.

    Here's a link:

    http://www.ashishmathur.com/tag/morefunc/

  8. #8
    Registered User
    Join Date
    01-26-2016
    Location
    Switzerland
    MS-Off Ver
    2016
    Posts
    10

    Re: Problem with VLOOKUP

    Damn. There has to be another way. With formulas.

    So, I'm telling you guys the story behind this: we have a big file with multiple worksheets for items we sell. 6 worksheets to be exact, each one is a special category and filled with about 2'000 items and their item numbers.
    The item numbers on the first sheet start with a "1". Second sheet is "2", you get the concept.
    And now we have some documents, like a delivery note for example, where you put in the item number and it shows the information to that item through a vlookup-formula.
    And because the items are on 6 sheets I need to write the vlookup-part 6 times in one formula.
    And if you want to copy the formula to another cell, it takes up to 30 seconds. For one cell! And if you open up a document with a lot of these vlookup-formulas, it takes aaages to fetch all the data.

    That's the whole story behind my problem. I'm just trying to speed up the whole formula, and the documents where the formula is used in.

  9. #9
    Registered User
    Join Date
    01-26-2016
    Location
    Switzerland
    MS-Off Ver
    2016
    Posts
    10

    Re: Problem with VLOOKUP

    Edit: Whoops. Why did that get posted twice?
    Last edited by JanB1; 01-27-2016 at 08:49 AM.

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,403

    Re: Problem with VLOOKUP

    It would help if you post a small sample file (no more than 3 worksheets) plus any document sheets, showing required results.

  11. #11
    Registered User
    Join Date
    01-26-2016
    Location
    Switzerland
    MS-Off Ver
    2016
    Posts
    10

    Re: Problem with VLOOKUP

    Isn't that enough?

    Quote Originally Posted by JanB1 View Post
    Two seperate files. See attachment for details.

    Attachment 442715

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,403

    Re: Problem with VLOOKUP

    Is this what you need?

    =VLOOKUP(B2,INDIRECT("'test" &LEFT(B2) &"'! A2:B1000"),2,0)

    and/or

    =VLOOKUP(B2,INDIRECT("'[Test" & LEFT(B2) & ".xlsx]test" &LEFT(B2) &"'!b2:c21"),2,0)
    Last edited by JohnTopley; 02-01-2016 at 07:58 AM.

  13. #13
    Registered User
    Join Date
    01-26-2016
    Location
    Switzerland
    MS-Off Ver
    2016
    Posts
    10

    Re: Problem with VLOOKUP

    Okay, I'm sorry. Imma reupload the files. The sheet called "Main" is the important one. I added some comments.

    Test.zip

  14. #14
    Registered User
    Join Date
    01-26-2016
    Location
    Switzerland
    MS-Off Ver
    2016
    Posts
    10

    Re: Problem with VLOOKUP

    What I need is something like the second formula with concat. The formal I wrote doesn't work when the "Test1.xlsx" is closed, because I'm using "indirect" I guess. But without the "indirect" the "vlookup" throws out an error. I want to shorten the old formula that works simply by going through all possibilities with 6 nested "IF"s. So, "if 1 goto A, else if 2 goto B, else if 3 goto C (etc)"to put it into simple pseudo-code. The new formula should just take the first number of the item number and directly go to the corresponding sheet in the data source.

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,403

    Re: Problem with VLOOKUP

    As was stated earlier INDIRECT does not work with closed files. See post #7.

    So you either have the file(s) open or you look at unsupported function mentioned by Tony.

    the first VLOOKUP in my last post will look at the appropriate sheet in a single workbook.

    The second will do the same for OPEN workbooks.

  16. #16
    Registered User
    Join Date
    01-26-2016
    Location
    Switzerland
    MS-Off Ver
    2016
    Posts
    10

    Re: Problem with VLOOKUP

    Quote Originally Posted by JohnTopley View Post
    As was stated earlier INDIRECT does not work with closed files. See post #7.
    Quote Originally Posted by JanB1 View Post
    The formal I wrote doesn't work when the "Test1.xlsx" is closed, because I'm using "indirect" I guess. But without the "indirect" the "vlookup" throws out an error.
    Yeah, I got that.

    Problem is, that the formula DOESN'T work without it (the "indirect", because it can't interpret the string. Is there any other function that takes a string and returns it as a reference?).
    And that add-on...welp I'm not the only person using this file. And because it's no longer supported...I don't know.

  17. #17
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    18,403

    Re: Problem with VLOOKUP

    It is INDIRECT or nothing if you want to avoid multiple IFs.

    Is there any reason why the data cannot be on one sheet: always better than splitting data into multiple sheets?

    You could still use the current codes "10003", 30004").
    Last edited by JohnTopley; 02-01-2016 at 02:02 PM.

  18. #18
    Registered User
    Join Date
    01-26-2016
    Location
    Switzerland
    MS-Off Ver
    2016
    Posts
    10

    Re: Problem with VLOOKUP

    Well ****. Okay.

    Yeah. Can't make a single file outta it. :/

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Vlookup problem in a loop with cell property and variable cell problem (long title sry)
    By ExcelsiorLux in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-13-2013, 10:38 AM
  2. Replies: 6
    Last Post: 05-28-2013, 05:08 PM
  3. vlookup? match? index? MULTIPLE criteria for vlookup search problem....
    By aborg88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-11-2013, 09:56 AM
  4. Excel 2007 : problem with a vlookup\if
    By Roadhogg71 in forum Excel General
    Replies: 5
    Last Post: 04-23-2012, 08:31 AM
  5. VLOOKUP problem
    By Britblink03 in forum Excel General
    Replies: 2
    Last Post: 08-09-2010, 01:43 PM
  6. vlookup problem and count problem
    By thy00123 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-05-2009, 04:31 AM
  7. [SOLVED] VLOOKUP Problem
    By Ian in forum Excel General
    Replies: 3
    Last Post: 04-06-2006, 01:55 PM
  8. [SOLVED] Vlookup problem
    By Big Jones in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-18-2005, 10:05 AM

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