VLOOKUP on multiple worksheets

[Brutus]

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

[Ken Wright]

One way:-
=IF(ISNA(VLOOKUP(A3,Sheet1!A1:B10,2,0)),IF(ISNA(VLOOKUP(A3,Sheet2!A1:B10,2,0)),IF(ISNA(VLOOKUP(A3,Sheet3!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" <[email protected]> wrote in message
news:[email protected]...
>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
>

[gjcase]

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

[Peo Sjoblom]

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},MATCH(1,--(COUNTIF(INDIRECT("'Sheet"&{1;2;3;4}&"'!A1:A500"),A1)>0),0))&"'!A1:C500"),3,0)


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Brutus" <[email protected]> wrote in message
news:[email protected]...
>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
>

[Peo Sjoblom]

I get FALSE or Oops when I use that


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"Ken Wright" <[email protected]> wrote in message
news:%[email protected]...
> One way:-
> =IF(ISNA(VLOOKUP(A3,Sheet1!A1:B10,2,0)),IF(ISNA(VLOOKUP(A3,Sheet2!A1:B10,2,0)),IF(ISNA(VLOOKUP(A3,Sheet3!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" <[email protected]> wrote in message
> news:[email protected]...
>>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
>>
>
>

[Ken Wright]

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" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
news:On30%[email protected]...
>I get FALSE or Oops when I use that
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> http://nwexcelsolutions.com
>
>
>
> "Ken Wright" <[email protected]> wrote in message
> news:%[email protected]...
>> One way:-
>> =IF(ISNA(VLOOKUP(A3,Sheet1!A1:B10,2,0)),IF(ISNA(VLOOKUP(A3,Sheet2!A1:B10,2,0)),IF(ISNA(VLOOKUP(A3,Sheet3!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" <[email protected]> wrote in message
>> news:[email protected]...
>>>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
>>>
>>
>>
>
>

[Brutus]

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

Dave

"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
> 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" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
> news:On30%[email protected]...
>>I get FALSE or Oops when I use that
>>
>>
>> --
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>> http://nwexcelsolutions.com
>>
>>
>>
>> "Ken Wright" <[email protected]> wrote in message
>> news:%[email protected]...
>>> One way:-
>>> =IF(ISNA(VLOOKUP(A3,Sheet1!A1:B10,2,0)),IF(ISNA(VLOOKUP(A3,Sheet2!A1:B10,2,0)),IF(ISNA(VLOOKUP(A3,Sheet3!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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>>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
>>>>
>>>
>>>
>>
>>
>
>