Variables in VLOOKUP

HunterN

Active Member
Joined
Mar 19, 2002
Messages
479
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
I entered this as the formula:


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

Got this back #VALUE!
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top