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

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