VLOOKUP formula searching multiple worksheets ??

  • Thread starter Thread starter theCityLight
  • Start date Start date
T

theCityLight

Does anyone know how to search for info from multiple Excel worksheets
using the VLOOOKUP formula or any other formula.

I have my VLOOKUP formula(column B) and list of values(column A)
(sorted in ascending order) on Worksheet 7 and I need to search for
these values in Worksheets 1,2,3,4,5& 6 Column F.

Is this possible with Excel ? I've tried this by using the formula

VLOOKUP(A2,Wrk1:Wrk6!$F:$G,1,FALSE)

but I get a #VALUE! error.
 
You will need a construct along these lines:

=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0)),
"not present",
VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk2!$F:$G,2,0)),
VLOOKUP(A2,Wrk1!$F:$G,2,0))

This is all one formula, looking in turn at worksheets 1, 2 and 3 -
I've just manually split it here to avoid awkward line breaks.
Hopefully you can see the symmetry, and how to extend it to 6
worksheets.

Hope this helps.

Pete
 
I got this from Peo Sjoblom, looks across eight sheets in his example. Peo
sent me a two page e-mail explaining how the formula works. Even with that
in hand I barely understand any of it. But it seems pretty easy to adapt to
your own workbooks/worksheets, the ranges and sheet names.

Where A2 is the lookup value on the formula sheet.
MySheets is a named range consisting of a list of the sheet names you want
to look at.
A2:A200 refers to the sheets being looked at.
A2:C200 is the Lookup_Array on the sheets being looked at.

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)

Using a named range shortens the formula considerable as you can see by this
one which lists the sheet names in the formula. And of course both formulas
are all on one line.

=VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),2,0)

HTH
Regards,
Howard
 
Are you looking to add up all column G values from all sheets when it finds
the lookup name in column F of each sheet? If so, you could just use SUMIF.
=SUMIF(Wrk1!F:F,A1,Wrk1!G:G)+SUMIF(Wrk2!F:F,A1,Wrk2!G:G)+etc.
Just a plain old SUM function works with the Wrk1:Wrk6 idea, but summing
based on a condition does not. I also could not get SUMPRODUCT to accept a
worksheet range.
 
You will need a construct along these lines:

=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0)),
"not present",
VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk2!$F:$G,2,0)),
VLOOKUP(A2,Wrk1!$F:$G,2,0))

This is all one formula, looking in turn at worksheets 1, 2 and 3 -
I've just manually split it here to avoid awkward line breaks.
Hopefully you can see the symmetry, and how to extend it to 6
worksheets.

Hope this helps.

Pete





- Show quoted text -
Click to expand...

Thanks so much, Pete. The formula worked great for 6 worksheets but I
have 9 worksheets. It errored out when I added the formula for the 7th
worksheet.
 
Are you looking to add up all column G values from all sheets when it finds
the lookup name in column F of each sheet? If so, you could just use SUMIF.
=SUMIF(Wrk1!F:F,A1,Wrk1!G:G)+SUMIF(Wrk2!F:F,A1,Wrk2!G:G)+etc.
Just a plain old SUM function works with the Wrk1:Wrk6 idea, but summing
based on a condition does not. I also could not get SUMPRODUCT to accept a
worksheet range.








- Show quoted text -
Click to expand...

KC,
I'm not adding the formula result. It is only a look up and tell
whether it's on the 9 worksheets or not.
 
I got this from Peo Sjoblom, looks across eight sheets in his example. Peo
sent me a two page e-mail explaining how the formula works. Even with that
in hand I barely understand any of it. But it seems pretty easy to adapt to
your own workbooks/worksheets, the ranges and sheet names.

Where A2 is the lookup value on the formula sheet.
MySheets is a named range consisting of a list of the sheet names you want
to look at.
A2:A200 refers to the sheets being looked at.
A2:C200 is the Lookup_Array on the sheets being looked at.

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)

