Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Variables in VLOOKUP

  1. #1
    Board Regular HunterN's Avatar
    Join Date
    Mar 2002
    Location
    Maryland
    Posts
    479
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi!

    I have several workbooks. One is the master workbook that will hold data from several seperate workbooks. I want to use the VLOOKUP function as I move down the rows in the master workbook, the thing is, that means that the table-array (or the workbook that I am searching) will change for each row I go to. I am trying to find an example of how to make a dynamic variable for the table-array that will allow me to change this variable for each row.

    This is what I am thinking:

    VLOOKUP($C$3, _
    'C:My Project["0251.xls"]Sheet1! $A$3:$D$52,2,false)

    The "0251.xls" will change for each row I read

    Have any suggestions? Thanks

    [ This Message was edited by: HunterN on 2002-04-23 05:57 ]

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-23 05:56, HunterN wrote:
    Hi!

    I have several workbooks. One is the master workbook that will hold data from several seperate workbooks. I want to use the VLOOKUP function as I move down the rows in the master workbook, the thing is, that means that the table-array (or the workbook that I am searching) will change for each row I go to. I am trying to find an example of how to make a dynamic variable for the table-array that will allow me to change this variable for each row.

    This is what I am thinking:

    VLOOKUP($C$3, _
    'C:My Project["0251.xls"]Sheet1! $A$3:$D$52,2,false)

    The "0251.xls" will change for each row I read

    Have any suggestions? Thanks

    [ This Message was edited by: HunterN on 2002-04-23 05:57 ]
    =VLOOKUP($C$3,INDIRECT("'["&E1&"]Sheet1'!$A$3:$D$52"),2,0)

    where E1 houses a file name like 0251.xls.

    The files in which you look up must be open.

    Aladin

  3. #3
    Board Regular HunterN's Avatar
    Join Date
    Mar 2002
    Location
    Maryland
    Posts
    479
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,

    What if the Project Number is in the column
    I will be looking at. How can I get this value to be the lookup variable in the VLOOKKUP table?

    Thanks, Nancy

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-23 08:32, HunterN wrote:
    Aladin,

    What if the Project Number is in the column
    I will be looking at. How can I get this value to be the lookup variable in the VLOOKKUP table?

    Thanks, Nancy
    I don't think I understand. Care to elaborate a bit? Are you referring to $C$3?


  5. #5
    Board Regular HunterN's Avatar
    Join Date
    Mar 2002
    Location
    Maryland
    Posts
    479
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes, actually it is cell $E$3 that will have the project Lookup value, but this value is also the Workbook name.

    Example: if Project Name (cell E3) has 0251 this is the lookup value I want to use in the VLOOKUP table and it it also the name of the workbook that I need to search in. Hope that makes some sence.

    Thanks for you help, Nancy

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-23 09:56, HunterN wrote:
    Yes, actually it is cell $E$3 that will have the project Lookup value, but this value is also the Workbook name.

    Example: if Project Name (cell E3) has 0251 this is the lookup value I want to use in the VLOOKUP table and it it also the name of the workbook that I need to search in. Hope that makes some sence.

    Thanks for you help, Nancy
    Would this

    =VLOOKUP(E3,INDIRECT("'["&E3&".xls"&"]Sheet1'!$A$3:$D$52"),2,0)

    work?

    Aladin

  7. #7
    Board Regular HunterN's Avatar
    Join Date
    Mar 2002
    Location
    Maryland
    Posts
    479
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    I entered this as the formula:


    =VLOOKUP(E3,INDIRECT("'["&E3&".xls"&"]Project'!$A$1:$AJ$30"),4,FALSE)

    Got this back #VALUE!

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-23 10:23, HunterN wrote:
    I entered this as the formula:


    =VLOOKUP(E3,INDIRECT("'["&E3&".xls"&"]Project'!$A$1:$AJ$30"),4,FALSE)

    Got this back #VALUE!
    I just constructed two WBs in order to test the above.

    In E3 (a text-formatted cell) I have the entry 0251.

    I created a WB named 0251.xls.

    In A3 in sheet Project of this WB (column A preformatted as text) I have the entry 0251. In D3 (column 4) I have just an arbitrary value "London".

    In WB 1, the formula

    =VLOOKUP(E3,INDIRECT("'["&E3&".xls]Project'!$A$1:$AJ$30"),4,0)

    gives me "London" as result.

    BTW: 0 means FALSE, so that's not the issue. Although this formula looks a bit different what you have tried, they are equivalent (I tested this).

    Is what you have tried including the set up/layout of the workbooks any different from the test situation I just described?

  9. #9
    New Member
    Join Date
    Jun 2010
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Re: Variables in VLOOKUP

    Hi,

    I've Question, i want to use loop variable in Vlookup,

    e.g. =VLOOKUP(E4,R[-3108]C:R[-11]C[53],1,FALSE)

    Can i use variable in "Lookup_Value" in place of E4, like if i defined one variable like RAW, which varies from 1 to 4, how can i place it in "Lookup_Value"
    & also in "Col_Index_Num". so that these values gets automatically changed when values changes from 1 to 4, meeting certain condition.

    Thanks in Advance
    Ashish

  10. #10
    Board Regular HunterN's Avatar
    Join Date
    Mar 2002
    Location
    Maryland
    Posts
    479
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Smile Re: Variables in VLOOKUP

    Just an update to this post.

    Here is what I finally put in my code to achieve what I needed to do.

    Code:
          sheetName = "My Project List"
          myWkbk = FYdir & "_List.xlsx"
          holdStr = myPath & "\" & FYdir & "\[" & myWkbk & "]" & sheetName
          Cells(2, 3).Formula = "=VLOOKUP($C$3,'" & holdStr & "'!Database,2,FALSE)"
    Where Database is a DefineNamed Range.

    Nancy

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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