using vlookup for multiple files

[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

 

[Pete_UK]

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

 

[JB]

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

JB
http://boisgontierjacques.free.fr/

 

[al]

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,

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

 

[al]

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,

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

 

[JB]

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

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,
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 -

- Afficher le texte des messages précédents -

 

[K-Man]

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,

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?

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 -

- Afficher le texte des messages précédents -