VLOOKUP on multiple worksheets

B

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
 
K

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 :)
------------------------------­------------------------------­----------------
 
G

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
 
P

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
 
K

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.
 
B

Brutus

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

Dave
 
Top