Using a named range shortens the formula considerable as you can see by this
one which lists the sheet names in the formula. And of course both formulas
are all on one line.

=VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),2,0)

HTH
Regards,
Howard
 
I got this from Peo Sjoblom, looks across eight sheets in his example. Peo
sent me a two page e-mail explaining how the formula works. Even with that
in hand I barely understand any of it. But it seems pretty easy to adaptto
your own workbooks/worksheets, the ranges and sheet names.

Where A2 is the lookup value on the formula sheet.
MySheets is a named range consisting of a list of the sheet names you want
to look at.
A2:A200 refers to the sheets being looked at.
A2:C200 is the Lookup_Array on the sheets being looked at.

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MyS­heets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)

Using a named range shortens the formula considerable as you can see by this
one which lists the sheet names in the formula. And of course both formulas
are all on one line.

=VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5­";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sh­eet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A2:A200"),A­2)>0),0))&"'!A2:C200"),2,0)

HTH
Regards,
Howard








- Show quoted text -
Click to expand...

Beautiful! Ths formula worked for 9 wrksheets . Thanks a great to all
who contributed!
 
Forgot to add, this is an array formula, CTRL+SHIFT+ENTER to commit.

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)

HTH
REgards,
Howard
 
Hi Pete,

I wanted to use the formula for 6 sheets, I managed to do for five sheets , then I have a warning as there are too many inserted formula.

I am not good at codes, what can I to use this for more then 5 sheets.

=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk2!$F:$G,2,0)),
VLOOKUP(A2,Wrk1!$F:$G,2,0))

Thanks



Pete_UK wrote:

Re: VLOOKUP formula searching multiple worksheets ??
21-Feb-07

You will need a construct along these lines

=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0))
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0))
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0))
"not present"
VLOOKUP(A2,Wrk3!$F:$G,2,0))
VLOOKUP(A2,Wrk2!$F:$G,2,0))
VLOOKUP(A2,Wrk1!$F:$G,2,0)

This is all one formula, looking in turn at worksheets 1, 2 and 3
I've just manually split it here to avoid awkward line breaks
Hopefully you can see the symmetry, and how to extend it to
worksheets

Hope this helps

Pet

On Feb 21, 7:15 pm, (e-mail address removed) wrote:

Previous Posts In This Thread:

VLOOKUP formula searching multiple worksheets ??
Does anyone know how to search for info from multiple Excel worksheet
using the VLOOOKUP formula or any other formula

I have my VLOOKUP formula(column B) and list of values(column A
(sorted in ascending order) on Worksheet 7 and I need to search fo
these values in Worksheets 1,2,3,4,5& 6 Column F

Is this possible with Excel ? I've tried this by using the formul

VLOOKUP(A2,Wrk1:Wrk6!$F:$G,1,FALSE

but I get a #VALUE! error.

Re: VLOOKUP formula searching multiple worksheets ??
You will need a construct along these lines

=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0))
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0))
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0))
"not present"
VLOOKUP(A2,Wrk3!$F:$G,2,0))
VLOOKUP(A2,Wrk2!$F:$G,2,0))
VLOOKUP(A2,Wrk1!$F:$G,2,0)

This is all one formula, looking in turn at worksheets 1, 2 and 3
I've just manually split it here to avoid awkward line breaks
Hopefully you can see the symmetry, and how to extend it to
worksheets

Hope this helps

Pet

On Feb 21, 7:15 pm, (e-mail address removed) wrote:

I got this from Peo Sjoblom, looks across eight sheets in his example.
I got this from Peo Sjoblom, looks across eight sheets in his example. Peo
sent me a two page e-mail explaining how the formula works. Even with that
in hand I barely understand any of it. But it seems pretty easy to adapt to
your own workbooks/worksheets, the ranges and sheet names

Where A2 is the lookup value on the formula sheet
MySheets is a named range consisting of a list of the sheet names you want
to look at
A2:A200 refers to the sheets being looked at
A2:C200 is the Lookup_Array on the sheets being looked at

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0

