Excel - VLOOKUP formula searching multiple worksheets ??

Asked By theCityLigh on 21-Feb-07 02:15 PM
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.




Pete_UK replied on 21-Feb-07 02:31 PM
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)),
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
L. Howard Kittle replied on 21-Feb-07 03:30 PM
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
KC Rippstein replied on 21-Feb-07 03:32 PM
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.
theCityLigh replied on 21-Feb-07 03:52 PM
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.
theCityLigh replied on 21-Feb-07 04:01 PM
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.
L. Howard Kittle replied on 21-Feb-07 04:33 PM
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
theCityLigh replied on 21-Feb-07 04:35 PM
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!
L. Howard Kittle replied on 21-Feb-07 05:05 PM
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
engi yalcin replied to Pete_UK on 17-Apr-10 07:10 AM
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
engi yalcin replied to Pete_UK on 17-Apr-10 07:11 AM
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
engi yalcin replied to Pete_UK on 17-Apr-10 07:13 AM
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
engi yalcin replied to Pete_UK on 17-Apr-10 07:24 AM
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
abdul jaleel replied to engi yalcin on 12-Jun-10 03:38 AM
you can use vlookup many sheets as many as you wish if the result is other than text.

write the vlook up function for 5 sheets then enter + symbol then write vlookup for another 5,you can enter + symbol again for adding another 5
Dandan Zhu replied to abdul jaleel on 16-Aug-10 05:16 AM
=IF(A7="","",-IF(HLOOKUP($A7,Calculation!$C$1:$HJ$801,801,FALSE)=1,0,J7*R7*I7*Q7/O7/100))



It looks up Calculation sheet at the moment, I want to look up both Calculation sheet and Calculation sheet 2.



Many thanks
mitch del rosario replied to L. Howard Kittle on 08-Feb-11 10:41 PM
But how  to add in the message display if the value not found? message display is "record not found"



Thanks!
mitch del rosario replied to L. Howard Kittle on 08-Feb-11 10:42 PM
how to add in the mesage display if the value is not found? the mesage is "record not found"





Thanks!
Krishna Murthy replied to Pete_UK on 17-Mar-11 01:12 AM
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