VlookUp syntax referencing another workbook..

[ilcaa]

I just cant get the correct syntax for a Vlookup referencing another workbook..here are the details.

Vlookup function is in located in each cell in column J, so i start in J2.
the workbook I need to reference is called tradingmoney.xls and the column that needs to be looked up is Column A (both workbooks have the same column as the reference vlookup)

any help, did i explain this the right way?? thanks. Max

 

[NBVC]

You need to also identify which column you are extracting results from and the sheet name within the workbook the table is in.

e.g. =Vlookup(A2,'[tradingmoney.xls]Sheet1'!$A$2:$Z$100,26,0)

This lookups A2 from the current sheet and finds it in your workbook, in column A of Sheet1 and returns the information in the 26th column from column A, which is column Z.

Adjust ranges, sheet name and column index to suit.

 

[ilcaa]

thanks for the reply

do the columns ( in both the inout sheet and the reference sheet) have to be in ascending order (the column being referenced) in order for a vlookup to work properly?

 

[NBVC]

Not if you add a 0 or FALSE in the last argument. This allows for exact match searches irreguardless of sort order. If you omit this or use 1 or TRUE then the sort order has to be ascending.

See Vlookup help file in Excel for further clarification.

 

[ilcaa]

very cool, thanks for the hel

 

[ilcaa]

ok, need some more help...

i just noticed my A column in 1 sheet is first name, last name
and in the reference sheet its lastname, first name

i assume that why its not working....i could use another column to reference but they are in different columns....in the input worksheet(where I want the value to come into) its column E and in the workbook/worksheet to reference its in column B

any way around this problem..?

 

[NBVC]

ok, need some more help...

i just noticed my A column in 1 sheet is first name, last name
and in the reference sheet its lastname, first name

i assume that why its not working....i could use another column to reference but they are in different columns....in the input worksheet(where I want the value to come into) its column E and in the workbook/worksheet to reference its in column B

any way around this problem..?

It doesn't matter that the references are in different columns in the 2 sheets.

e.g. =Vlookup(E2,'[tradingmoney.xls]Sheet1'!$B$2:$Z$100,25,0)

where E2 contains the lookup value in the current workbook/sheet and B2:Z100 is the reference table in the tradingmoney.xls workbook in Sheet1. The table range starts at the column you are to reference/compare with the lookup value (it is the left-most column of the range), then you count columns from there to determine the number you use in the next column (the column index number). So if I still want to pull from column Z, then my column index is now 25.

Look at the Vlookup help and google Vlookup you will find lots of information and examples.