Using a named range shortens the formula considerable as you can see by this
one which lists the sheet names in the formula. And of course both formulas
are all on one line

=VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),2,0

HT
Regards
Howar


Are you looking to add up all column G values from all sheets when it finds
Are you looking to add up all column G values from all sheets when it finds
the lookup name in column F of each sheet? If so, you could just use SUMIF
=SUMIF(Wrk1!F:F,A1,Wrk1!G:G)+SUMIF(Wrk2!F:F,A1,Wrk2!G:G)+etc
Just a plain old SUM function works with the Wrk1:Wrk6 idea, but summing
based on a condition does not. I also could not get SUMPRODUCT to accept a
worksheet range.



Re: VLOOKUP formula searching multiple worksheets ??
Thanks so much, Pete. The formula worked great for 6 worksheets but I
have 9 worksheets. It errored out when I added the formula for the 7th
worksheet.

Re: VLOOKUP formula searching multiple worksheets ??
KC,
I am not adding the formula result. It is only a look up and tell
whether it is on the 9 worksheets or not.

I got this from Peo Sjoblom, looks across eight sheets in his example.
I got this from Peo Sjoblom, looks across eight sheets in his example. Peo
sent me a two page e-mail explaining how the formula works. Even with that
in hand I barely understand any of it. But it seems pretty easy to adapt to
your own workbooks/worksheets, the ranges and sheet names.

Where A2 is the lookup value on the formula sheet.
MySheets is a named range consisting of a list of the sheet names you want
to look at.
A2:A200 refers to the sheets being looked at.
A2:C200 is the Lookup_Array on the sheets being looked at.

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)

Using a named range shortens the formula considerable as you can see by this
one which lists the sheet names in the formula. And of course both formulas
are all on one line.

=VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),2,0)

HTH
Regards,
Howard


Re: VLOOKUP formula searching multiple worksheets ??
eo
at
to
&MyS=ADheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)
his
las
eet5=AD";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Shee=
t1";"Sh=ADeet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A=
2:A200"),A=AD2)>0),0))&"'!A2:C200"),2,0)

Beautiful! Ths formula worked for 9 wrksheets . Thanks a great to all
who contributed!

Forgot to add, this is an array formula, CTRL+SHIFT+ENTER to commit.
Forgot to add, this is an array formula, CTRL+SHIFT+ENTER to commit.

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)

HTH
REgards,
Howard



Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Report Engine, Part 2
http://www.eggheadcafe.com/tutorial...c9-cf3a9ee210a7/wpf-report-engine-part-2.aspx
 
Hi Pete,

I wanted to use the formula for 6 sheets, I managed to do for five sheets , then I have a warning as there are too many inserted formula.

I am not good at codes, what can I to use this for more then 5 sheets.

=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk2!$F:$G,2,0)),
VLOOKUP(A2,Wrk1!$F:$G,2,0))

Thanks



Pete_UK wrote:

Re: VLOOKUP formula searching multiple worksheets ??
21-Feb-07

You will need a construct along these lines

=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0))
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0))
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0))
"not present"
VLOOKUP(A2,Wrk3!$F:$G,2,0))
VLOOKUP(A2,Wrk2!$F:$G,2,0))
VLOOKUP(A2,Wrk1!$F:$G,2,0)

This is all one formula, looking in turn at worksheets 1, 2 and 3
I've just manually split it here to avoid awkward line breaks
Hopefully you can see the symmetry, and how to extend it to
worksheets

Hope this helps

Pet

On Feb 21, 7:15 pm, (e-mail address removed) wrote:

Previous Posts In This Thread:

VLOOKUP formula searching multiple worksheets ??
Does anyone know how to search for info from multiple Excel worksheet
using the VLOOOKUP formula or any other formula

