VlookUp syntax referencing another workbook..

ilcaa

Well-known Member
Joined
May 25, 2005
Messages
686
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

Well-known Member
Joined
May 25, 2005
Messages
686
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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

Well-known Member
Joined
May 25, 2005
Messages
686

ADVERTISEMENT

very cool, thanks for the hel
 

ilcaa

Well-known Member
Joined
May 25, 2005
Messages
686
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

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,043
Messages
5,545,689
Members
410,698
Latest member
Wloven
Top