Variables in VLOOKUP

[HunterN]

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 ]

[Aladin Akyurek]

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

[HunterN]

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

[Aladin Akyurek]

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?

[HunterN]

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

[Aladin Akyurek]

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

[HunterN]

I entered this as the formula:


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

Got this back #VALUE!

[Aladin Akyurek]

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?

[erashish14]

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

[HunterN]

Just an update to this post.

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

      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