I have my VLOOKUP formula(column B) and list of values(column A
(sorted in ascending order) on Worksheet 7 and I need to search fo
these values in Worksheets 1,2,3,4,5& 6 Column F

Is this possible with Excel ? I've tried this by using the formul

VLOOKUP(A2,Wrk1:Wrk6!$F:$G,1,FALSE

but I get a #VALUE! error.

Re: VLOOKUP formula searching multiple worksheets ??
You will need a construct along these lines

=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0))
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0))
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0))
"not present"
VLOOKUP(A2,Wrk3!$F:$G,2,0))
VLOOKUP(A2,Wrk2!$F:$G,2,0))
VLOOKUP(A2,Wrk1!$F:$G,2,0)

This is all one formula, looking in turn at worksheets 1, 2 and 3
I've just manually split it here to avoid awkward line breaks
Hopefully you can see the symmetry, and how to extend it to
worksheets

Hope this helps

Pet

On Feb 21, 7:15 pm, (e-mail address removed) wrote:

I got this from Peo Sjoblom, looks across eight sheets in his example.
I got this from Peo Sjoblom, looks across eight sheets in his example. Peo
sent me a two page e-mail explaining how the formula works. Even with that
in hand I barely understand any of it. But it seems pretty easy to adapt to
your own workbooks/worksheets, the ranges and sheet names

Where A2 is the lookup value on the formula sheet
MySheets is a named range consisting of a list of the sheet names you want
to look at
A2:A200 refers to the sheets being looked at
A2:C200 is the Lookup_Array on the sheets being looked at

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0

Using a named range shortens the formula considerable as you can see by this
one which lists the sheet names in the formula. And of course both formulas
are all on one line

=VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),2,0

HT
Regards
Howar


Are you looking to add up all column G values from all sheets when it finds
Are you looking to add up all column G values from all sheets when it finds
the lookup name in column F of each sheet? If so, you could just use SUMIF
=SUMIF(Wrk1!F:F,A1,Wrk1!G:G)+SUMIF(Wrk2!F:F,A1,Wrk2!G:G)+etc
Just a plain old SUM function works with the Wrk1:Wrk6 idea, but summing
based on a condition does not. I also could not get SUMPRODUCT to accept a
worksheet range.



Re: VLOOKUP formula searching multiple worksheets ??
Thanks so much, Pete. The formula worked great for 6 worksheets but I
have 9 worksheets. It errored out when I added the formula for the 7th
worksheet.

Re: VLOOKUP formula searching multiple worksheets ??
KC,
I am not adding the formula result. It is only a look up and tell
whether it is on the 9 worksheets or not.

I got this from Peo Sjoblom, looks across eight sheets in his example.
I got this from Peo Sjoblom, looks across eight sheets in his example. Peo
sent me a two page e-mail explaining how the formula works. Even with that
in hand I barely understand any of it. But it seems pretty easy to adapt to
your own workbooks/worksheets, the ranges and sheet names.

Where A2 is the lookup value on the formula sheet.
MySheets is a named range consisting of a list of the sheet names you want
to look at.
A2:A200 refers to the sheets being looked at.
A2:C200 is the Lookup_Array on the sheets being looked at.

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)

Using a named range shortens the formula considerable as you can see by this
one which lists the sheet names in the formula. And of course both formulas
are all on one line.

=VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),2,0)

HTH
Regards,
Howard


Re: VLOOKUP formula searching multiple worksheets ??
eo
at
to
&MyS=ADheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)
his
las
eet5=AD";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Shee=
t1";"Sh=ADeet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A=
2:A200"),A=AD2)>0),0))&"'!A2:C200"),2,0)

Beautiful! Ths formula worked for 9 wrksheets . Thanks a great to all
who contributed!

Forgot to add, this is an array formula, CTRL+SHIFT+ENTER to commit.
Forgot to add, this is an array formula, CTRL+SHIFT+ENTER to commit.

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)

HTH
REgards,
Howard


