using vlookup for multiple files

  • Thread starter Thread starter al
  • Start date Start date
A

al

Hi,

is it possible to use Vlookup for searching in say 2 or more files?
file1, file2, file3 etc..

if I use

=VLOOKUP(A2,[file1]Sheet1!$A$1:$B$6,2,FALSE)+VLOOKUP(A2,[file2]Sheet1!
$A$1:$B$6,2,FALSE)

gives me an error.

Is it possible with IF statement may be? Any help will be
appreciated.

thx
 
You could try this approach:

=IF(ISERROR(VLOOKUP(A2,[file1]Sheet1!$A$1:$B$6,2,FALSE)),0,VLOOKUP(A2,
[file1]Sheet1!$A$1:$B$6,2,FALSE))+IF(ISERROR(VLOOKUP(A2,[file2]Sheet1!
$A$1:$B$6,2,FALSE)),0,VLOOKUP(A2,[file2]Sheet1!$A$1:$B$6,2,FALSE))

and so on, so that each VLOOKUP is contained within its own IF which
returns either 0 or the VLOOKUP return value and avoids all errors.

Note that if file1, file2 etc is not open you need to include the full
path to the file as well as the filename and extension, so your
formula will be quite long. It will be easier to have those files open
when you develop the formula, then Excel will insert the path when the
file is closed.

Hope this helps.

Pete
 
Hi Pete,

This formula gives me error after +IF(ISERROR(VLOOKUP(.....etc) when I
do a step by step evaluation of this formula. Can we add '+' to tell
excel to look in other (second file)?

thanks


You could try this approach:

=IF(ISERROR(VLOOKUP(A2,[file1]Sheet1!$A$1:$B$6,2,FALSE)),0,VLOOKUP(A2,
[file1]Sheet1!$A$1:$B$6,2,FALSE))+IF(ISERROR(VLOOKUP(A2,[file2]Sheet1!
$A$1:$B$6,2,FALSE)),0,VLOOKUP(A2,[file2]Sheet1!$A$1:$B$6,2,FALSE))

and so on, so that each VLOOKUP is contained within its own IF which
returns either 0 or the VLOOKUP return value and avoids all errors.

Note that if file1, file2 etc is not open you need to include the full
path to the file as well as the filename and extension, so your
formula will be quite long. It will be easier to have those files open
when you develop the formula, then Excel will insert the path when the
file is closed.

Hope this helps.

Pete

Hi,
Click to expand...
is it possible to use Vlookup for searching in say 2 or more files?
file1, file2, file3 etc..
Click to expand...
if I use
=VLOOKUP(A2,[file1]Sheet1!$A$1:$B$6,2,FALSE)+VLOOKUP(A2,[file2]Sheet1!
$A$1:$B$6,2,FALSE)
Click to expand...

gives me an error.
Click to expand...
Is it possible with IF statement may be?  Any help will be
appreciated.
Click to expand...
thx
Click to expand...
Click to expand...
 
Hi JB,

This is an interesting way to go about referencing to different zones,
can I include zones in the same sheet as formula however referencing
external files in different location?

thanks
Al

3 tables

Named ranges
zone1   $C$10:$E$14
zone2   $H$11:$J$17
zone3   $D$19:$F$23

=VLOOKUP(A2,INDIRECT("zone"&MATCH(TRUE,
(COUNTIF(INDIRECT("zone"&ROW(1:3)),A2)>0),0)),2,FALSE)

http://cjoint.com/?0crjE8QCp4R

JBhttp://boisgontierjacques.free.fr/

Hi,
Click to expand...
is it possible to use Vlookup for searching in say 2 or more files?
file1, file2, file3 etc..
Click to expand...
if I use
=VLOOKUP(A2,[file1]Sheet1!$A$1:$B$6,2,FALSE)+VLOOKUP(A2,[file2]Sheet1!
$A$1:$B$6,2,FALSE)
Click to expand...

gives me an error.
Click to expand...
Is it possible with IF statement may be?  Any help will be
appreciated.
Click to expand...
thx
Click to expand...
Click to expand...
 
NF is a named range.It contains the names of files/sheets.
Files must be open.

[test.xls]feuil1
[test.xls]feuil2
[test.xls]feuil3
[test2.xls]feuil1
[test2.xls]feuil2

Search codes are in column A and result in column B

=VLOOKUP(A2,INDIRECT("'"&INDEX(nf,MATCH(TRUE,
(COUNTIF(INDIRECT("'"&nf&"'!A2:A6"),A2)>0),0))&"'!A2:B6"),2,0)

http://cjoint.com/?0crsuIgywxA

JB


Hi JB,

This is an interesting way to go about referencing to different zones,
can I include zones in the same sheet as formula however referencing
external files in different location?

thanks
Al

3 tables
Click to expand...
Named ranges
zone1   $C$10:$E$14
zone2   $H$11:$J$17
zone3   $D$19:$F$23
=VLOOKUP(A2,INDIRECT("zone"&MATCH(TRUE,
(COUNTIF(INDIRECT("zone"&ROW(1:3)),A2)>0),0)),2,FALSE)
Click to expand...
http://cjoint.com/?0crjE8QCp4R
Click to expand...
JBhttp://boisgontierjacques.free.fr/
Click to expand...

Hi,
is it possible to use Vlookup for searching in say 2 or more files?
file1, file2, file3 etc..
if I use
=VLOOKUP(A2,[file1]Sheet1!$A$1:$B$6,2,FALSE)+VLOOKUP(A2,[file2]Sheet1!
$A$1:$B$6,2,FALSE)
gives me an error.
Is it possible with IF statement may be?  Any help will be
appreciated.
thx- Masquer le texte des messages précédents -
Click to expand...
Click to expand...

- Afficher le texte des messages précédents -
Click to expand...
 
Hi JB,

thanks very much for the file you posted, however I am a bit confused,
I do not understand the NF command and how it relates to referenced
sheets/files (in my case), also the formula result is giving #REF as
it searches bb under sheet feuil2, as the final result.

Thanks for your input.
al

NF is a named range.It contains the names of files/sheets.
Files must be open.

[test.xls]feuil1
[test.xls]feuil2
[test.xls]feuil3
[test2.xls]feuil1
[test2.xls]feuil2

Search codes are in column A and result in column B

=VLOOKUP(A2,INDIRECT("'"&INDEX(nf,MATCH(TRUE,
(COUNTIF(INDIRECT("'"&nf&"'!A2:A6"),A2)>0),0))&"'!A2:B6"),2,0)

http://cjoint.com/?0crsuIgywxA

JB

Hi JB,
Click to expand...
This is an interesting way to go about referencing to different zones,
can I include zones in the same sheet as formula however referencing
external files in different location?
Click to expand...
if I use
=VLOOKUP(A2,[file1]Sheet1!$A$1:$B$6,2,FALSE)+VLOOKUP(A2,[file2]Sheet1!
$A$1:$B$6,2,FALSE)
gives me an error.
Is it possible with IF statement may be?  Any help will be
appreciated.
thx- Masquer le texte des messages précédents -
Click to expand...
Click to expand...
- Afficher le texte des messages précédents -
Click to expand...
Click to expand...
 
Back
Top