How to VLOOKUP in XCEL across different workbooks each named diffe

[kool]

I am trying to set-up a VLOOKUP in one workbook to find data in other
workbooks, each with a different filename. The location of the files is the
same. I don't know how to incorporate the different source filename in my
LOOKUP Function.

Thx

 

[Bernard Liengme]

=VLOOKUP(A1,'[Book Two.xls]Sheet2'!A1:B15,2,FALSE)

If in doubt: enter the formula on the same sheet and the table;
now CUT and Paste it to the other book
You will get something like
=VLOOKUP('[Book Two.xls]Sheet2'!A1,'[Book Two.xls]Sheet2'!A1:B15,2,FALSE)
Edit this to remove reference to the second workbook for the first argument
best wishes

 

[kool]

Two things;
1) what if "Book two" is in .csv format
2) Since "book two" has a different name for each LOOKUP, what I did was
set-up a list in book one that has the unique names, for example; Cell
A2;[book three.csv], Cell A3;[book four.csv], and so on. so now I wanted to
somehow pull in the cell in book one with the each name as to tell VLOOKUP
the source in my function.

something like this; =VLOOKUP(A1,[Cell A2.csv]Sheet2'!A1:B15,2, FALSE)

=VLOOKUP(A1,'[Book Two.xls]Sheet2'!A1:B15,2,FALSE)

If in doubt: enter the formula on the same sheet and the table;
now CUT and Paste it to the other book
You will get something like
=VLOOKUP('[Book Two.xls]Sheet2'!A1,'[Book Two.xls]Sheet2'!A1:B15,2,FALSE)
Edit this to remove reference to the second workbook for the first argument
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

I am trying to set-up a VLOOKUP in one workbook to find data in other
workbooks, each with a different filename. The location of the files is
the
same. I don't know how to incorporate the different source filename in my
LOOKUP Function.

Thx

 

[Bernard Liengme]

In Book2.CSV I have
a 1
b 2
c 3
d 4
e 5
f 6

In an Excel file I have
In A1, the text: Book Two
In C1 the text: c ..... this is what I want to look up
In D1 I have the formula:
=VLOOKUP(C1,INDIRECT("'"&A1&".csv'!A1:B6"),2,FALSE)
This does the lookup and returns the value 3

The function INDIRECT requires the CSV file to be open (Edit | Links may
help)

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Two things;
1) what if "Book two" is in .csv format
2) Since "book two" has a different name for each LOOKUP, what I did was
set-up a list in book one that has the unique names, for example; Cell
A2;[book three.csv], Cell A3;[book four.csv], and so on. so now I wanted
to
somehow pull in the cell in book one with the each name as to tell VLOOKUP
the source in my function.

something like this; =VLOOKUP(A1,[Cell A2.csv]Sheet2'!A1:B15,2, FALSE)

=VLOOKUP(A1,'[Book Two.xls]Sheet2'!A1:B15,2,FALSE)

If in doubt: enter the formula on the same sheet and the table;
now CUT and Paste it to the other book
You will get something like
=VLOOKUP('[Book Two.xls]Sheet2'!A1,'[Book
Two.xls]Sheet2'!A1:B15,2,FALSE)
Edit this to remove reference to the second workbook for the first
argument
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

I am trying to set-up a VLOOKUP in one workbook to find data in other
workbooks, each with a different filename. The location of the files is
the
same. I don't know how to incorporate the different source filename in
my
LOOKUP Function.

Thx

 

[kool]

That was vrey helpful. Thank you. Now if I can only figure out how to lookup
the date in the source files, without having to open them..

In Book2.CSV I have
a 1
b 2
c 3
d 4
e 5
f 6

In an Excel file I have
In A1, the text: Book Two
In C1 the text: c ..... this is what I want to look up
In D1 I have the formula:
=VLOOKUP(C1,INDIRECT("'"&A1&".csv'!A1:B6"),2,FALSE)
This does the lookup and returns the value 3

The function INDIRECT requires the CSV file to be open (Edit | Links may
help)

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Two things;
1) what if "Book two" is in .csv format
2) Since "book two" has a different name for each LOOKUP, what I did was
set-up a list in book one that has the unique names, for example; Cell
A2;[book three.csv], Cell A3;[book four.csv], and so on. so now I wanted
to
somehow pull in the cell in book one with the each name as to tell VLOOKUP
the source in my function.

something like this; =VLOOKUP(A1,[Cell A2.csv]Sheet2'!A1:B15,2, FALSE)

=VLOOKUP(A1,'[Book Two.xls]Sheet2'!A1:B15,2,FALSE)

If in doubt: enter the formula on the same sheet and the table;
now CUT and Paste it to the other book
You will get something like
=VLOOKUP('[Book Two.xls]Sheet2'!A1,'[Book
Two.xls]Sheet2'!A1:B15,2,FALSE)
Edit this to remove reference to the second workbook for the first
argument
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


I am trying to set-up a VLOOKUP in one workbook to find data in other
workbooks, each with a different filename. The location of the files is
the
same. I don't know how to incorporate the different source filename in
my
LOOKUP Function.

Thx