I have an error for 6 sheets
Hi Pete,

I wanted to use the formula for 6 sheets, I managed to do for five sheets , then I have a warning as there are too many inserted formula.

I am not good at codes, what can I to use this for more then 5 sheets.

=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk2!$F:$G,2,0)),
VLOOKUP(A2,Wrk1!$F:$G,2,0))

Thanks


Submitted via EggHeadCafe - Software Developer Portal of Choice
IIS 7.0 Extensionless UrlRewriting (Short urls)
http://www.eggheadcafe.com/tutorial...df-52898c6aa5d7/iis-70-extensionless-url.aspx
 
Hi Pete,

I wanted to use the formula for 6 sheets, I managed to do for five sheets , then I have a warning as there are too many inserted formula.

I am not good at codes, what can I to use this for more then 5 sheets.

=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk2!$F:$G,2,0)),
VLOOKUP(A2,Wrk1!$F:$G,2,0))

Thanks



Pete_UK wrote:

Re: VLOOKUP formula searching multiple worksheets ??
21-Feb-07

You will need a construct along these lines:

=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0)),
"not present",
VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk2!$F:$G,2,0)),
VLOOKUP(A2,Wrk1!$F:$G,2,0))

This is all one formula, looking in turn at worksheets 1, 2 and 3 -
I've just manually split it here to avoid awkward line breaks.
Hopefully you can see the symmetry, and how to extend it to 6
worksheets.

Hope this helps.

Pete


On Feb 21, 7:15 pm, (e-mail address removed) wrote:

Previous Posts In This Thread:

VLOOKUP formula searching multiple worksheets ??
Does anyone know how to search for info from multiple Excel worksheets
using the VLOOOKUP formula or any other formula.

I have my VLOOKUP formula(column B) and list of values(column A)
(sorted in ascending order) on Worksheet 7 and I need to search for
these values in Worksheets 1,2,3,4,5& 6 Column F.

Is this possible with Excel ? I've tried this by using the formula

VLOOKUP(A2,Wrk1:Wrk6!$F:$G,1,FALSE)

but I get a #VALUE! error.

Re: VLOOKUP formula searching multiple worksheets ??
You will need a construct along these lines:

=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0)),
"not present",
VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk2!$F:$G,2,0)),
VLOOKUP(A2,Wrk1!$F:$G,2,0))

This is all one formula, looking in turn at worksheets 1, 2 and 3 -
I've just manually split it here to avoid awkward line breaks.
Hopefully you can see the symmetry, and how to extend it to 6
worksheets.

Hope this helps.

Pete


On Feb 21, 7:15 pm, (e-mail address removed) wrote:

I got this from Peo Sjoblom, looks across eight sheets in his example.
I got this from Peo Sjoblom, looks across eight sheets in his example. Peo
sent me a two page e-mail explaining how the formula works. Even with that
in hand I barely understand any of it. But it seems pretty easy to adapt to
your own workbooks/worksheets, the ranges and sheet names.

Where A2 is the lookup value on the formula sheet.
MySheets is a named range consisting of a list of the sheet names you want
to look at.
A2:A200 refers to the sheets being looked at.
A2:C200 is the Lookup_Array on the sheets being looked at.

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)

Using a named range shortens the formula considerable as you can see by this
one which lists the sheet names in the formula. And of course both formulas
are all on one line.

=VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),2,0)

HTH
Regards,
Howard



Are you looking to add up all column G values from all sheets when it finds
Are you looking to add up all column G values from all sheets when it finds
the lookup name in column F of each sheet? If so, you could just use SUMIF.
=SUMIF(Wrk1!F:F,A1,Wrk1!G:G)+SUMIF(Wrk2!F:F,A1,Wrk2!G:G)+etc.
Just a plain old SUM function works with the Wrk1:Wrk6 idea, but summing
based on a condition does not. I also could not get SUMPRODUCT to accept a
worksheet range.



