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