+ Reply to Thread
Results 1 to 11 of 11

Combining vlookup with concatenate

  1. #1
    Registered User
    Join Date
    10-16-2006
    Posts
    4

    Combining vlookup with concatenate

    I have a worksheet in which I want to add vlookup function where the table array will be taken dynamically from concatenated path.
    I want to store the path in one cell, the filename prefix in another cell, the file name suffix in a 3rd cell and the sheet and range in a 4th cell.

    How to do that?

    Thanks

  2. #2
    Registered User
    Join Date
    07-27-2006
    MS-Off Ver
    MS Office 2007
    Posts
    79
    Quote Originally Posted by orit
    I have a worksheet in which I want to add vlookup function where the table array will be taken dynamically from concatenated path.
    I want to store the path in one cell, the filename prefix in another cell, the file name suffix in a 3rd cell and the sheet and range in a 4th cell.

    How to do that?

    Thanks

    Sorry... do not understand your Q... can you post an example?
    TL

    https://sites.google.com/site/teelim/
    My page of "not so useful" spreadsheets

  3. #3
    Forum Contributor starryknight64's Avatar
    Join Date
    09-27-2006
    Location
    Missouri
    MS-Off Ver
    2003 (Work) & 2007 (Home)
    Posts
    193
    Say the 4 cells are A1, B1, C1, and D1

    To concatenate them use the "&" symbol. So...

    =A1 & B1 & C1

    should give you the full path of whatever you're wanting. Then D1 still has the sheet and range of the desired data.

    I hope this helps!
    starryknight64

  4. #4
    Registered User
    Join Date
    10-16-2006
    Posts
    4
    I succeed to get the full path by using the "&" sign or the CONCATENATE function, but doesn't succeed to use it as the table array of VLLOKUP function. For example I have the following table:
    A | B | C | D | E
    -------------------------------------------------------------
    path | file_prefix | file_version | sheet_name | range
    --------------------------------------------------------------
    D:\Test\ | CP_Desc_ | 21 | process A | $A$4:$AA$202

    I want to get the function:
    Please Login or Register  to view this content.
    by instead giving the explicit path: D:\Test\[CP_Desc_21.xls]process A'!$A$4:$AA$202 , concatenate it from the different cells. I tried:
    Please Login or Register  to view this content.
    But I get #VALUE!.

    What is the problem? any solution?

    Thanks a lot

  5. #5
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by orit
    I have a worksheet in which I want to add vlookup function where the table array will be taken dynamically from concatenated path.
    I want to store the path in one cell, the filename prefix in another cell, the file name suffix in a 3rd cell and the sheet and range in a 4th cell.

    How to do that?

    Thanks
    You will probably need to concatenate the full path-range in a cell and then use VLookup(value,Indirect(cell),col-index,type)

    ---
    Si fractum non sit, noli id reficere.

  6. #6
    Registered User
    Join Date
    10-16-2006
    Posts
    4
    When I try the following:
    Please Login or Register  to view this content.
    While the cell F21 contains the full path range as formated in vlookup (the result of the concatenate function as desplayed in cell F21 is:
    Please Login or Register  to view this content.
    But is still doesn't work. any idea?

    Thanks

  7. #7
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by orit
    When I try the following:
    Please Login or Register  to view this content.
    While the cell F21 contains the full path range as formated in vlookup (the result of the concatenate function as desplayed in cell F21 is:
    Please Login or Register  to view this content.
    But is still doesn't work. any idea?

    Thanks
    Yes, the format is bad

    Try my suggestion of
    VLookup(value,Indirect(cell),col-index,type)
    where $D25 appears to be your value, $F$21 appears to be your cell, 3 appears to be your co-index, and 0 appears to be your True-False type.

    Let me know how you go
    ---

  8. #8
    Registered User
    Join Date
    10-16-2006
    Posts
    4
    Oh, I understand, to use the function "INDIRECT".
    When i try the following:
    Please Login or Register  to view this content.
    while the cell F21 holds the result of a concatenate function which is:
    'D:\Test\[CP_Desc_21.xls]process A'!$A$4:$AA$202

    I get the result: #REF!

    What can be the reason. What is wrong?

    Thanks

  9. #9
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by orit
    Oh, I understand, to use the function "INDIRECT".
    When i try the following:
    Please Login or Register  to view this content.
    while the cell F21 holds the result of a concatenate function which is:
    'D:\Test\[CP_Desc_21.xls]process A'!$A$4:$AA$202

    I get the result: #REF!

    What can be the reason. What is wrong?

    Thanks
    ="'C:\Excel\[Book1.xls]Sheet1'!A1:D4"

    with

    =VLOOKUP($D9,INDIRECT($F$21),3,FALSE)

    works if the book is open, #REF if closed.

    hth
    ---

  10. #10
    Registered User
    Join Date
    01-27-2011
    Location
    Silicon Valley
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Combining vlookup with concatenate

    Quote Originally Posted by Bryan Hessey View Post
    ="'C:\Excel\[Book1.xls]Sheet1'!A1:D4"

    with

    =VLOOKUP($D9,INDIRECT($F$21),3,FALSE)

    works if the book is open, #REF if closed.

    hth
    ---
    So I have been scouring blogs and maybe what I want to do is impossible.

    What I want is exactly what this poster asked for.
    Create a file name on the fly
    vlookup information in that file.

    The solution posted above was that you have to store the file name in an intermediate cell and then use indirect. Why that is, I have no idea. It sure seems that the original Concatenate solution should have worked.

    Regardless, this only works if the file is open. Otherwise you get #REF errors.
    Indirect does not work on closed files.

    I have a years worth of files.
    I do not/can not/ manually open them all before doing this operation.

    So I need to expand the problem

    Does anyone know how to

    Create a file name on the fly
    vlookup information in that file.
    AND work on closed files.

  11. #11
    Registered User
    Join Date
    08-22-2011
    Location
    ATHENS
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Combining vlookup with concatenate

    Add 0 to the cell that you have performed the concatenate formula.
    Then do the vlookup from the cell that you have added 0.

+ 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