Re: VLOOKUP formula searching multiple worksheets ??
Thanks so much, Pete. The formula worked great for 6 worksheets but I
have 9 worksheets. It errored out when I added the formula for the 7th
worksheet.

Re: VLOOKUP formula searching multiple worksheets ??
KC,
I am not adding the formula result. It is only a look up and tell
whether it is on the 9 worksheets or not.

I got this from Peo Sjoblom, looks across eight sheets in his example.
I got this from Peo Sjoblom, looks across eight sheets in his example. Peo
sent me a two page e-mail explaining how the formula works. Even with that
in hand I barely understand any of it. But it seems pretty easy to adapt to
your own workbooks/worksheets, the ranges and sheet names.

Where A2 is the lookup value on the formula sheet.
MySheets is a named range consisting of a list of the sheet names you want
to look at.
A2:A200 refers to the sheets being looked at.
A2:C200 is the Lookup_Array on the sheets being looked at.

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)

Using a named range shortens the formula considerable as you can see by this
one which lists the sheet names in the formula. And of course both formulas
are all on one line.

=VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),2,0)

HTH
Regards,
Howard


Re: VLOOKUP formula searching multiple worksheets ??
eo
at
to
&MyS=ADheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)
his
las
eet5=AD";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Shee=
t1";"Sh=ADeet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A=
2:A200"),A=AD2)>0),0))&"'!A2:C200"),2,0)

Beautiful! Ths formula worked for 9 wrksheets . Thanks a great to all
who contributed!

Forgot to add, this is an array formula, CTRL+SHIFT+ENTER to commit.
Forgot to add, this is an array formula, CTRL+SHIFT+ENTER to commit.

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)

HTH
REgards,
Howard


I have an error for 6 sheets
Hi Pete,

I wanted to use the formula for 6 sheets, I managed to do for five sheets , then I have a warning as there are too many inserted formula.

I am not good at codes, what can I to use this for more then 5 sheets.

=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk2!$F:$G,2,0)),
VLOOKUP(A2,Wrk1!$F:$G,2,0))

Thanks

I have an error for 6 sheets
Hi Pete,

I wanted to use the formula for 6 sheets, I managed to do for five sheets , then I have a warning as there are too many inserted formula.

I am not good at codes, what can I to use this for more then 5 sheets.

=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk2!$F:$G,2,0)),
VLOOKUP(A2,Wrk1!$F:$G,2,0))

Thanks


Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Datagrid as ComboBox Dropdown Part 2
http://www.eggheadcafe.com/tutorial...96-e24127040bbf/wpf-datagrid-as-combobox.aspx
 
Hi Pete,

I wanted to use the formula for 6 sheets, I managed to do for five sheets , (in excel 2007) then I have a warning as there are too many inserted formula.

I am not good at codes, what can I to use this for more then 5 sheets.

=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk2!$F:$G,2,0)),
VLOOKUP(A2,Wrk1!$F:$G,2,0))

Thanks



Pete_UK wrote:

Re: VLOOKUP formula searching multiple worksheets ??
21-Feb-07

You will need a construct along these lines

=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0))
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0))
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0))
"not present"
VLOOKUP(A2,Wrk3!$F:$G,2,0))
VLOOKUP(A2,Wrk2!$F:$G,2,0))
VLOOKUP(A2,Wrk1!$F:$G,2,0)

This is all one formula, looking in turn at worksheets 1, 2 and 3
I've just manually split it here to avoid awkward line breaks
Hopefully you can see the symmetry, and how to extend it to
worksheets

Hope this helps

Pet

On Feb 21, 7:15 pm, (e-mail address removed) wrote:

Previous Posts In This Thread:

VLOOKUP formula searching multiple worksheets ??
Does anyone know how to search for info from multiple Excel worksheet
using the VLOOOKUP formula or any other formula

