I have 4 sheets of data(sheet 1,sheet2,...) and 10 columns of data on each
sheet, each sheet starts with column A as the part number and 10 as a price.
I have a separate sheet called summary, that has a list of part numbers in
column A, i want to match this list to return the values that are in the 4
sheets of data.

Hi Vish

If the 4 sheets are named as Sheet1, Sheet2, Sheet3 and Sheet4 try the below
formula from the summary sheet cell B1 with a valid part number in cell A1

In Summary sheet cell B1..
=VLOOKUP($C$1,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF( INDIRECT("Sheet"&ROW(INDIRECT("1:4"))&"!A:A"),$C$1 )0,0)&"!A:J"),COLUMN(),0)

Please note that this is an array formula. An array formula can perform
multiple calculations and then return either a single result or multiple
results. You create array formulas in the same way that you create other
formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

Once applied copy/drag the fomula to the right (upto Col J)..

--
Jacob


"Vish" wrote:

I have 4 sheets of data(sheet 1,sheet2,...) and 10 columns of data on each
sheet, each sheet starts with column A as the part number and 10 as a price.
I have a separate sheet called summary, that has a list of part numbers in
column A, i want to match this list to return the values that are in the 4
sheets of data.

Hi,

You may refer to question 9 at the following link -
http://ashishmathur.com/knowledgebaseII.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Vish" wrote in message
...
I have 4 sheets of data(sheet 1,sheet2,...) and 10 columns of data on each
sheet, each sheet starts with column A as the part number and 10 as a
price.
I have a separate sheet called summary, that has a list of part numbers in
column A, i want to match this list to return the values that are in the 4
sheets of data.