VLOOKUP on multiple worksheets
 

I have a very large spreadsheet with 4 worksheets. I need to do a VLOOKUP
for a value that may occur on any of the worksheets. I have been unable to
write a formula that will work. I cannot combine the worksheets into one
huge sheet. All the columns are laid out the same all all the sheets so
once I find the item I want I can display the information I want easily.

Dave

VLOOKUP on multiple worksheets
 

One way:-
=IF(ISNA(VLOOKUP(A3,Sheet1!A1:B10,2,0)),IF(ISNA(VL OOKUP(A3,Sheet2!A1:B10,2,0)),IF(ISNA(VLOOKUP(A3,Sh eet3!A1:B10,2,0)),IF(ISNA(VLOOKUP(A3,Sheet4!A1:B10 ,2,0)),"Oops"))))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"Brutus" wrote in message
ink.net...
I have a very large spreadsheet with 4 worksheets. I need to do a VLOOKUP
for a value that may occur on any of the worksheets. I have been unable to
write a formula that will work. I cannot combine the worksheets into one
huge sheet. All the columns are laid out the same all all the sheets so
once I find the item I want I can display the information I want easily.

Dave

VLOOKUP on multiple worksheets
 

Is data duplicated on the sheets? If not, just add the results of 4
Vlookups (one for each sheet). You will have to screen out #N/A.

Something like =If(ISNA(Vlookup(cond for Sheet 1),0,Vlookup(Cond for
Sheet1)) + Same for Sheet 2+ Same for Sheet 3 + Same for sheet 4

HTH

---GJC


--
gjcase
------------------------------------------------------------------------
gjcase's Profile: http://www.excelforum.com/member.php...o&userid=26061
View this thread: http://www.excelforum.com/showthread...hreadid=528678

VLOOKUP on multiple worksheets
 

Assume the lookup value is in A1 in the summary sheet and the range on the
other sheets
is A1:C500 and you want to lookup the value in column C

=VLOOKUP(A1,INDIRECT("'"&INDEX({"Sheet1";"Sheet2"; "Sheet3";"Sheet4"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3"; "Sheet4"}&"'!A1:A500"),A1)0),0))&"'!A1:C500"),3,0 )


entered with ctrl + shift & enter

replace the sheet names with your sheet names, if it would be Sheet1 to 4
meaning same name with an index number you can shorten it to

=VLOOKUP(A1,INDIRECT("'Sheet"&INDEX({1;2;3;4},MATC H(1,--(COUNTIF(INDIRECT("'Sheet"&{1;2;3;4}&"'!A1:A500"), A1)0),0))&"'!A1:C500"),3,0)


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Brutus" wrote in message
ink.net...
I have a very large spreadsheet with 4 worksheets. I need to do a VLOOKUP
for a value that may occur on any of the worksheets. I have been unable to
write a formula that will work. I cannot combine the worksheets into one
huge sheet. All the columns are laid out the same all all the sheets so
once I find the item I want I can display the information I want easily.

Dave

VLOOKUP on multiple worksheets
 

I get FALSE or Oops when I use that


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"Ken Wright" wrote in message
...
One way:-
=IF(ISNA(VLOOKUP(A3,Sheet1!A1:B10,2,0)),IF(ISNA(VL OOKUP(A3,Sheet2!A1:B10,2,0)),IF(ISNA(VLOOKUP(A3,Sh eet3!A1:B10,2,0)),IF(ISNA(VLOOKUP(A3,Sheet4!A1:B10 ,2,0)),"Oops"))))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"Brutus" wrote in message
ink.net...
I have a very large spreadsheet with 4 worksheets. I need to do a VLOOKUP
for a value that may occur on any of the worksheets. I have been unable
to write a formula that will work. I cannot combine the worksheets into
one huge sheet. All the columns are laid out the same all all the sheets
so once I find the item I want I can display the information I want
easily.

Dave

VLOOKUP on multiple worksheets
 

Yep, I'm a muppet tonight, had additional VLOOKUPs in my head but for some
reason nested them and didn't check it. Cheers for the catch Peo

Assuming that value being looked up is numeric

=IF(ISNA(VLOOKUP(A3,Sheet1!A1:B10,2,0),"",VLOOKUP( A3,Sheet1!A1:B10,2,0))
+IF(ISNA(VLOOKUP(A3,Sheet2!A1:B10,2,0),"",VLOOKUP( A3,Sheet2!A1:B10,2,0))
+IF(ISNA(VLOOKUP(A3,Sheet3!A1:B10,2,0),"",VLOOKUP( A3,Sheet3!A1:B10,2,0))
+IF(ISNA(VLOOKUP(A3,Sheet4!A1:B10,2,0),"",VLOOKUP( A3,Sheet4!A1:B10,2,0))

Prefer yours though :-)

Cheers Ken.


"Peo Sjoblom" wrote in message
...
I get FALSE or Oops when I use that


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"Ken Wright" wrote in message
...
One way:-
=IF(ISNA(VLOOKUP(A3,Sheet1!A1:B10,2,0)),IF(ISNA(VL OOKUP(A3,Sheet2!A1:B10,2,0)),IF(ISNA(VLOOKUP(A3,Sh eet3!A1:B10,2,0)),IF(ISNA(VLOOKUP(A3,Sheet4!A1:B10 ,2,0)),"Oops"))))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"Brutus" wrote in message
ink.net...
I have a very large spreadsheet with 4 worksheets. I need to do a
VLOOKUP for a value that may occur on any of the worksheets. I have been
unable to write a formula that will work. I cannot combine the
worksheets into one huge sheet. All the columns are laid out the same
all all the sheets so once I find the item I want I can display the
information I want easily.

Dave

VLOOKUP on multiple worksheets
 

I am actually looking up a variety of things. Some are phone numbers, some
are text only, some are alphanumeric.

Dave

"Ken Wright" wrote in message
...
Yep, I'm a muppet tonight, had additional VLOOKUPs in my head but for some
reason nested them and didn't check it. Cheers for the catch Peo

Assuming that value being looked up is numeric

=IF(ISNA(VLOOKUP(A3,Sheet1!A1:B10,2,0),"",VLOOKUP( A3,Sheet1!A1:B10,2,0))
+IF(ISNA(VLOOKUP(A3,Sheet2!A1:B10,2,0),"",VLOOKUP( A3,Sheet2!A1:B10,2,0))
+IF(ISNA(VLOOKUP(A3,Sheet3!A1:B10,2,0),"",VLOOKUP( A3,Sheet3!A1:B10,2,0))
+IF(ISNA(VLOOKUP(A3,Sheet4!A1:B10,2,0),"",VLOOKUP( A3,Sheet4!A1:B10,2,0))

Prefer yours though :-)

Cheers Ken.


"Peo Sjoblom" wrote in message
...
I get FALSE or Oops when I use that


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"Ken Wright" wrote in message
...
One way:-
=IF(ISNA(VLOOKUP(A3,Sheet1!A1:B10,2,0)),IF(ISNA(VL OOKUP(A3,Sheet2!A1:B10,2,0)),IF(ISNA(VLOOKUP(A3,Sh eet3!A1:B10,2,0)),IF(ISNA(VLOOKUP(A3,Sheet4!A1:B10 ,2,0)),"Oops"))))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"Brutus" wrote in message
ink.net...
I have a very large spreadsheet with 4 worksheets. I need to do a
VLOOKUP for a value that may occur on any of the worksheets. I have
been unable to write a formula that will work. I cannot combine the
worksheets into one huge sheet. All the columns are laid out the same
all all the sheets so once I find the item I want I can display the
information I want easily.

Dave