I have my VLOOKUP formula(column B) and list of values(column A
(sorted in ascending order) on Worksheet 7 and I need to search fo
these values in Worksheets 1,2,3,4,5& 6 Column F

Is this possible with Excel ? I've tried this by using the formul

VLOOKUP(A2,Wrk1:Wrk6!$F:$G,1,FALSE

but I get a #VALUE! error.

Re: VLOOKUP formula searching multiple worksheets ??
You will need a construct along these lines

=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0))
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0))
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0))
"not present"
VLOOKUP(A2,Wrk3!$F:$G,2,0))
VLOOKUP(A2,Wrk2!$F:$G,2,0))
VLOOKUP(A2,Wrk1!$F:$G,2,0)

This is all one formula, looking in turn at worksheets 1, 2 and 3
I've just manually split it here to avoid awkward line breaks
Hopefully you can see the symmetry, and how to extend it to
worksheets

Hope this helps

Pet

On Feb 21, 7:15 pm, (e-mail address removed) wrote:

I got this from Peo Sjoblom, looks across eight sheets in his example.
I got this from Peo Sjoblom, looks across eight sheets in his example. Peo
sent me a two page e-mail explaining how the formula works. Even with that
in hand I barely understand any of it. But it seems pretty easy to adapt to
your own workbooks/worksheets, the ranges and sheet names

Where A2 is the lookup value on the formula sheet
MySheets is a named range consisting of a list of the sheet names you want
to look at
A2:A200 refers to the sheets being looked at
A2:C200 is the Lookup_Array on the sheets being looked at

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0

Using a named range shortens the formula considerable as you can see by this
one which lists the sheet names in the formula. And of course both formulas
are all on one line

=VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),2,0

HT
Regards
Howar


Are you looking to add up all column G values from all sheets when it finds
Are you looking to add up all column G values from all sheets when it finds
the lookup name in column F of each sheet? If so, you could just use SUMIF
=SUMIF(Wrk1!F:F,A1,Wrk1!G:G)+SUMIF(Wrk2!F:F,A1,Wrk2!G:G)+etc
Just a plain old SUM function works with the Wrk1:Wrk6 idea, but summing
based on a condition does not. I also could not get SUMPRODUCT to accept a
worksheet range.



Re: VLOOKUP formula searching multiple worksheets ??
Thanks so much, Pete. The formula worked great for 6 worksheets but I
have 9 worksheets. It errored out when I added the formula for the 7th
worksheet.

Re: VLOOKUP formula searching multiple worksheets ??
KC,
I am not adding the formula result. It is only a look up and tell
whether it is on the 9 worksheets or not.

I got this from Peo Sjoblom, looks across eight sheets in his example.
I got this from Peo Sjoblom, looks across eight sheets in his example. Peo
sent me a two page e-mail explaining how the formula works. Even with that
in hand I barely understand any of it. But it seems pretty easy to adapt to
your own workbooks/worksheets, the ranges and sheet names.

Where A2 is the lookup value on the formula sheet.
MySheets is a named range consisting of a list of the sheet names you want
to look at.
A2:A200 refers to the sheets being looked at.
A2:C200 is the Lookup_Array on the sheets being looked at.

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)

Using a named range shortens the formula considerable as you can see by this
one which lists the sheet names in the formula. And of course both formulas
are all on one line.

=VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),2,0)

HTH
Regards,
Howard


Re: VLOOKUP formula searching multiple worksheets ??
eo
at
to
&MyS=ADheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)
his
las
eet5=AD";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Shee=
t1";"Sh=ADeet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A=
2:A200"),A=AD2)>0),0))&"'!A2:C200"),2,0)

Beautiful! Ths formula worked for 9 wrksheets . Thanks a great to all
who contributed!

Forgot to add, this is an array formula, CTRL+SHIFT+ENTER to commit.
Forgot to add, this is an array formula, CTRL+SHIFT+ENTER to commit.

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)

HTH
REgards,
Howard


I have an error for 6 sheets
Hi Pete,

I wanted to use the formula for 6 sheets, I managed to do for five sheets , then I have a warning as there are too many inserted formula.

I am not good at codes, what can I to use this for more then 5 sheets.

=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk2!$F:$G,2,0)),
VLOOKUP(A2,Wrk1!$F:$G,2,0))

Thanks

I have an error for 6 sheets
Hi Pete,

I wanted to use the formula for 6 sheets, I managed to do for five sheets , then I have a warning as there are too many inserted formula.

I am not good at codes, what can I to use this for more then 5 sheets.

=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk2!$F:$G,2,0)),
VLOOKUP(A2,Wrk1!$F:$G,2,0))

Thanks

formula for 6 sheets
Hi Pete,

I wanted to use the formula for 6 sheets, I managed to do for five sheets , then I have a warning as there are too many inserted formula.

I am not good at codes, what can I to use this for more then 5 sheets.

=IF(ISNA(VLOOKUP(A2,Wrk1!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk2!$F:$G,2,0)),
IF(ISNA(VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk3!$F:$G,2,0)),
VLOOKUP(A2,Wrk2!$F:$G,2,0)),
VLOOKUP(A2,Wrk1!$F:$G,2,0))

Thanks


Submitted via EggHeadCafe - Software Developer Portal of Choice
IIS 7.0 Extensionless UrlRewriting (Short urls)
http://www.eggheadcafe.com/tutorial...df-52898c6aa5d7/iis-70-extensionless-url.aspx
 
Quit posting the same thing every few minutes. You can use something like
this or with a defined name to do as desired.
=SUMPRODUCT(SUMIF(INDIRECT(ms&"!$a1:$z1"),"ttl",INDIRECT(ms&"!a"&ROW(A2)&":z"&ROW(A2)&"")))
 
Try this. I got this from Peo S. a couple years ago and it looks up across
any number of worksheets in the same workbook.

Looks pretty foreboding, I admit, and only understand a portion of how it
works. But it is really not to bad to install.

=VLOOKUP(A1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A1)>0),0))&"'!A2:B200"),2,0)

Lets say you enter this formula in D1 of sheet 1, all on one line, and use
CTRL + SHIFT + ENTER to commit. (array-entered)

=VLOOKUP(A1

A1 is the look_value on sheet 1.

MySheets

Is a named range which is a list of the worksheets you want to conduct the
lookup.
So you list the names of the sheet somewhere on sheet 1 and then select that
list and name it MySheets Those sheet will be referred to in the formula.

))&"'!A2:B200"),

The A2:B200 here is the table_array on EACH sheet that is named in the
MySheets named range. (I believe this must be identical on all sheets,
A2:B200 on each sheet or F1:G22 on each sheet for example.)

"),2,0)

The 2 here denotes the col_index_num which holds the value to be returned.

Be sure to use the array-enter.. Ctrl = Shift + Enter to commit. If you
make changes to the formula you have to do another array-enter.

Enjoy!

HTH
Regards,
Howard
 
But how to add in the message display if the value not found? message display is "record not found"

Thanks!
 
how to add in the mesage display if the value is not found? the mesage is "record not found"


Thanks!
 
Hi Pete,

I have tried this formula but getting the error as False. Can you please look into the syntax error and help me.

=IF(ISNA(VLOOKUP(C2438,'[LIV Tracking for IN01-11.xls]Data-IN01'!$L$2:$M$1500,2,FALSE)),IF(ISNA(VLOOKUP(C2438,'[LIV Tracking for IN02-11.xls]Data-IN02'!$N$3:$O$1500,2,0)),VLOOKUP(C2438,'[LIV Tracking for IN01-11.xls]Data-IN01'!$L$2:$M$1500,2,0),VLOOKUP(C2438,'[LIV Tracking for IN02-11.xls]Data-IN02'!$N$27:$O$1500,2,0)))

Regards,
Krishna
 
Back
Top